plpgsql function Syntax

#1

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

#2

On Wed, 1 Sep 2004 derrick (AT) grifflink (DOT) com wrote:

Quote:

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.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

#3

Excellent! Thank you.

Derrick
----- Original Message -----
From: "Stephan Szabo" <sszabo (AT) megazone (DOT) bigpanda.com>
To: <derrick (AT) grifflink (DOT) com>
Cc: <pgsql-novice (AT) postgresql (DOT) org>
Sent: Wednesday, September 01, 2004 8:32 PM
Subject: Re: [NOVICE] plpgsql function Syntax

Quote:

On Wed, 1 Sep 2004 derrick (AT) grifflink (DOT) 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.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

plpgsql function Syntax

comp.databases.postgresql.novice comp.databases.postgresql.novice

plpgsql function Syntax - 09-01-2004 , 08:16 PM

Re: plpgsql function Syntax - 09-01-2004 , 09:32 PM

Re: plpgsql function Syntax - 09-01-2004 , 10:20 PM