Thread: dynamic sql statements and OUT variables

dynamic sql statements and OUT variables

From

"Girish Bajaj"

Date:

29 July 2003, 07:55:19

1) How do I get the value back for this dynamic statement?

minUserID := execute ''SELECT MIN(userid) as minID FROM '' || campQueueTableName;

campQueueTableName and minUserID are variables. The language is plpgsql. I want the value of what comes back in the MIN to be stored in the variable.

2) How do I do this?

Select myValue := userid FROM table1;

Where I want the value of what comes out from userid col, to go into the variable declared as myValue. Language is plpgsql.

Thanks,

Girish

Re: dynamic sql statements and OUT variables

From

"Henshall, Stuart - TNP Southwest"

Date:

29 July 2003, 08:29:48

1) If r is declared as type RECORD
FOR r IN EXECUTE ''SELECT MIN(userid) as minID FROM '' || campQueueTableName LOOP
minUserID :=r.minID
END LOOP

2) With a known table name it is much easier:
Select INTO myValue userid FROM table1 LIMIT 1;

as a side note:
Select INTO myValue userid FROM table1 ORDER BY userid LIMIT 1;
gives the same as MIN(userid) would but is faster and allows access to the other fields.
hth,
- Stuart
P.S. Sorry about funny format caused by the disclaimer adder.

-----Original Message-----
From: Girish Bajaj [mailto:gbajaj@tietronix.com]
Sent: 29 July 2003 08:54
To: pgsql-novice@postgresql.org
Subject: [NOVICE] dynamic sql statements and OUT variables

THIS EMAIL HAS BEEN SWEPT FOR VIRUSES BY THE NORTHCLIFFE GROUP MAILSWEEPER SERVER.