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
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 |