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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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.

Browse pgsql-novice by date

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