a function problem...
| От | joye4023@gigigaga.com (joyehsu) |
|---|---|
| Тема | a function problem... |
| Дата | |
| Msg-id | 142923bd.0302170757.1ed0eb0d@posting.google.com обсуждение исходный текст |
| Список | 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!
В списке pgsql-general по дате отправления: