FW: PL/pgSQL Function Help

Well that kills two birds. I completely didn't understand
the difference between SETOF and just RECORD return types.

That fixed it up, and for the record here is what the function looks
like now.

CREATE TYPE myrec AS (
id int
);

CREATE OR REPLACE FUNCTION test(x int) RETURNS SETOF myrec
AS '
DECLARE
output RECORD;
BEGIN
IF x THEN
RETURN;
END IF;

SELECT INTO output 9999;
RETURN NEXT output;
RETURN;
END;
'
LANGUAGE plpgsql;

xxx=# select * from test(1);
id
----
(0 rows)

xxx=# select * from test(0);
id
------
9999
(1 row)

Thanks Tom!

--
David

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Friday, December 16, 2005 3:30 PM
To: Niblett, David A
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] PL/pgSQL Function Help

"Niblett, David A" <niblettda(at)gru(dot)com> writes:
> Is there no way in Postgres that I can simply not return anything so I
> show zero rows?

Make the function return SETOF myrec not just myrec. Then you can
return zero or one (or more) myrec's.

regards, tom lane