On Mon, 20 Sep 2004, CHRIS HOOVER wrote: > I need some help writing a simple function. > > Due to some program limitations for a program I run the db's for, I'm having > to write some simple functions to run some selects. However, I am not sure > how to have them correctly return the record(s) selected and/or how to > properly call them from sql. > > Would someone be so kind as to help me with this. > > Here is an example function: > > CREATE OR REPLACE FUNCTION "public"."test_func" (varchar) RETURNS SETOF > "public"."test_tbl" AS' > Declare > PCN varchar; > test_tbl_rec clmhdr%ROWTYPE; > > Begin > > PCN := $1; > > select into test_tbl_rec * from test_tbl where test_tbl.pcn = PCN; > return test_tbl_rec; > > end; > 'LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY INVOKER;
If you want to return sets of rows, you're going to need to loop over the returned rows from the select using return next. These links may help: http://www.varlena.com/varlena/GeneralBits/26.html http://techdocs.postgresql.org/guides/SetReturningFunctions > I was trying to call this function from psql using: > select test_func('asdf'); As a side note, plpgsql set returning functions cannot be called in a select list, only in the from clause (the above links will have examples). ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
