Обсуждение: 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) > >