PLPGSQL: Using SELECT INTO and EXECUTE

From: Michael Dunn <michael(at)2cactus(dot)com>
To: Postgres <pgsql-general(at)postgresql(dot)org>
Subject: PLPGSQL: Using SELECT INTO and EXECUTE
Date: 2001-06-12 20:29:08
Message-ID: 3B267B94.60908@2cactus.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Limin Liu 2001-06-12 20:31:04 Re: Big5 contains '\'
Previous Message Tim Barnard 2001-06-12 20:22:57 Re: libpq++ PgDatabase and PgConnection