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
