Thread: plpgsql function Syntax

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?

Thanks,
Derrick

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.

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