Обсуждение: a function problem...

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: "joyehsu" <joye4023@gigigaga.com>


> 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...

I believe MAX() will return NULL when rows don't match. So
maxint after the select is NULL, and FOUND is true. So I'd write

IF (maxint IS NULL) THEN
...

Yet another reason why NULLs are evil...

Mike Mascari
mascarm@mascari.com