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

On Sun, Mar 6, 2011 at 14:29, <depstein@alliedtesting.com> wrote:

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$$;

Or use parentheses:

do $$
begin
perform (with A as (select 1 as foo)
select foo from A);
end;
$$ language 'plpgsql';

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@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 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/&gt;
We Deliver Quality.

Hello

why you can do it?

please, try to RETURN QUERY ...

Regards

Pavel Stehule

On Thu, Mar 24, 2011 at 10:36 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

Hello

why you can do it?

please, try to RETURN QUERY ...

Regards

Pavel Stehule

$$begin

perform(

with A as (select generate_series(1,3) as foo)

select foo from A

);

end$$;

This is 'DO' statement. Also I think this is legitimate bug:

you can do perform func(foo_id) from something;

but not

with something as (something)
perform func(foo_id) from something;

this might do as workaround:
do
$$begin
perform(
with A as (select 1 as foo)
select array(select foo from A)
);
end$$;

merlin

Added to TODO:

Improve PERFORM handling of WITH queries or document limitation

---------------------------------------------------------------------------

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.

(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@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 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/&gt;
We Deliver Quality.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

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 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/&gt;
We Deliver Quality.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On Tue, Sep 6, 2011 at 1:43 PM, Bruce Momjian <bruce@momjian.us> wrote:

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

Based on previous experience with PL/pgsql, my understanding is that
PL/pgsql basically replaces "perform" with "select" to get the query
that it actually runs. You'd get the same error from:

rhaas=# select (select relname from pg_class);
ERROR: more than one row returned by a subquery used as an expression

I've never really liked this behavior, but I don't have a clear idea
what to do about it.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Wed, Oct 19, 2011 at 7:36 AM, Robert Haas <robertmhaas@gmail.com> wrote:

On Tue, Sep 6, 2011 at 1:43 PM, Bruce Momjian <bruce@momjian.us> wrote:

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

Based on previous experience with PL/pgsql, my understanding is that
PL/pgsql basically replaces "perform" with "select" to get the query
that it actually runs.  You'd get the same error from:

rhaas=# select (select relname from pg_class);
ERROR:  more than one row returned by a subquery used as an expression

I've never really liked this behavior, but I don't have a clear idea
what to do about it.

yeah. it's an interesting thought experiment to try and come up with
a wrapper in the form of
wrap(query);

That's efficient, guarantees that 'query' is completely run, and does
not error no matter how many rows or columns 'query' comes back with.

I've got:
select min(1) from (query) q;

The point being, how do I convert any query to a non WITH variant so
it can be PERFORM'd? Anyways, I always thought having to do perform
at all was pretty weak sauce -- not sure why it's required.

merlin

Merlin Moncure <mmoncure@gmail.com> writes:

The point being, how do I convert any query to a non WITH variant so
it can be PERFORM'd? Anyways, I always thought having to do perform
at all was pretty weak sauce -- not sure why it's required.

Possibly it was an Oracle compatibility thing ... anyone know PL/SQL
well enough to say how this works there?

I suppose you could argue that selecting a value and implicitly throwing
it away is confusing to novices, but on the other hand I've seen a whole
lot of novices confused by the need to write PERFORM instead of SELECT.
I think it wouldn't be an unreasonable thing to just interpret a SELECT
with no INTO clause as being a PERFORM (ie execute and discard results).
Then we'd not have to do anything magic for commands starting with WITH.

regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> wrote:

I think it wouldn't be an unreasonable thing to just interpret a
SELECT with no INTO clause as being a PERFORM (ie execute and
discard results).

FWIW, that would probably confuse people coming from MS SQL Server
or Sybase ASE, since doing that in Transact-SQL would return a
result set. Any stored procedure can produce an arbitrarily
intermixed stream of result sets, information lines, and error
messages.

-Kevin

I wrote:

Merlin Moncure <mmoncure@gmail.com> writes:

The point being, how do I convert any query to a non WITH variant so
it can be PERFORM'd? Anyways, I always thought having to do perform
at all was pretty weak sauce -- not sure why it's required.

Possibly it was an Oracle compatibility thing ... anyone know PL/SQL
well enough to say how this works there?

After writing that, I remembered I had an old PL/SQL manual sitting
about, so I took a look. So far as I can see, there is no PERFORM
statement in PL/SQL, and no SELECT-without-INTO either; that is, the
functionality of executing a SELECT and discarding the result simply
isn't there.

So at this point it looks like we made up PERFORM out of whole cloth,
and we could just as easily choose to do it another way. Jan, do you
remember anything about the reasoning for PERFORM?

regards, tom lane

On 10/20/2011 05:23 AM, Tom Lane wrote:

I wrote:

Merlin Moncure<mmoncure@gmail.com> writes:

The point being, how do I convert any query to a non WITH variant so
it can be PERFORM'd? Anyways, I always thought having to do perform
at all was pretty weak sauce -- not sure why it's required.

Possibly it was an Oracle compatibility thing ... anyone know PL/SQL
well enough to say how this works there?

After writing that, I remembered I had an old PL/SQL manual sitting
about, so I took a look. So far as I can see, there is no PERFORM
statement in PL/SQL, and no SELECT-without-INTO either; that is, the
functionality of executing a SELECT and discarding the result simply
isn't there.

So at this point it looks like we made up PERFORM out of whole cloth,
and we could just as easily choose to do it another way.

How does PL/SQL handle multiple result set returns?

PL/PgSQL doesn't currently support that, but if true stored procedures
land up in someone's sights down the track it'll be important to be able
to support multiple result sets. If compatibility is of interest, then
it'd be good to know whether PL/SQL uses "RETURN SELECT" or just
"SELECT" to produce a result set.

If it just uses "SELECT" (and it sounds like it does from the above)
then perhaps retaining that meaning, and thus disallowing it from
functions that cannot return multiple result sets, would be better. When
true stored procs are implemented they can then permit bare SELECTs,
emitting their output as a resultset.

--
Craig Ringer

2011/10/19 Tom Lane <tgl@sss.pgh.pa.us>:

I wrote:

Merlin Moncure <mmoncure@gmail.com> writes:

The point being, how do I convert any query to a non WITH variant so
it can be PERFORM'd?  Anyways, I always thought having to do perform
at all was pretty weak sauce -- not sure why it's required.

Possibly it was an Oracle compatibility thing ... anyone know PL/SQL
well enough to say how this works there?

After writing that, I remembered I had an old PL/SQL manual sitting
about, so I took a look.  So far as I can see, there is no PERFORM
statement in PL/SQL, and no SELECT-without-INTO either; that is, the
functionality of executing a SELECT and discarding the result simply
isn't there.

So at this point it looks like we made up PERFORM out of whole cloth,
and we could just as easily choose to do it another way.  Jan, do you
remember anything about the reasoning for PERFORM?

It has a CALL statement, or procedures can be called directly.

Regards

Pavel Stehule

I suppose you could argue that selecting a value and implicitly throwing
it away is confusing to novices, but on the other hand I've seen a whole
lot of novices confused by the need to write PERFORM instead of SELECT.
I think it wouldn't be an unreasonable thing to just interpret a SELECT
with no INTO clause as being a PERFORM (ie execute and discard results).
Then we'd not have to do anything magic for commands starting with WITH.

regards, tom lane

it would be really a good idea to allow SELECT without INTO in plpgsql.

PERFORM just makes things much more complicated, without actually adding any
really working "protection" from misusing SELECT without INTO.

With best regards,
-- Valentin Gogichashvili

2011/10/20 Valentine Gogichashvili <valgog@gmail.com>:

I suppose you could argue that selecting a value and implicitly throwing
it away is confusing to novices, but on the other hand I've seen a whole
lot of novices confused by the need to write PERFORM instead of SELECT.
I think it wouldn't be an unreasonable thing to just interpret a SELECT
with no INTO clause as being a PERFORM (ie execute and discard results).
Then we'd not have to do anything magic for commands starting with WITH.

                       regards, tom lane

it would be really a good idea to allow SELECT without INTO in plpgsql.

SELECT without INTO is useless in plpgsql - because you have to drop result.

regards

Pavel Stehule

On Thu, Oct 20, 2011 at 2:28 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

it would be really a good idea to allow SELECT without INTO in plpgsql.

SELECT without INTO is useless in plpgsql - because you have to drop result.

not if you're calling a function:
select func();

merlin

it would be really a good idea to allow SELECT without INTO in plpgsql.

SELECT without INTO is useless in plpgsql - because you have to drop

result.

not if you're calling a function:
select func();

or calling bunch of functions:

SELECT func(param) FROM some_subselect_with_params;

or if you do not have writable CTEs yet and have a dream of calling
something like:

INSERT INTO some_table_with_data ...
RETURNING func(some_generated_field);

And, ernestly, if it were useless to have SELECT without INTO in plpgsql,
there also would be no PERFORM command in plpgsql...

Best regards,

-- Valentine Gogichashvili

2011/10/20 Merlin Moncure <mmoncure@gmail.com>:

On Thu, Oct 20, 2011 at 2:28 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

it would be really a good idea to allow SELECT without INTO in plpgsql.

SELECT without INTO is useless in plpgsql - because you have to drop result.

not if you're calling a function:
select func();

it is correct just for void function.

Pavel

Valentine Gogichashvili <valgog@gmail.com> writes:

And, ernestly, if it were useless to have SELECT without INTO in plpgsql,
there also would be no PERFORM command in plpgsql...

Precisely. Pavel's claim is nonsense. The only real question is how
useful is it to call it PERFORM instead of SELECT.

regards, tom lane