Thread: dynamic sql statements and OUT variables

dynamic sql statements and OUT variables

From
"Girish Bajaj"
Date:

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:

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.

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

DISCLAIMER:The information in this message is confidential and may be legally privileged. It is intended solely for the addressee.  Access to this message by anyone else is unauthorised.  If you are not the intended recipient, any disclosure, copying, or distribution of the message, or any action or omission taken by you in reliance on it, is prohibited and may be unlawful.  Please immediately contact the sender if you have received this message in error. Thank you.