Skip site navigation (1) Skip section navigation (2)

Re: Can't use WITH in a PERFORM query in PL/pgSQL?

From: <depstein(at)alliedtesting(dot)com>
To: <pgsql-bugs(at)postgresql(dot)org>
Cc: <pgagarinov(at)alliedtesting(dot)com>, <vshahov(at)alliedtesting(dot)com>
Subject: Re: Can't use WITH in a PERFORM query in PL/pgSQL?
Date: 2011-03-24 15:21:36
Message-ID: 29F36C7C98AB09499B1A209D48EAA615B501DA9401@mail2a.alliedtesting.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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.

(Sorry for top-posting: I am forced to use Outlook at work...)

From: Dmitry Epstein
Sent: Sunday, March 06, 2011 4:29 PM
To: 'pgsql-bugs(at)postgresql(dot)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 and code 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 even when 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.

Responses

pgsql-bugs by date

Next:From: Pavel StehuleDate: 2011-03-24 15:36:31
Subject: Re: Can't use WITH in a PERFORM query in PL/pgSQL?
Previous:From: Fujii MasaoDate: 2011-03-24 12:13:44
Subject: Re: postgres 9 streaming replication

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group