Re: Can't use WITH in a PERFORM query in PL/pgSQL?
От | Bruce Momjian |
---|---|
Тема | Re: Can't use WITH in a PERFORM query in PL/pgSQL? |
Дата | |
Msg-id | 201109061743.p86HhQX16354@momjian.us обсуждение исходный текст |
Ответ на | Re: Can't use WITH in a PERFORM query in PL/pgSQL? (<depstein@alliedtesting.com>) |
Ответы |
Re: Can't use WITH in a PERFORM query in PL/pgSQL?
(Robert Haas <robertmhaas@gmail.com>)
|
Список | pgsql-bugs |
depstein@alliedtesting.com wrote: > Update: It has been suggested to wrap perform around a select like this: > > do > $$begin > perform( > with A as (select 1 as foo) > select foo from A > ); > end$$; > > This won't work if select returns more than one statement: > > do > $$begin > perform( > with A as (select generate_series(1,3) as foo) > select foo from A > ); > end$$; > > ERROR: more than one row returned by a subquery used as an expression > > So I still say it's broken. Well, this problem isn't isolated to WITH queries: test=> do $$begin perform( select 1 UNION ALL select 1 ); end$$; ERROR: more than one row returned by a subquery used as an expression test=> do $$begin perform( select relname from pg_class ); end$$; ERROR: more than one row returned by a subquery used as an expression perform() can't seem to handle any SELECT that returns more than one row, but perform replacing the SELECT can: test=> do $$begin perform relname from pg_class; end$$; DO That is certainly unsual, and I have documented this suggestion and limitation in the attached patch that I have applied to 9.0, 9.1, and head. I think the idea that PERFORM will replace one or more SELECTs in a WITH clause is just totally confusing and probably should not be supported. I guess the only bug is that perform() can't handle more than one returned row, but at least we have documented that and can fix it later if we want. I have to say, those Allied Testing people are very good at finding bugs. --------------------------------------------------------------------------- > > From: Dmitry Epstein > Sent: Sunday, March 06, 2011 4:29 PM > To: 'pgsql-bugs@postgresql.org' > Cc: Peter Gagarinov; Vladimir Shahov > Subject: Can't use WITH in a PERFORM query in PL/pgSQL? > > PostgreSQL 9.0.1 > > It seems that PostgreSQL doesn't understand the WITH construct when used in a PERFORM query inside PL/pgSQL functions andcode blocks: > > Example: > > do > $$begin > with A as (select 1 as foo) > perform foo from A; > end$$; > > syntax error at or near "perform" > > do > $$begin > with A as (select 1 as foo) > select foo from A; > end$$; > > query has no destination for result data > > The only workaround that I can think of is to use a dummy variable to capture the query result. This has to be done evenwhen the query doesn't have a result (as when calling a function returning void). > > do > $$declare > dummy record; > begin > with A as (select 1 as foo) > select foo into dummy from A; > end$$; > > > Dmitry Epstein | Developer > > Allied Testing > T + 7 495 544 48 69 Ext 417 > M + 7 926 215 73 36 > > www.alliedtesting.com<http://www.alliedtesting.com/> > We Deliver Quality. > -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml new file mode 100644 index 08c3658..a2482de *** a/doc/src/sgml/plpgsql.sgml --- b/doc/src/sgml/plpgsql.sgml *************** PERFORM <replaceable>query</replaceable> *** 940,945 **** --- 940,948 ---- result. Write the <replaceable>query</replaceable> the same way you would write an SQL <command>SELECT</> command, but replace the initial keyword <command>SELECT</> with <command>PERFORM</command>. + For <keyword>WITH</> queries, use <keyword>PERFORM</> and then + place the query in parentheses. (In this case, the query can only + return one row.) <application>PL/pgSQL</application> variables will be substituted into the query just as for commands that return no result, and the plan is cached in the same way. Also, the special variable
В списке pgsql-bugs по дате отправления: