Excellent! Thank you.
Derrick
----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone.bigpanda.com>
To: <derrick@grifflink.com>
Cc: <pgsql-novice@postgresql.org>
Sent: Wednesday, September 01, 2004 8:32 PM
Subject: Re: [NOVICE] plpgsql function Syntax
>
> On Wed, 1 Sep 2004 derrick@grifflink.com wrote:
>
> > Is it possible to get a select statement to work like this with a for
loop?
> >
> > CREATE FUNCTION public.search(int4)
> > RETURNS SETOF search1 AS
> > '
> > DECLARE
> > category alias for $1;
> > newrows search1%rowtype;
> > rec RECORD;
> > SQLCommand varchar;
> >
> > BEGIN
> > IF (category = 0) THEN
> > SQLCommand := 'SELECT * FROM table';
> > ELSE
> > SQLCommand := 'SELECT idnumber FROM table';
> > END IF;
> >
> > FOR rec IN SQLCommand
> > LOOP
> > ...blah...
> > ...blah...
> > END LOOP;
> >
> > Basically I want to create the SELECT statement dynamically and then use
> > that select statement in subsequent querries later in the function.
Will
> > this syntax work or should I try to use a VIEW?
>
> You should be able to use FOR rec IN EXECUTE SQLCommand LOOP to execute
> the string and loop over the results I think.
>
>