Re: a function problem...

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