Skip site navigation (1) Skip section navigation (2)

Re: dynamic sql statements and OUT variables

From: "Henshall, Stuart - TNP Southwest" <shenshall(at)tnp-southwest(dot)co(dot)uk>
To: 'Girish Bajaj' <gbajaj(at)tietronix(dot)com>,pgsql-novice(at)postgresql(dot)org
Subject: Re: dynamic sql statements and OUT variables
Date: 2003-07-29 08:21:11
Message-ID: E382B5D8EDE1D6118DBE0008C759BCD601EAACB3@WCPEXCHANGE (view raw or flat)
Thread:
Lists: pgsql-novice
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(at)tietronix(dot)com]
Sent: 29 July 2003 08:54
To: pgsql-novice(at)postgresql(dot)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.


pgsql-novice by date

Next:From: Mel JameroDate: 2003-07-29 09:06:35
Subject: help: now() + N is now failing!
Previous:From: Girish BajajDate: 2003-07-29 07:53:39
Subject: dynamic sql statements and OUT variables

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group