PL/pgSQL PERFORM with CTE

Hello

2013/8/20 David E. Wheeler <david@justatheory.com>

Hackers,

This seems reasonable:

david=# DO $$
david$# BEGIN
david$# WITH now AS (SELECT now())
david$# SELECT * from now;
david$# END;
david$# $$;
ERROR: query has no destination for result data
HINT: If you want to discard the results of a SELECT, use PERFORM
instead.
CONTEXT: PL/pgSQL function inline_code_block line 3 at SQL statement

This not so much:

david=# DO $$
david$# BEGIN
david$# WITH now AS (SELECT now())
david$# PERFORM * from now;
david$# END;
david$# $$;
ERROR: syntax error at or near "PERFORM"
LINE 4: PERFORM * from now;
^
Parser bug in PL/pgSQL, perhaps?

no

you cannot use a PL/pgSQL statement inside SQL statement.

Regards

Pavel

Hi Pavel,

On Aug 20, 2013, at 2:11 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

david=# DO $$
david$# BEGIN
david$# WITH now AS (SELECT now())
david$# PERFORM * from now;
david$# END;
david$# $$;
ERROR: syntax error at or near "PERFORM"
LINE 4: PERFORM * from now;
^
Parser bug in PL/pgSQL, perhaps?

no

you cannot use a PL/pgSQL statement inside SQL statement.

Well, there ought to be *some* way to tell PL/pgSQL to discard the result. Right now I am adding a variable to select into but never otherwise use. Inelegant, IMHO. Perhaps I’m missing some other way to do it?

If so, it would help if the hint suggesting the use of PERFORM pointed to such alternatives.

Best,

David

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2013/8/20 David E. Wheeler <david@justatheory.com>

Hi Pavel,

On Aug 20, 2013, at 2:11 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

david=# DO $$
david$# BEGIN
david$# WITH now AS (SELECT now())
david$# PERFORM * from now;
david$# END;
david$# $$;
ERROR: syntax error at or near "PERFORM"
LINE 4: PERFORM * from now;
^
Parser bug in PL/pgSQL, perhaps?

no

you cannot use a PL/pgSQL statement inside SQL statement.

Well, there ought to be *some* way to tell PL/pgSQL to discard the result.
Right now I am adding a variable to select into but never otherwise use.
Inelegant, IMHO. Perhaps I’m missing some other way to do it?

If so, it would help if the hint suggesting the use of PERFORM pointed to
such alternatives.

postgres=# DO $$
BEGIN
PERFORM * FROM (WITH now AS (SELECT now())
SELECT * from now) x;
END;
$$;
DO
postgres=#

Regards

Pavel

2013/8/20 Andres Freund <andres@2ndquadrant.com>

On 2013-08-20 14:15:55 +0200, David E. Wheeler wrote:

Hi Pavel,

On Aug 20, 2013, at 2:11 PM, Pavel Stehule <pavel.stehule@gmail.com>

wrote:

david=# DO $$
david$# BEGIN
david$# WITH now AS (SELECT now())
david$# PERFORM * from now;
david$# END;
david$# $$;
ERROR: syntax error at or near "PERFORM"
LINE 4: PERFORM * from now;
^
Parser bug in PL/pgSQL, perhaps?

no

you cannot use a PL/pgSQL statement inside SQL statement.

Well, there ought to be *some* way to tell PL/pgSQL to discard the

result. Right now I am adding a variable to select into but never otherwise
use. Inelegant, IMHO. Perhaps I’m missing some other way to do it?

If so, it would help if the hint suggesting the use of PERFORM pointed

to such alternatives.

Not that that's elegant but IIRC PERFORM (WITH ...) ought to work. I
don't think the intermingled plpgsql/sql grammars allow a nice way right
now.

+1

Pavel

On 8/20/13 2:21 PM, Pavel Stehule wrote:

2013/8/20 David E. Wheeler <david@justatheory.com>

Well, there ought to be *some* way to tell PL/pgSQL to discard the result.
Right now I am adding a variable to select into but never otherwise use.
Inelegant, IMHO. Perhaps I’m missing some other way to do it?

If so, it would help if the hint suggesting the use of PERFORM pointed to
such alternatives.

postgres=# DO $$
BEGIN
PERFORM * FROM (WITH now AS (SELECT now())
SELECT * from now) x;
END;
$$;
DO

.. which doesn't work if you want to use table-modifying CTEs.

Regards,
Marko Tiikkaja

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 2013-08-20 14:15:55 +0200, David E. Wheeler wrote:

Hi Pavel,

On Aug 20, 2013, at 2:11 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

david=# DO $$
david$# BEGIN
david$# WITH now AS (SELECT now())
david$# PERFORM * from now;
david$# END;
david$# $$;
ERROR: syntax error at or near "PERFORM"
LINE 4: PERFORM * from now;
^
Parser bug in PL/pgSQL, perhaps?

no

you cannot use a PL/pgSQL statement inside SQL statement.

Well, there ought to be *some* way to tell PL/pgSQL to discard the result. Right now I am adding a variable to select into but never otherwise use. Inelegant, IMHO. Perhaps I’m missing some other way to do it?

If so, it would help if the hint suggesting the use of PERFORM pointed to such alternatives.

Not that that's elegant but IIRC PERFORM (WITH ...) ought to work. I
don't think the intermingled plpgsql/sql grammars allow a nice way right
now.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Aug 20, 2013, at 2:24 PM, Marko Tiikkaja <marko@joh.to> wrote:

postgres=# DO $$
BEGIN
PERFORM * FROM (WITH now AS (SELECT now())
SELECT * from now) x;
END;
$$;
DO

.. which doesn't work if you want to use table-modifying CTEs.

Which, in fact, is exactly my use case (though not what I posted upthread).

Best,

David

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2013/8/20 David E. Wheeler <david@justatheory.com>

On Aug 20, 2013, at 2:24 PM, Marko Tiikkaja <marko@joh.to> wrote:

postgres=# DO $$
BEGIN
PERFORM * FROM (WITH now AS (SELECT now())
SELECT * from now) x;
END;
$$;
DO

.. which doesn't work if you want to use table-modifying CTEs.

Which, in fact, is exactly my use case (though not what I posted upthread).

but it works

postgres=# do $$begin with x as (select 10) insert into omega select * from
x; end;$$;
DO

Regards

Pavel

On Aug 20, 2013, at 2:31 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

but it works

postgres=# do $$begin with x as (select 10) insert into omega select * from x; end;$$;
DO

But this does not:

david=# DO $$
david$# BEGIN
david$# PERFORM * FROM (
david$# WITH inserted AS (
david$# INSERT INTO foo values (1) RETURNING id
david$# ) SELECT inserted.id
david$# ) x;
david$# END;
david$# $$;
ERROR: WITH clause containing a data-modifying statement must be at the top level
LINE 2: WITH inserted AS (
^
QUERY: SELECT * FROM (
WITH inserted AS (
INSERT INTO foo values (1) RETURNING id
) SELECT inserted.id
) x
CONTEXT: PL/pgSQL function inline_code_block line 3 at PERFORM

Best,

David

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2013/8/20 David E. Wheeler <david@justatheory.com>

On Aug 20, 2013, at 2:31 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

but it works

postgres=# do $$begin with x as (select 10) insert into omega select *

from x; end;$$;

DO

But this does not:

david=# DO $$
david$# BEGIN
david$# PERFORM * FROM (
david$# WITH inserted AS (
david$# INSERT INTO foo values (1) RETURNING id
david$# ) SELECT inserted.id
david$# ) x;
david$# END;
david$# $$;
ERROR: WITH clause containing a data-modifying statement must be at the
top level
LINE 2: WITH inserted AS (
^
QUERY: SELECT * FROM (
WITH inserted AS (
INSERT INTO foo values (1) RETURNING id
) SELECT inserted.id
) x
CONTEXT: PL/pgSQL function inline_code_block line 3 at PERFORM

yes, in this context you should not use a PERFORM

PL/pgSQL protect you before useless queries - so you can use a CTE without
returned result directly or CTE with result via PERFORM statement (and in
this case it must be unmodifing CTE).

Sorry, I don't see any problem - why you return some from CTE and then you
throw this result?

On Tue, Aug 20, 2013 at 7:25 AM, Andres Freund <andres@2ndquadrant.com> wrote:

On 2013-08-20 14:15:55 +0200, David E. Wheeler wrote:

Hi Pavel,

On Aug 20, 2013, at 2:11 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

david=# DO $$
david$# BEGIN
david$# WITH now AS (SELECT now())
david$# PERFORM * from now;
david$# END;
david$# $$;
ERROR: syntax error at or near "PERFORM"
LINE 4: PERFORM * from now;
^
Parser bug in PL/pgSQL, perhaps?

no

you cannot use a PL/pgSQL statement inside SQL statement.

Well, there ought to be *some* way to tell PL/pgSQL to discard the result. Right now I am adding a variable to select into but never otherwise use. Inelegant, IMHO. Perhaps I’m missing some other way to do it?

If so, it would help if the hint suggesting the use of PERFORM pointed to such alternatives.

Not that that's elegant but IIRC PERFORM (WITH ...) ought to work. I
don't think the intermingled plpgsql/sql grammars allow a nice way right
now.

I think the way forward is to remove the restriction such that data
returning queries must be PERFORM'd.

merlin

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2013/8/20 Merlin Moncure <mmoncure@gmail.com>

On Tue, Aug 20, 2013 at 7:25 AM, Andres Freund <andres@2ndquadrant.com>
wrote:

On 2013-08-20 14:15:55 +0200, David E. Wheeler wrote:

Hi Pavel,

On Aug 20, 2013, at 2:11 PM, Pavel Stehule <pavel.stehule@gmail.com>

wrote:

david=# DO $$
david$# BEGIN
david$# WITH now AS (SELECT now())
david$# PERFORM * from now;
david$# END;
david$# $$;
ERROR: syntax error at or near "PERFORM"
LINE 4: PERFORM * from now;
^
Parser bug in PL/pgSQL, perhaps?

no

you cannot use a PL/pgSQL statement inside SQL statement.

Well, there ought to be *some* way to tell PL/pgSQL to discard the

result. Right now I am adding a variable to select into but never otherwise
use. Inelegant, IMHO. Perhaps I’m missing some other way to do it?

If so, it would help if the hint suggesting the use of PERFORM pointed

to such alternatives.

Not that that's elegant but IIRC PERFORM (WITH ...) ought to work. I
don't think the intermingled plpgsql/sql grammars allow a nice way right
now.

I think the way forward is to remove the restriction such that data
returning queries must be PERFORM'd

I disagree, current rule has sense.

Pavel

On Aug 20, 2013, at 2:41 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

yes, in this context you should not use a PERFORM

PL/pgSQL protect you before useless queries - so you can use a CTE without returned result directly or CTE with result via PERFORM statement (and in this case it must be unmodifing CTE).

Sorry, I don't see any problem - why you return some from CTE and then you throw this result?

I am passing the values returned from a CTE to a call to pg_notify(). I do not care to collect the output of pg_notify(), which returns VOID.

Best,

David

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2013-08-20 14:35 keltez�ssel, David E. Wheeler �rta:

On Aug 20, 2013, at 2:31 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

but it works

postgres=# do $$begin with x as (select 10) insert into omega select * from x; end;$$;
DO

But this does not:

david=# DO $$
david$# BEGIN
david$# PERFORM * FROM (
david$# WITH inserted AS (
david$# INSERT INTO foo values (1) RETURNING id
david$# ) SELECT inserted.id
david$# ) x;
david$# END;
david$# $$;
ERROR: WITH clause containing a data-modifying statement must be at the top level
LINE 2: WITH inserted AS (
^
QUERY: SELECT * FROM (
WITH inserted AS (
INSERT INTO foo values (1) RETURNING id
) SELECT inserted.id
) x
CONTEXT: PL/pgSQL function inline_code_block line 3 at PERFORM

This is the same error as if you put the WITH into a subquery,
which is what PERFORM does.

Proof:

SELECT * FROM (
WITH inserted AS (
INSERT INTO foo values (1) RETURNING id
) SELECT inserted.id
) x;

Best regards,
Zolt�n B�sz�rm�nyi

--
----------------------------------
Zolt�n B�sz�rm�nyi
Cybertec Sch�nig & Sch�nig GmbH
Gr�hrm�hlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de
http://www.postgresql.at/

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Aug 20, 2013, at 2:44 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

I think the way forward is to remove the restriction such that data
returning queries must be PERFORM'd

I disagree, current rule has sense.

Perhaps a DECLARE FUNCTION attribute that turns off the functionality, then?

Best,

David

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Tue, Aug 20, 2013 at 7:44 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2013/8/20 Merlin Moncure <mmoncure@gmail.com>

On Tue, Aug 20, 2013 at 7:25 AM, Andres Freund <andres@2ndquadrant.com>
wrote:

On 2013-08-20 14:15:55 +0200, David E. Wheeler wrote:

Hi Pavel,

On Aug 20, 2013, at 2:11 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

david=# DO $$
david$# BEGIN
david$# WITH now AS (SELECT now())
david$# PERFORM * from now;
david$# END;
david$# $$;
ERROR: syntax error at or near "PERFORM"
LINE 4: PERFORM * from now;
^
Parser bug in PL/pgSQL, perhaps?

no

you cannot use a PL/pgSQL statement inside SQL statement.

Well, there ought to be *some* way to tell PL/pgSQL to discard the
result. Right now I am adding a variable to select into but never otherwise
use. Inelegant, IMHO. Perhaps I’m missing some other way to do it?

If so, it would help if the hint suggesting the use of PERFORM pointed
to such alternatives.

Not that that's elegant but IIRC PERFORM (WITH ...) ought to work. I
don't think the intermingled plpgsql/sql grammars allow a nice way right
now.

I think the way forward is to remove the restriction such that data
returning queries must be PERFORM'd

I disagree, current rule has sense.

Curious what your thinking is there.

merlin

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2013/8/20 David E. Wheeler <david@justatheory.com>

On Aug 20, 2013, at 2:41 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

yes, in this context you should not use a PERFORM

PL/pgSQL protect you before useless queries - so you can use a CTE

without returned result directly or CTE with result via PERFORM statement
(and in this case it must be unmodifing CTE).

Sorry, I don't see any problem - why you return some from CTE and then

you throw this result?

I am passing the values returned from a CTE to a call to pg_notify(). I do
not care to collect the output of pg_notify(), which returns VOID.

it is little bit different issue - PL/pgSQL doesn't check if returned type
is VOID - it can be allowed, I am thinking. So check of empty result can be
enhanced.

Regards

Pavel

On 8/20/13 2:53 PM, Pavel Stehule wrote:

2013/8/20 David E. Wheeler <david@justatheory.com>

I am passing the values returned from a CTE to a call to pg_notify(). I do
not care to collect the output of pg_notify(), which returns VOID.

it is little bit different issue - PL/pgSQL doesn't check if returned type
is VOID - it can be allowed, I am thinking. So check of empty result can be
enhanced.

That still doesn't help at all in the case where the function returns
something, but you simply don't care about the result.

That said, I don't think this issue is big enough to start radically
changing how SELECT without INTO works -- you can always get around this
limitation by SELECTing into a variable, as David mentioned in his
original message. It's annoying, but it works.

Regards,
Marko Tiikkaja

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Aug 20, 2013, at 2:53 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

I am passing the values returned from a CTE to a call to pg_notify(). I do not care to collect the output of pg_notify(), which returns VOID.

it is little bit different issue - PL/pgSQL doesn't check if returned type is VOID - it can be allowed, I am thinking. So check of empty result can be enhanced.

I am confused. I do not need to check the result (except via FOUND). But I am sure I can think of other situations where I am calling something where I do not care about the result, even if it returns one.

Best,

David

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers