PLPGSQL: Using SELECT INTO and EXECUTE

Started by Michael Dunnalmost 25 years ago6 messagesgeneral
Jump to latest
#1Michael Dunn
michael@2cactus.com

Can EXECUTE handle a SELECT INTO statement within a plpgsql function.
Here is what I am trying to do.

The standard SELECT INTO statement:

SELECT INTO session_logins_id s.session_logins_id
FROM session_logins s
WHERE s.username = session_login_in;

The problem with using a standard SELECT INTO statement within a plpgsql
function is that I need to dynamically assign the table name in the FROM
clause. Since plpgsql cannot parse a variable within a standard SQL
statement I issue the EXECUTE command using a concatenated SQL statement
inside a variable. Such that:

DECLARE
session_login_in ALIAS FOR $x;

session_logins_id INTEGER;

BEGIN
sql_command := ''SELECT INTO session_logins_id
s.session_logins_id
FROM '' || table_name || '' s
WHERE s.username = '''''' || session_login_in ||
'''''';'';

EXECUTE sql_command;

This is but one variation I have tried to pass to the EXECUTE command..
but, in all instances it errors out. This particular example above
errors out with the following:
ERROR: parser: parse error at or near "INTO".
A second variation would be to isolate the plpgsql variable
session_logins_id outside the command:

sql_command := ''SELECT INTO '' || session_logins_id || ''
s.session_logins_id
FROM '' || table_name || '' s
WHERE s.username = '''''' || session_login_in ||
'''''';'';

But, this second variation returns a null string inside the sql_command
variable and obviously errors out with the EXECUTE command not being
able to execute a null query. Am I not structuring the command
correctly to be passed to the EXECUTE statement?? Or, is it not possible
to use a SELECT INTO statement using the EXECUTE command? The only
other workaround I can think of is calling a c function from a stored
prcedure, but then I am concerned with degradation in performance since
this particular function would be handling a large amount of requests a
second. Additionally, I would like to maintain continuity in the code
and do not want to introduce another language into the scheme. Any
suggestions would be greatly appreciated. Thanks

Regards,

Michael Dunn

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Michael Dunn (#1)
Re: PLPGSQL: Using SELECT INTO and EXECUTE

Michael Dunn <michael@2cactus.com> writes:

Can EXECUTE handle a SELECT INTO statement within a plpgsql function.

SELECT INTO doesn't mean the same thing in plpgsql as it does in regular
SQL. Use CREATE TABLE AS, instead.

regards, tom lane

#3Michael Dunn
michael@2cactus.com
In reply to: Michael Dunn (#1)
Re: PLPGSQL: Using SELECT INTO and EXECUTE

Tom,

Thanks for the input.. but shortly after sending the post I found the
document outlining the conversion from Oracle PL/SQL to Postgres
PLPGSQL. SELECT INTO is not supported by EXECUTE... and that in place
of SELECT INTO one should use the FOR...EXECUTE command. Thanks again
for your timely response...

Regards,

Michael Dunn

Tom Lane wrote:

Show quoted text

Michael Dunn <michael@2cactus.com> writes:

Can EXECUTE handle a SELECT INTO statement within a plpgsql function.

SELECT INTO doesn't mean the same thing in plpgsql as it does in regular
SQL. Use CREATE TABLE AS, instead.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

#4Allan Kamau
hugebirdwings@yahoo.com
In reply to: Tom Lane (#2)
PLPGSQL: Using Transactions and locks

Hi all,
How do I write transaction statements like 'BEGIN
WORK'... in PLPGSQL.
Also how do I write lock statements in the same.
Thank you in advance.

Allan Kamau

__________________________________________________
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail - only $35
a year! http://personal.mail.yahoo.com/

#5Gregory Wood
gregw@com-stock.com
In reply to: Allan Kamau (#4)
Re: PLPGSQL: Using Transactions and locks

How do I write transaction statements like 'BEGIN
WORK'... in PLPGSQL.

You can't... the function is already running within a transaction (implicit
or explicit) and PostgreSQL doesn't have any nested transactions, therefore
you can't start a transaction from within a function.

Also how do I write lock statements in the same.

I'm not sure what you're trying to do, but I think SELECT ... FOR UPDATE
would work in this context.

Greg

#6Alex Pilosov
alex@pilosoft.com
In reply to: Allan Kamau (#4)
Re: PLPGSQL: Using Transactions and locks

You cannot have nested transactions, thus you can't have BEGIN/COMMIT
inside your plpgsql function.

You can do locking, by doing this: EXECUTE ''LOCK foobar'';

On Wed, 13 Jun 2001, Allan Kamau wrote:

Show quoted text

Hi all,
How do I write transaction statements like 'BEGIN
WORK'... in PLPGSQL.
Also how do I write lock statements in the same.
Thank you in advance.

Allan Kamau

__________________________________________________
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail - only $35
a year! http://personal.mail.yahoo.com/

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)