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!