Thread: PLPGSQL: Using SELECT INTO and EXECUTE

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

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

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:

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

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/

> 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

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:

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