FW: PL/pgSQL Function Help

["Niblett, David A" <niblettda@xxxxxxx>]

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@xxxxxxxxxxxxx] 
Sent: Friday, December 16, 2005 3:30 PM
To: Niblett, David A
Cc: pgsql-general@xxxxxxxxxxxxxx
Subject: Re: [GENERAL] PL/pgSQL Function Help 


"Niblett, David A" <niblettda@xxxxxxx> 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