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

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.

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

On Thu, Mar 24, 2011 at 10:36 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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(at)alliedtesting(dot)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(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.
>

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

depstein(at)alliedtesting(dot)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(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.
>

--
Bruce Momjian <bruce(at)momjian(dot)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(at)momjian(dot)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(at)gmail(dot)com> wrote:
> On Tue, Sep 6, 2011 at 1:43 PM, Bruce Momjian <bruce(at)momjian(dot)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(at)gmail(dot)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(at)sss(dot)pgh(dot)pa(dot)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(at)gmail(dot)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(at)gmail(dot)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(at)sss(dot)pgh(dot)pa(dot)us>:
> I wrote:
>> Merlin Moncure <mmoncure(at)gmail(dot)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

>                        regards, tom lane
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

>
>
> 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(at)gmail(dot)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

> PERFORM just makes things much more complicated, without actually adding any
> really working "protection" from misusing SELECT without INTO.
> With best regards,
> -- Valentin Gogichashvili

On Thu, Oct 20, 2011 at 2:28 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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(at)gmail(dot)com>:
> On Thu, Oct 20, 2011 at 2:28 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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

>
> merlin
>

Valentine Gogichashvili <valgog(at)gmail(dot)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

2011/10/20 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Valentine Gogichashvili <valgog(at)gmail(dot)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.

I didn't design a PERFORM statement. There is two views - somebody
from sybase's family know so SELECT without into is forwarded to
client. This functionality is missing on Oracle's family. Is true so
PERFORM statement is strange, but maybe it's open door for sybase's
functionality that was not implemented ever.

Regards

Pavel Stehule

>
>                        regards, tom lane
>

Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> I didn't design a PERFORM statement. There is two views - somebody
> from sybase's family know so SELECT without into is forwarded to
> client. This functionality is missing on Oracle's family. Is true so
> PERFORM statement is strange, but maybe it's open door for sybase's
> functionality that was not implemented ever.

I cannot imagine that we'd ever make SELECT inside a plpgsql function
act like that. Functions have no business directly transmitting
information to the client; if they tried, they'd most likely just break
the FE/BE protocol.

There might be use for such a thing in a hypothetical "real stored
procedure language" where the code is executing in a context entirely
different from what Postgres functions run in ... but that language
would be something different from plpgsql.

I grant the argument that people coming from Sybase-ish DBs might be
confused by this; but the current arrangement is also confusing lots
of people, so I don't think that argument has all that much weight.

regards, tom lane

On Thu, Oct 20, 2011 at 3:01 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>> I didn't design a PERFORM statement. There is two views - somebody
>> from sybase's family know so SELECT without into is forwarded to
>> client. This functionality is missing on Oracle's family. Is true so
>> PERFORM statement is strange,  but maybe it's open door for sybase's
>> functionality that was not implemented ever.
>
> I cannot imagine that we'd ever make SELECT inside a plpgsql function
> act like that.  Functions have no business directly transmitting
> information to the client; if they tried, they'd most likely just break
> the FE/BE protocol.
>
> There might be use for such a thing in a hypothetical "real stored
> procedure language" where the code is executing in a context entirely
> different from what Postgres functions run in ... but that language
> would be something different from plpgsql.

small aside: I disagreed with this point a while back but I'm coming
around to your point of view...

merlin

2011/10/20 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>> I didn't design a PERFORM statement. There is two views - somebody
>> from sybase's family know so SELECT without into is forwarded to
>> client. This functionality is missing on Oracle's family. Is true so
>> PERFORM statement is strange,  but maybe it's open door for sybase's
>> functionality that was not implemented ever.
>
> I cannot imagine that we'd ever make SELECT inside a plpgsql function
> act like that.  Functions have no business directly transmitting
> information to the client; if they tried, they'd most likely just break
> the FE/BE protocol.
>
> There might be use for such a thing in a hypothetical "real stored
> procedure language" where the code is executing in a context entirely
> different from what Postgres functions run in ... but that language
> would be something different from plpgsql.
>
> I grant the argument that people coming from Sybase-ish DBs might be
> confused by this; but the current arrangement is also confusing lots
> of people, so I don't think that argument has all that much weight.

I agree with you you in almost all - Sybase-ish SELECT has sense for
procedures only (in PL/pgSQL) - In SQL/PSM is natural for table
functions. I disagree with Merlin or Valentine from one reason - What
is sense of SELECT, that has not processed result? Is it correct from
language design perspective?

I can do a write a query with sense - like SELECT fx(i) FROM
generate_series(1,1000) or query without sense - like SELECT 1 FROM
generate_series(1,1000) - when we enable a SELECT without INTO. And
there is next question - is first select a good idea - from
readability perspective - in PL/pgSQL - is not better to use a SQL
language??

Regards

Pavel Stehule

p.s. other question is implementation of PERFORM - that is it a just
SELECT synonym,

>
>                        regards, tom lane
>