Re: PLPGSQL: Using Transactions and locks

Поиск
Список
Период
Сортировка
Искать

Re: PLPGSQL: Using Transactions and locks

От:
"Gregory Wood" <gregw@com-stock.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

Re: PLPGSQL: Using SELECT INTO and EXECUTE

От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Michael Dunn  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

PLPGSQL: Using Transactions and locks

От:
Allan Kamau <hugebirdwings@yahoo.com>
Дата:
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/

PLPGSQL: Using SELECT INTO and EXECUTE

От:
Michael Dunn <michael@2cactus.com>
Дата:
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

Re: PLPGSQL: Using SELECT INTO and EXECUTE

От:
Michael Dunn <michael@2cactus.com>
Дата:
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  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
>
>

Re: PLPGSQL: Using Transactions and locks

От:
Alex Pilosov <alex@pilosoft.com>
Дата:
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)
> 
>
FAQ