From: | joye4023(at)gigigaga(dot)com (joyehsu) |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | a function problem... |
Date: | 2003-02-17 15:57:50 |
Message-ID: | 142923bd.0302170757.1ed0eb0d@posting.google.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi! I tried to create a pgsql function to get me default values, but I
can't get correct results,please help me solve the problem...
>create table test (t text);
>CREATE FUNCTION test()
RETURNS text
AS 'DECLARE
prefix text;
maxint integer;
ret text;
BEGIN
prefix := cast(date_part('year', now())::integer - 1911 as text) ||
lpad(date_part('month', now()), 2, '0');
SELECT INTO maxint max(date_part('day', t::date))::integer from test
where t like prefix || '%';
IF FOUND
THEN
maxint := maxint + 1;
ret = prefix || lpad(cast(maxint as text), 2, '0');
ELSE
ret = prefix || cast('01' as text);
END IF;
RETURN ret;
END;'
LANGUAGE 'plpgsql';
The above are my table and fuction...what test() do is to find out the
max value 'maxint' in column 'test', and return maxint + 1...
Example:
>select * from test;
t
--------
920201
920205
(2 rows)
>select test();
test
--------
920206
(1 row)
the result is what I want...but while I clear table 'test',
>delete from test;
DELETE 2
>select test();
test
------
(1 row)
and it should be '920201', get a null string instead...
I think there's some problem with the line(select into maxint),is
there something wrong with test()? please help me solve it, thanks!
From | Date | Subject | |
---|---|---|---|
Next Message | jose antonio leo | 2003-02-17 16:34:02 | accent = no accent |
Previous Message | Tom Lane | 2003-02-17 15:52:54 | Re: Index not used with IS NULL |