PLPGSQL: Using SELECT INTO and EXECUTE

Поиск
Список
Период
Сортировка
От Michael Dunn
Тема PLPGSQL: Using SELECT INTO and EXECUTE
Дата
Msg-id 3B267B94.60908@2cactus.com
обсуждение исходный текст
Ответы Re: PLPGSQL: Using SELECT INTO and EXECUTE
Список 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


В списке pgsql-general по дате отправления:

Предыдущее
От: "Gregory Wood"
Дата:
Сообщение: Re: very big problem with NULL
Следующее
От: Tom Lane
Дата:
Сообщение: Re: REPOST: redefining location of the socket file /tmp/.s.PGSQL.5432