Thread: About EXECUTE in pl/pgsql
Hi, I do following in pl/pgsql. (st1 has been set to "LIKE 'abc%' ") EXECUTE ''CREATE TEMP TABLE comuser AS '' || ''SELECT * FROM comt_user1 WHERE username ''||St1|| ''ORDER BY username;''; I've got "ERROR: EXECUTE ..SELECT INTO hasn't impletmented", How can?? JACK
jack wrote: > Hi, > I do following in pl/pgsql. (st1 has been set to "LIKE 'abc%' ") > > EXECUTE > ''CREATE TEMP TABLE comuser AS '' || > ''SELECT * FROM comt_user1 WHERE username ''||St1|| > ''ORDER BY username;''; > > I've got "ERROR: EXECUTE ..SELECT INTO hasn't impletmented", How can?? Can you try to CREATE the table and then do a INSERT ... SELECT. Perhaps that's the only way. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
"jack" <datactrl@tpg.com.au> writes:
> I do following in pl/pgsql. (st1 has been set to "LIKE 'abc%' ")
> EXECUTE
> ''CREATE TEMP TABLE comuser AS '' ||
> ''SELECT * FROM comt_user1 WHERE username ''||St1||
> ''ORDER BY username;'';
> I've got "ERROR: EXECUTE ..SELECT INTO hasn't impletmented", How can??
Ooops :-(. That check was intended to prevent SELECT ... INTO table
from being used in EXECUTE, mainly because of the confusion factor
(SELECT INTO in plpgsql doesn't mean what it means in SQL, but the
SQL interpretation is what you'd get from EXECUTE).
However, the check wasn't intended to trigger on CREATE AS.
I've stuck a quick fix into the current sources. Should be in 7.2.1.
regards, tom lane
Thank you Tom ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "jack" <datactrl@tpg.com.au> Cc: <pgsql-sql@postgresql.org> Sent: Monday, March 25, 2002 5:45 PM Subject: Re: [SQL] About EXECUTE in pl/pgsql > "jack" <datactrl@tpg.com.au> writes: > > I do following in pl/pgsql. (st1 has been set to "LIKE 'abc%' ") > > > EXECUTE > > ''CREATE TEMP TABLE comuser AS '' || > > ''SELECT * FROM comt_user1 WHERE username ''||St1|| > > ''ORDER BY username;''; > > > I've got "ERROR: EXECUTE ..SELECT INTO hasn't impletmented", How can?? > > Ooops :-(. That check was intended to prevent SELECT ... INTO table > from being used in EXECUTE, mainly because of the confusion factor > (SELECT INTO in plpgsql doesn't mean what it means in SQL, but the > SQL interpretation is what you'd get from EXECUTE). > > However, the check wasn't intended to trigger on CREATE AS. > > I've stuck a quick fix into the current sources. Should be in 7.2.1. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
Tom Lane wrote: > "jack" <datactrl@tpg.com.au> writes: > > I do following in pl/pgsql. (st1 has been set to "LIKE 'abc%' ") > > > EXECUTE > > ''CREATE TEMP TABLE comuser AS '' || > > ''SELECT * FROM comt_user1 WHERE username ''||St1|| > > ''ORDER BY username;''; > > > I've got "ERROR: EXECUTE ..SELECT INTO hasn't impletmented", How can?? > > Ooops :-(. That check was intended to prevent SELECT ... INTO table > from being used in EXECUTE, mainly because of the confusion factor > (SELECT INTO in plpgsql doesn't mean what it means in SQL, but the > SQL interpretation is what you'd get from EXECUTE). > > However, the check wasn't intended to trigger on CREATE AS. > > I've stuck a quick fix into the current sources. Should be in 7.2.1. CVS current and 7.2.X CVS HISTORY/release.sgml updated with: Allow CREATE TABLE AS ... SELECT in PL/PgSQL (Tom) -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> CVS current and 7.2.X CVS HISTORY/release.sgml updated with:
> Allow CREATE TABLE AS ... SELECT in PL/PgSQL (Tom)
We already did allow that. Please make it say
Allow EXECUTE of "CREATE TABLE AS ... SELECT" in PL/PgSQL (Tom)
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > CVS current and 7.2.X CVS HISTORY/release.sgml updated with: > > Allow CREATE TABLE AS ... SELECT in PL/PgSQL (Tom) > > We already did allow that. Please make it say > > Allow EXECUTE of "CREATE TABLE AS ... SELECT" in PL/PgSQL (Tom) Done. I was wondering if EXECUTE was the only issue. I see it was. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026