a function problem...

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!

Responses

Browse pgsql-general by date

  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