Date: Mon, 20 Sep 2004 13:51:09 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: CHRIS HOOVER <CHRIS.HOOVER@companiongroup.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Help with function
In-Reply-To: <NX13783b-6a515768@companiongroup.com>
Message-ID: <20040920134549.L79243@megazone.bigpanda.com>
References: <NX13783b-6a515768@companiongroup.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

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).