Re: PL/pgSQL PERFORM with CTE

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?

Best,

David

Hello

2013/8/20 David E. Wheeler <david(at)justatheory(dot)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

>
> Best,
>
> David
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

Hi Pavel,

On Aug 20, 2013, at 2:11 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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

2013/8/20 David E. Wheeler <david(at)justatheory(dot)com>

> Hi Pavel,
>
> On Aug 20, 2013, at 2:11 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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

>
> Best,
>
> David
>
>

2013/8/20 Andres Freund <andres(at)2ndquadrant(dot)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(dot)stehule(at)gmail(dot)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

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

On 8/20/13 2:21 PM, Pavel Stehule wrote:
> 2013/8/20 David E. Wheeler <david(at)justatheory(dot)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

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

On Aug 20, 2013, at 2:24 PM, Marko Tiikkaja <marko(at)joh(dot)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

2013/8/20 David E. Wheeler <david(at)justatheory(dot)com>

> On Aug 20, 2013, at 2:24 PM, Marko Tiikkaja <marko(at)joh(dot)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

>
> Best,
>
> David
>
>

On Aug 20, 2013, at 2:31 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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

2013/8/20 David E. Wheeler <david(at)justatheory(dot)com>

> On Aug 20, 2013, at 2:31 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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?

> Best,
>
> David
>
>

On Tue, Aug 20, 2013 at 7:25 AM, Andres Freund <andres(at)2ndquadrant(dot)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(dot)stehule(at)gmail(dot)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

2013/8/20 Merlin Moncure <mmoncure(at)gmail(dot)com>

> On Tue, Aug 20, 2013 at 7:25 AM, Andres Freund <andres(at)2ndquadrant(dot)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(dot)stehule(at)gmail(dot)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

>
> merlin
>

On Aug 20, 2013, at 2:41 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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

2013-08-20 14:35 keltezéssel, David E. Wheeler írta:
> On Aug 20, 2013, at 2:31 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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/

On Aug 20, 2013, at 2:44 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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

On Tue, Aug 20, 2013 at 7:44 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>
>
>
> 2013/8/20 Merlin Moncure <mmoncure(at)gmail(dot)com>
>>
>> On Tue, Aug 20, 2013 at 7:25 AM, Andres Freund <andres(at)2ndquadrant(dot)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(dot)stehule(at)gmail(dot)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

2013/8/20 David E. Wheeler <david(at)justatheory(dot)com>

> On Aug 20, 2013, at 2:41 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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

>
> Best,
>
> David
>
>

On 8/20/13 2:53 PM, Pavel Stehule wrote:
> 2013/8/20 David E. Wheeler <david(at)justatheory(dot)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

On Aug 20, 2013, at 2:53 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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

2013/8/20 David E. Wheeler <david(at)justatheory(dot)com>

> On Aug 20, 2013, at 2:53 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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.
>

When you would to ignore result, then you should to use a PERFORM -
actually, it is limited now and should be fixed. Have no problem with it.

I don't would to enable a free unbound statement that returns result.

Regards

Pavel

>
> Best,
>
> David
>
>

On Aug 20, 2013, at 3:05 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:

> When you would to ignore result, then you should to use a PERFORM - actually, it is limited now and should be fixed. Have no problem with it.

Glad to have you on board. :-)

> I don't would to enable a free unbound statement that returns result.

I have no pony in that race. I think it is useful, though I prefer to unit test things enough that I would be fine without it.

But even without it, there may be times when I want to discard a result in a function that *does* return a value -- likely a different value. So there needs to be a way to distinguish statements that should return a value and those that do not.

Best,

David

2013/8/20 David E. Wheeler <david(at)justatheory(dot)com>

> On Aug 20, 2013, at 3:05 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
>
> > When you would to ignore result, then you should to use a PERFORM -
> actually, it is limited now and should be fixed. Have no problem with it.
>
> Glad to have you on board. :-)
>
> > I don't would to enable a free unbound statement that returns result.
>
> I have no pony in that race. I think it is useful, though I prefer to unit
> test things enough that I would be fine without it.
>
> But even without it, there may be times when I want to discard a result in
> a function that *does* return a value -- likely a different value. So there
> needs to be a way to distinguish statements that should return a value and
> those that do not.
>

can you show some examples, please

Pavel

> Best,
>
> David
>
>

On Aug 20, 2013, at 3:18 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:

> can you show some examples, please

This is not dissimilar to what I am actually doing:

CREATE TABLE foo (id SERIAL PRIMARY KEY, name TEXT);

CREATE OR REPLACE FUNCTION shipit (
VARIADIC things TEXT[]
) RETURNS BOOL LANGUAGE plpgsql AS $$
BEGIN
WITH inserted AS (
INSERT INTO foo (name)
SELECT * FROM unnest(things)
RETURNING id
)
PERFORM pg_notify(
'inserted ids',
ARRAY(SELECT * FROM inserted)::text
);
RETURN FOUND;
END;
$$;

Only I am using a dummy row variable instead of PERFORM, of course.

Best,

David

2013/8/20 David E. Wheeler <david(at)justatheory(dot)com>

> On Aug 20, 2013, at 3:18 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
>
> > can you show some examples, please
>
> This is not dissimilar to what I am actually doing:
>
> CREATE TABLE foo (id SERIAL PRIMARY KEY, name TEXT);
>
> CREATE OR REPLACE FUNCTION shipit (
> VARIADIC things TEXT[]
> ) RETURNS BOOL LANGUAGE plpgsql AS $$
> BEGIN
> WITH inserted AS (
> INSERT INTO foo (name)
> SELECT * FROM unnest(things)
> RETURNING id
> )
> PERFORM pg_notify(
> 'inserted ids',
> ARRAY(SELECT * FROM inserted)::text
> );
> RETURN FOUND;
> END;
> $$;
>
> Only I am using a dummy row variable instead of PERFORM, of course.
>

pg_notify returns void, so there are no necessary casting to void

so enhanced check - so all returned columns are void should be enough

Regards

Pavel

>
> Best,
>
> David
>
>

On Aug 20, 2013, at 3:38 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:

> pg_notify returns void, so there are no necessary casting to void
>
> so enhanced check - so all returned columns are void should be enough

What if I call another function I wrote myself that returns an INT, but I do not care about the INT? Maybe that function does the insert and returns the number of inserted rows.

I can think of all kinds of reasons this might be the case; whether they are good or bad approaches is immaterial: sometimes you work with what you have.

I am find with PERFORM to determine when a query's results should be discarded. I just think it needs to cover a few more cases.

Best,

David

2013/8/20 David E. Wheeler <david(at)justatheory(dot)com>

> On Aug 20, 2013, at 3:38 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
>
> > pg_notify returns void, so there are no necessary casting to void
> >
> > so enhanced check - so all returned columns are void should be enough
>
> What if I call another function I wrote myself that returns an INT, but I
> do not care about the INT? Maybe that function does the insert and returns
> the number of inserted rows.
>
> I can think of all kinds of reasons this might be the case; whether they
> are good or bad approaches is immaterial: sometimes you work with what you
> have.
>
> I am find with PERFORM to determine when a query's results should be
> discarded. I just think it needs to cover a few more cases.
>

yes

I understand. I'll look, how PERFORM can be fixed

Regards

Pavel

>
> Best,
>
> David

On 08/20/2013 05:48 AM, Merlin Moncure wrote:
> On Tue, Aug 20, 2013 at 7:44 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>>
>>
>>
>> 2013/8/20 Merlin Moncure <mmoncure(at)gmail(dot)com>
>>>
>>> On Tue, Aug 20, 2013 at 7:25 AM, Andres Freund <andres(at)2ndquadrant(dot)com>

>>> 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.

I have to agree with Merlin. I've always thought the PERFORM thing was
a wart we'd get around to removing eventually. In what way is it a feature?

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> I have to agree with Merlin. I've always thought the PERFORM thing was
> a wart we'd get around to removing eventually. In what way is it a feature?

I'd always assumed it was a PL/SQL compatibility thing, but a look in a
PL/SQL reference doesn't turn up any such statement. So far as I can see,
the situation in Oracle PL/SQL is:
* SELECT must have an INTO clause;
* there isn't any way to execute a SELECT and just discard the result.

Jan might remember more about his thought process here, but I'm thinking
that he copied the SELECT-must-have-INTO rule and then chose to invent
a new statement for the case of wanting to discard the result. I think
you could make an argument for that being good from an oversight-detection
standpoint, but it's not a really strong argument. Particularly in view
of the difficulty we'd have in supporting WITH ... PERFORM ... nicely,
it doesn't seem unreasonable to just allow SELECT-without-INTO.

regards, tom lane

Tom,

> Jan might remember more about his thought process here, but I'm thinking
> that he copied the SELECT-must-have-INTO rule and then chose to invent
> a new statement for the case of wanting to discard the result. I think
> you could make an argument for that being good from an oversight-detection
> standpoint, but it's not a really strong argument. Particularly in view
> of the difficulty we'd have in supporting WITH ... PERFORM ... nicely,
> it doesn't seem unreasonable to just allow SELECT-without-INTO.

For my own part, I have to correct forgetting to substitute "PERORM" for
"SELECT" around 200 times each major PL/pgSQL project. So it would be
user-friendly for it to go away.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

2013/8/23 Josh Berkus <josh(at)agliodbs(dot)com>

> Tom,
>
>
> > Jan might remember more about his thought process here, but I'm thinking
> > that he copied the SELECT-must-have-INTO rule and then chose to invent
> > a new statement for the case of wanting to discard the result. I think
> > you could make an argument for that being good from an
> oversight-detection
> > standpoint, but it's not a really strong argument. Particularly in view
> > of the difficulty we'd have in supporting WITH ... PERFORM ... nicely,
> > it doesn't seem unreasonable to just allow SELECT-without-INTO.
>
> For my own part, I have to correct forgetting to substitute "PERORM" for
> "SELECT" around 200 times each major PL/pgSQL project. So it would be
> user-friendly for it to go away.
>

But it can have a different reason. In T-SQL (Microsoft or Sybase) or MySQL
a unbound query is used to direct transfer data to client side.

There

BEGIN
SELECT 10;
END;

doesn't mean "ignore result of query", but it means push result to client.

And we doesn't support this functionality, so I prefer doesn't allow this
syntax.

Regards

Pavel

>
> --
> Josh Berkus
> PostgreSQL Experts Inc.
> http://pgexperts.com
>

Pavel,

> But it can have a different reason. In T-SQL (Microsoft or Sybase) or MySQL
> a unbound query is used to direct transfer data to client side.

Are you planning to implement that in PL/pgSQL?

Currently, PL/pgSQL requires RETURN ____ in order to return a query
result to the caller. Is there some reason we'd change that?

If you're implementing TSQL-for-PostgreSQL, of course you might want to
have different behavior with SELECT. However, TSQL is not PL/pgSQL.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On Fri, Aug 23, 2013 at 12:51 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> Pavel,
>
>> But it can have a different reason. In T-SQL (Microsoft or Sybase) or MySQL
>> a unbound query is used to direct transfer data to client side.
>
> Are you planning to implement that in PL/pgSQL?
>
> Currently, PL/pgSQL requires RETURN ____ in order to return a query
> result to the caller. Is there some reason we'd change that?
>
> If you're implementing TSQL-for-PostgreSQL, of course you might want to
> have different behavior with SELECT. However, TSQL is not PL/pgSQL.

I don't think Pavel's point makes sense in the context of functions.
With stored procedures it might though -- but I don't see why that we
need to reserve behavior for SELECT without INTO -- it can behave
differently when executed with a hypothetical CALL.

merlin

2013/8/23 Josh Berkus <josh(at)agliodbs(dot)com>

> Pavel,
>
> > But it can have a different reason. In T-SQL (Microsoft or Sybase) or
> MySQL
> > a unbound query is used to direct transfer data to client side.
>
> Are you planning to implement that in PL/pgSQL?
>
>
yes. I would to see a stored procedures with this functionality in pg

> Currently, PL/pgSQL requires RETURN ____ in order to return a query
> result to the caller. Is there some reason we'd change that?
>
>
it is different functionality.

> If you're implementing TSQL-for-PostgreSQL, of course you might want to
> have different behavior with SELECT. However, TSQL is not PL/pgSQL.
>

I don't would to implement T-SQL. Same functionality has a PSM in MySQL.
And in this moment, there is not any blocker why this should not be in
Postgres.

Regards

Pavel

> --
> Josh Berkus
> PostgreSQL Experts Inc.
> http://pgexperts.com
>

2013/8/23 Merlin Moncure <mmoncure(at)gmail(dot)com>

> On Fri, Aug 23, 2013 at 12:51 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> > Pavel,
> >
> >> But it can have a different reason. In T-SQL (Microsoft or Sybase) or
> MySQL
> >> a unbound query is used to direct transfer data to client side.
> >
> > Are you planning to implement that in PL/pgSQL?
> >
> > Currently, PL/pgSQL requires RETURN ____ in order to return a query
> > result to the caller. Is there some reason we'd change that?
> >
> > If you're implementing TSQL-for-PostgreSQL, of course you might want to
> > have different behavior with SELECT. However, TSQL is not PL/pgSQL.
>
> I don't think Pavel's point makes sense in the context of functions.
> With stored procedures it might though -- but I don't see why that we
> need to reserve behavior for SELECT without INTO -- it can behave
> differently when executed with a hypothetical CALL.
>

I think so is not good if some programming language functionality does one
in one context (functions) and does something else in second context
(procedures).

On second hand, I am thinking so requirement PERFORM is good. A query that
does some, but result is ignored, is strange (and it can be a performance
fault), so we should not be too friendly in this use case.

PERFORM must be fixed, but should be used.

Regards

Pavel

>
> merlin
>

On Fri, Aug 23, 2013 at 1:38 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>
>
>
> 2013/8/23 Merlin Moncure <mmoncure(at)gmail(dot)com>
> I think so is not good if some programming language functionality does one
> in one context (functions) and does something else in second context
> (procedures).

It's not really different -- it means 'return if able'. Also there
are a lot of things that would have to be different for other reasons
especially transaction management. It's not reasonable to expect same
behavior in function vs procedure context -- especially in terms of
sending output to the caller.

> On second hand, I am thinking so requirement PERFORM is good. A query that
> does some, but result is ignored, is strange (and it can be a performance
> fault), so we should not be too friendly in this use case.

Completely disagree. There are many cases where this is *not*
strange. For example:
SELECT writing_func(some_col) FROM foo;

merlin

2013/8/23 Merlin Moncure <mmoncure(at)gmail(dot)com>

> On Fri, Aug 23, 2013 at 1:38 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
> >
> >
> >
> > 2013/8/23 Merlin Moncure <mmoncure(at)gmail(dot)com>
> > I think so is not good if some programming language functionality does
> one
> > in one context (functions) and does something else in second context
> > (procedures).
>
> It's not really different -- it means 'return if able'. Also there
> are a lot of things that would have to be different for other reasons
> especially transaction management. It's not reasonable to expect same
> behavior in function vs procedure context -- especially in terms of
> sending output to the caller.
>
> > On second hand, I am thinking so requirement PERFORM is good. A query
> that
> > does some, but result is ignored, is strange (and it can be a performance
> > fault), so we should not be too friendly in this use case.
>
> Completely disagree. There are many cases where this is *not*
> strange. For example:
> SELECT writing_func(some_col) FROM foo;
>

it is about a personal taste - if you prefer more verbose or less verbose
languages.

I feeling a PERFORM usage as something special and you example is nice
case, where I am think so PERFORM is good for verbosity.

Regards

Pavel

>
> merlin
>

On Aug 23, 2013, at 8:51 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:

> it is about a personal taste - if you prefer more verbose or less verbose languages.
>
> I feeling a PERFORM usage as something special and you example is nice case, where I am think so PERFORM is good for verbosity.

I really do not see the point of PERFORM in the current implementation of PL/pgSQL. If we were to allow SELECT to run when it is not returning a value or selecting into a variable, it would be unambiguous, since the other two cases require:

* Using RETURN (or RETURN QUERY)
* The INTO clause

I have come around to the position that I think Tom, Josh, and Merlin have all put forward, that PERFORM is unnecessary.

Unless Jan chimes in with something the rest of us have missed, it’s starting to feel like a consensus to me, other than your objections, of course.

Best,

David

2013/8/23 David E. Wheeler <david(at)justatheory(dot)com>

> On Aug 23, 2013, at 8:51 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
>
> > it is about a personal taste - if you prefer more verbose or less
> verbose languages.
> >
> > I feeling a PERFORM usage as something special and you example is nice
> case, where I am think so PERFORM is good for verbosity.
>
> I really do not see the point of PERFORM in the current implementation of
> PL/pgSQL. If we were to allow SELECT to run when it is not returning a
> value or selecting into a variable, it would be unambiguous, since the
> other two cases require:
>
> * Using RETURN (or RETURN QUERY)
> * The INTO clause
>
> I have come around to the position that I think Tom, Josh, and Merlin have
> all put forward, that PERFORM is unnecessary.
>
> Unless Jan chimes in with something the rest of us have missed, it’s
> starting to feel like a consensus to me, other than your objections, of
> course.
>
>
ook

Regards

Pavel

> Best,
>
> David
>
>

On 08/23/2013 11:30 AM, Pavel Stehule wrote:
> 2013/8/23 Josh Berkus <josh(at)agliodbs(dot)com>
>
>> Pavel,
>>
>>> But it can have a different reason. In T-SQL (Microsoft or Sybase) or
>> MySQL
>>> a unbound query is used to direct transfer data to client side.
>>
>> Are you planning to implement that in PL/pgSQL?
>>
>>
> yes. I would to see a stored procedures with this functionality in pg

Is there some reason we wouldn't use RETURN QUERY in that case, instead
of SELECT? As I said above, it would be more consistent with existing
PL/pgSQL.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On 2013-08-23 22:02, Josh Berkus wrote:
> On 08/23/2013 11:30 AM, Pavel Stehule wrote:
>> 2013/8/23 Josh Berkus <josh(at)agliodbs(dot)com>
>>
>>> Pavel,
>>>
>>>> But it can have a different reason. In T-SQL (Microsoft or Sybase) or
>>> MySQL
>>>> a unbound query is used to direct transfer data to client side.
>>>
>>> Are you planning to implement that in PL/pgSQL?
>>>
>>>
>> yes. I would to see a stored procedures with this functionality in pg
>
> Is there some reason we wouldn't use RETURN QUERY in that case, instead
> of SELECT? As I said above, it would be more consistent with existing
> PL/pgSQL.

How would using the same syntax to do an entirely different thing be
consistent?

Regards,
Marko Tiikkaja

On 08/23/2013 01:06 PM, Marko Tiikkaja wrote:
>> Is there some reason we wouldn't use RETURN QUERY in that case, instead
>> of SELECT? As I said above, it would be more consistent with existing
>> PL/pgSQL.
>
> How would using the same syntax to do an entirely different thing be
> consistent?

Currently the only way to return query results to the caller is to use
some form of RETURN. It is 100% consistent.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

2013/8/23 Josh Berkus <josh(at)agliodbs(dot)com>

> On 08/23/2013 11:30 AM, Pavel Stehule wrote:
> > 2013/8/23 Josh Berkus <josh(at)agliodbs(dot)com>
> >
> >> Pavel,
> >>
> >>> But it can have a different reason. In T-SQL (Microsoft or Sybase) or
> >> MySQL
> >>> a unbound query is used to direct transfer data to client side.
> >>
> >> Are you planning to implement that in PL/pgSQL?
> >>
> >>
> > yes. I would to see a stored procedures with this functionality in pg
>
> Is there some reason we wouldn't use RETURN QUERY in that case, instead
> of SELECT? As I said above, it would be more consistent with existing
> PL/pgSQL.
>

for example - multirecordset support. can be reason why distinguish between
these syntax and these functionality.

Regards

Pavel

>
> --
> Josh Berkus
> PostgreSQL Experts Inc.
> http://pgexperts.com
>

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> On 08/23/2013 01:06 PM, Marko Tiikkaja wrote:
>>> Is there some reason we wouldn't use RETURN QUERY in that case, instead
>>> of SELECT? As I said above, it would be more consistent with existing
>>> PL/pgSQL.

>> How would using the same syntax to do an entirely different thing be
>> consistent?

> Currently the only way to return query results to the caller is to use
> some form of RETURN. It is 100% consistent.

I don't find it consistent at all, because what that means is that the
data is to be returned to the SQL statement that called the function.

What's more, the point of any such extension needs to be to allow
*multiple* resultsets to be returned to the client --- if you only need
one, you can have that functionality today with plain old SELECT FROM
myfunction(). And returning some data but continuing execution is surely
not consistent with RETURN.

Basically it seems that we have two choices for how to represent this
(hypothetical) future functionality:

1. Define SELECT without INTO as meaning return results directly to client;

2. Invent some new syntax to do it.

In a green field I think we'd want to do #2, because #1 seems rather
error-prone and unobvious. The only real attraction of #1, IMO, is that
it's consistent with T-SQL. But that's not a terribly strong argument
given the many existing inconsistencies between T-SQL and plpgsql.

BTW, what about INSERT/UPDATE/DELETE RETURNING? Do we want to let
these execute and throw away the data? The argument that this would
be a feature seems a lot weaker than for SELECT, because after all you
could usually just leave off the RETURNING clause. But I'm sure somebody
will say they want to put a function with side-effects into RETURNING
and then ignore its output.

regards, tom lane

On Fri, Aug 23, 2013 at 5:07 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Josh Berkus <josh(at)agliodbs(dot)com> writes:
>> Currently the only way to return query results to the caller is to use
>> some form of RETURN. It is 100% consistent.
>
> I don't find it consistent at all, because what that means is that the
> data is to be returned to the SQL statement that called the function.
>
> What's more, the point of any such extension needs to be to allow
> *multiple* resultsets to be returned to the client --- if you only need
> one, you can have that functionality today with plain old SELECT FROM
> myfunction(). And returning some data but continuing execution is surely
> not consistent with RETURN.

With set returning functions, RETURN QUERY etc means 'yield this data' --
which is pretty weird -- so your point only holds true for unadorned return
(not RETURN NEXT , RETURN QUERY, etc). So I guess it's hard to claim
RETURN means 'return control' though in a procedural sense. In a perfect
world, maybe a separate keyword could have been made to distinguish those
cases (e.h. YIELD QUERY), so I agree (after some reflection) with the
spirit of your point. It's not good to have principle keywords do markedly
different things.

> Basically it seems that we have two choices for how to represent this
> (hypothetical) future functionality:
>
> 1. Define SELECT without INTO as meaning return results directly to
client;
>
> 2. Invent some new syntax to do it.
>
> In a green field I think we'd want to do #2, because #1 seems rather
> error-prone and unobvious. The only real attraction of #1, IMO, is that
> it's consistent with T-SQL. But that's not a terribly strong argument
> given the many existing inconsistencies between T-SQL and plpgsql.

Very good points. I think the only compelling case for #1 that could be
made would be to improve compatibility with pl/sql -- from what I can see
Oracle has not defined the behavior (that is, in pl/sql select must have
INTO) but maybe someone could comment on that.

> BTW, what about INSERT/UPDATE/DELETE RETURNING? Do we want to let
> these execute and throw away the data? The argument that this would
> be a feature seems a lot weaker than for SELECT, because after all you
> could usually just leave off the RETURNING clause. But I'm sure somebody
> will say they want to put a function with side-effects into RETURNING
> and then ignore its output.

If we agree to relax PERFORM, those should be relaxed on the same basis.
In fact, this is conclusive evidence that PERFORM is obsolete: it hails
from the days where SELECT was the only data returning DML.

merlin

2013/8/24 Merlin Moncure <mmoncure(at)gmail(dot)com>

> On Fri, Aug 23, 2013 at 5:07 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Josh Berkus <josh(at)agliodbs(dot)com> writes:
> >> Currently the only way to return query results to the caller is to use
> >> some form of RETURN. It is 100% consistent.
> >
> > I don't find it consistent at all, because what that means is that the
> > data is to be returned to the SQL statement that called the function.
> >
> > What's more, the point of any such extension needs to be to allow
> > *multiple* resultsets to be returned to the client --- if you only need
> > one, you can have that functionality today with plain old SELECT FROM
> > myfunction(). And returning some data but continuing execution is surely
> > not consistent with RETURN.
>
> With set returning functions, RETURN QUERY etc means 'yield this data' --
> which is pretty weird -- so your point only holds true for unadorned return
> (not RETURN NEXT , RETURN QUERY, etc). So I guess it's hard to claim
> RETURN means 'return control' though in a procedural sense. In a perfect
> world, maybe a separate keyword could have been made to distinguish those
> cases (e.h. YIELD QUERY), so I agree (after some reflection) with the
> spirit of your point. It's not good to have principle keywords do markedly
> different things.
>
>
> > Basically it seems that we have two choices for how to represent this
> > (hypothetical) future functionality:
> >
> > 1. Define SELECT without INTO as meaning return results directly to
> client;
> >
> > 2. Invent some new syntax to do it.
> >
> > In a green field I think we'd want to do #2, because #1 seems rather
> > error-prone and unobvious. The only real attraction of #1, IMO, is that
> > it's consistent with T-SQL. But that's not a terribly strong argument
> > given the many existing inconsistencies between T-SQL and plpgsql.
>
> Very good points. I think the only compelling case for #1 that could be
> made would be to improve compatibility with pl/sql -- from what I can see
> Oracle has not defined the behavior (that is, in pl/sql select must have
> INTO) but maybe someone could comment on that.
>
>
Oracle has a special function for returning sets from procedures - see a
new functionality "Implicit Result Sets"
http://tkyte.blogspot.cz/2013/07/12c-implicit-result-sets.html

Although I am thinking so this feature is in T-SQL much more user friendly.

Regards

Pavel

>
> > BTW, what about INSERT/UPDATE/DELETE RETURNING? Do we want to let
> > these execute and throw away the data? The argument that this would
> > be a feature seems a lot weaker than for SELECT, because after all you
> > could usually just leave off the RETURNING clause. But I'm sure somebody
> > will say they want to put a function with side-effects into RETURNING
> > and then ignore its output.
>
> If we agree to relax PERFORM, those should be relaxed on the same basis.
> In fact, this is conclusive evidence that PERFORM is obsolete: it hails
> from the days where SELECT was the only data returning DML.
>
> merlin
>

Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> Oracle has a special function for returning sets from procedures - see a
> new functionality "Implicit Result Sets"
> http://tkyte.blogspot.cz/2013/07/12c-implicit-result-sets.html

That article is worth reading, because Tom K. points out exactly why
T-SQL's approach is a bad idea compared to returning refcursors.
It's not clear to me that we should be in a hurry to go there, much less
try to be 100% syntax compatible with it.

regards, tom lane

2013/8/24 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>

> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> > Oracle has a special function for returning sets from procedures - see a
> > new functionality "Implicit Result Sets"
> > http://tkyte.blogspot.cz/2013/07/12c-implicit-result-sets.html
>
> That article is worth reading, because Tom K. points out exactly why
> T-SQL's approach is a bad idea compared to returning refcursors.
> It's not clear to me that we should be in a hurry to go there, much less
> try to be 100% syntax compatible with it.
>

I disagree - Tom K. speaking about what he likes or dislikes (and about
what he didn't use) He forgot about strong points of implicit result or
interesting points. Clients usually has no problem with dynamic datasets -
PHP, DBI, Llibpq, GUI components .. all libs support a generic access and
this generic access is often used due less dependency on queries.

There are a three interesting possibilities of implicit result sets:

* Possibility to return dynamic dataset - when you don't know a result
before execution - typical use case is a some form of pivot tables or some
analytics queries.

* Possibility to return multiple results as flattening of some
multidimensional data.

* Possibilty to write multiresults reports for one call execution.

This functionality can be emulated by refcursors sets, but it is
significantly less user friendly - so it is not widely used on Oracle's
world.

regards

Pavel

>
> regards, tom lane
>

On Aug 27, 2013, at 12:30 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:

> I disagree - Tom K. speaking about what he likes or dislikes (and about what he didn't use) He forgot about strong points of implicit result or interesting points. Clients usually has no problem with dynamic datasets - PHP, DBI, Llibpq, GUI components .. all libs support a generic access and this generic access is often used due less dependency on queries.
>
> There are a three interesting possibilities of implicit result sets:
>
> * Possibility to return dynamic dataset - when you don't know a result before execution - typical use case is a some form of pivot tables or some analytics queries.
>
> * Possibility to return multiple results as flattening of some multidimensional data.
>
> * Possibilty to write multiresults reports for one call execution.

As a dynamic language programmer, I can see this, as long as it’s not to the exclusion of strong typing interfaces, as well.

However, I do not think it should be implicit. If a function or procedure wants to return values or query results or whatever to the caller, it should explicitly do so by using some key word. We already have RETURN, RETURN NEXT, RETURN QUERY, and RETURN EXECUTE, which is great for functions. For hypothetical functions or procedures that want to return data as it processes, rather than buffering the results and returning them all at once, perhaps we could add YIELD, YEILD QUERY, and YIELD EXECUTE. In fact, this is pretty much exactly what the key word YIELD is for in coroutines:

https://en.wikipedia.org/wiki/Coroutine

But whatever the keyword, I think it makes sense to require one to return results to the caller. Any query that does not return, yield, or capture (select into) values should just have its results discarded.

My $0.02.

Best,

DAvid

On 08/27/2013 08:32 PM, David E. Wheeler wrote:
> On Aug 27, 2013, at 12:30 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>
>> I disagree - Tom K. speaking about what he likes or dislikes (and about what he didn't use) He forgot about strong points of implicit result or interesting points. Clients usually has no problem with dynamic datasets - PHP, DBI, Llibpq, GUI components .. all libs support a generic access and this generic access is often used due less dependency on queries.
>>
>> There are a three interesting possibilities of implicit result sets:
>>
>> * Possibility to return dynamic dataset - when you don't know a result before execution - typical use case is a some form of pivot tables or some analytics queries.
>>
>> * Possibility to return multiple results as flattening of some multidimensional data.
>>
>> * Possibilty to write multiresults reports for one call execution.
> As a dynamic language programmer, I can see this, as long as it’s not to the exclusion of strong typing interfaces, as well.
>
> However, I do not think it should be implicit. If a function or procedure wants to return values or query results or whatever to the caller, it should explicitly do so by using some key word. We already have RETURN, RETURN NEXT, RETURN QUERY, and RETURN EXECUTE, which is great for functions. For hypothetical functions or procedures that want to return data as it processes, rather than buffering the results and returning them all at once, perhaps we could add YIELD, YEILD QUERY, and YIELD EXECUTE.

Conceptually RETURN NEXT is exactly the same as YIELD.

If you look at the SRFs at the C level, then what you do is essentially
a YIELD.
It is only postgreslql backen SRW wrapper which then collects all these
YIELDed values/rows and returns them as on set.

In other words, our SRFs do not currently do any result streaming,
though there is nothing in theory that would prevent them from doing so.

Also, very similar FDWs do streaming.

-------------------
Hannu

--
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ

2013/8/27 David E. Wheeler <david(at)justatheory(dot)com>

> On Aug 27, 2013, at 12:30 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
>
> > I disagree - Tom K. speaking about what he likes or dislikes (and about
> what he didn't use) He forgot about strong points of implicit result or
> interesting points. Clients usually has no problem with dynamic datasets -
> PHP, DBI, Llibpq, GUI components .. all libs support a generic access and
> this generic access is often used due less dependency on queries.
> >
> > There are a three interesting possibilities of implicit result sets:
> >
> > * Possibility to return dynamic dataset - when you don't know a result
> before execution - typical use case is a some form of pivot tables or some
> analytics queries.
> >
> > * Possibility to return multiple results as flattening of some
> multidimensional data.
> >
> > * Possibilty to write multiresults reports for one call execution.
>
> As a dynamic language programmer, I can see this, as long as it’s not to
> the exclusion of strong typing interfaces, as well.
>
> However, I do not think it should be implicit. If a function or procedure
> wants to return values or query results or whatever to the caller, it
> should explicitly do so by using some key word. We already have RETURN,
> RETURN NEXT, RETURN QUERY, and RETURN EXECUTE, which is great for
> functions. For hypothetical functions or procedures that want to return
> data as it processes, rather than buffering the results and returning them
> all at once, perhaps we could add YIELD, YEILD QUERY, and YIELD EXECUTE. In
> fact, this is pretty much exactly what the key word YIELD is for in
> coroutines:
>
> https://en.wikipedia.org/wiki/Coroutine
>
> But whatever the keyword, I think it makes sense to require one to return
> results to the caller. Any query that does not return, yield, or capture
> (select into) values should just have its results discarded.
>

A usual and first solution and syntax is defined by Sybase - we can define
own syntax, but I don't think so it is necessary be original everywhere.

My opinion is surely subjective - this feature is one from few features
that are nice on T-SQL.

Regards

Pavel

>
> My $0.02.
>
> Best,
>
> DAvid

Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> 2013/8/27 David E. Wheeler <david(at)justatheory(dot)com>
>> But whatever the keyword, I think it makes sense to require one to return
>> results to the caller. Any query that does not return, yield, or capture
>> (select into) values should just have its results discarded.

> A usual and first solution and syntax is defined by Sybase - we can define
> own syntax, but I don't think so it is necessary be original everywhere.
> My opinion is surely subjective - this feature is one from few features
> that are nice on T-SQL.

We aren't following T-SQL on any other syntax detail, so why would we
start with this one? plpgsql is meant to follow Oracle syntax not T-SQL.

I agree with David that we should use some new syntax to specify
return-results-directly-to-client, assuming we ever get any such
functionality. It seems like a pretty bad choice of default behavior,
which is essentially what you're saying it should be.

regards, tom lane

2013/8/27 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>

> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> > 2013/8/27 David E. Wheeler <david(at)justatheory(dot)com>
> >> But whatever the keyword, I think it makes sense to require one to
> return
> >> results to the caller. Any query that does not return, yield, or capture
> >> (select into) values should just have its results discarded.
>
> > A usual and first solution and syntax is defined by Sybase - we can
> define
> > own syntax, but I don't think so it is necessary be original everywhere.
> > My opinion is surely subjective - this feature is one from few features
> > that are nice on T-SQL.
>
> We aren't following T-SQL on any other syntax detail, so why would we
> start with this one? plpgsql is meant to follow Oracle syntax not T-SQL.
>
> I agree with David that we should use some new syntax to specify
> return-results-directly-to-client, assuming we ever get any such
> functionality. It seems like a pretty bad choice of default behavior,
> which is essentially what you're saying it should be.
>

this functionality should be disabled in functions. This can be allowed
only for procedures started by CALL statements. I don't propose it for
functions.

Regards

Pavel

>
> regards, tom lane
>

On Aug 27, 2013, at 1:36 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:

> I agree with David that we should use some new syntax to specify
> return-results-directly-to-client, assuming we ever get any such
> functionality. It seems like a pretty bad choice of default behavior,
> which is essentially what you're saying it should be.
>
> this functionality should be disabled in functions. This can be allowed only for procedures started by CALL statements. I don't propose it for functions.

That does not make it a bad idea. Let me summarize:

I propose to remove the requirement to use PERFORM to execute queries for which the result should be discarded. It should instead be implicit that results are discarded unless you capture them or return them.

You propose to continue requiring PERFORM to execute queries for which the result should be discarded. This is so that, in the future, SQL statements can implicitly return to the caller.

That sound about right to you?

I *really* dislike the idea that some SQL execution implicitly returns from a PL/pgSQL function or procedure. That just seems too magical. I strongly prefer that the scope of the code executed in a function or procedure be limited to the scope of the function or procedure itself, and only return data to the caller if I explicitly tell it to. Much less magical, IMHO.

Best,

David

2013/8/27 David E. Wheeler <david(at)justatheory(dot)com>

> On Aug 27, 2013, at 1:36 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
>
> > I agree with David that we should use some new syntax to specify
> > return-results-directly-to-client, assuming we ever get any such
> > functionality. It seems like a pretty bad choice of default behavior,
> > which is essentially what you're saying it should be.
> >
> > this functionality should be disabled in functions. This can be allowed
> only for procedures started by CALL statements. I don't propose it for
> functions.
>
> That does not make it a bad idea. Let me summarize:
>
> I propose to remove the requirement to use PERFORM to execute queries for
> which the result should be discarded. It should instead be implicit that
> results are discarded unless you capture them or return them.
>
> You propose to continue requiring PERFORM to execute queries for which the
> result should be discarded. This is so that, in the future, SQL statements
> can implicitly return to the caller.
>
> That sound about right to you?
>
> I *really* dislike the idea that some SQL execution implicitly returns
> from a PL/pgSQL function or procedure. That just seems too magical. I
> strongly prefer that the scope of the code executed in a function or
> procedure be limited to the scope of the function or procedure itself, and
> only return data to the caller if I explicitly tell it to. Much less
> magical, IMHO.
>

what is magical?

Stored procedures - we talk about this technology was a originally simple
script moved from client side to server side.

so if I write on client side

BEGIN;
SELECT 1,2;
SELECT 2;
SELECT 3,4;
END;

then I expect results

1,2
2
3,4

Procedure is some batch moved and wrapped on server side

CREATE PROCEDURE foo()
BEGIN
SELECT 1,2;
SELECT 2;
SELECT 3,4
END;

And is not strange expect a result

CALL foo()

1,2
2
3,4

Procedure is a script (batch) moved to server side for better performance
and better reuse.

You should not thinking about procedures like void functions, because it is
a little bit different creature - and void functions is significantly
limited in functionality.

My proposal is consistent - no result goes to /dev/null without special
mark. It is disabled (in function) or it goes to client (in procedures).

Regards

Pavel

>
> Best,
>
> David
>
>

On Aug 27, 2013, at 3:10 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:

> CREATE PROCEDURE foo()
> BEGIN
> SELECT 1,2;
> SELECT 2;
> SELECT 3,4
> END;
>
> And is not strange expect a result
>
> CALL foo()
>
> 1,2
> 2
> 3,4
>
> Procedure is a script (batch) moved to server side for better performance and better reuse.

I am not familiar with procedures, being a long time Postgres guy, but you’re right that it never occurred to me that they be thought of as batch files.

Still, this is PL/pgSQL we’re talking about, not TSQL or SQL/PSM anything else. Perhaps your syntax suggestions make sense there, in which case, when you develop such functionality to Postgres, you would need to figure out how to get PERFORM to work with CTEs. But PL/pgSQL requires an explicit key word to return data, and I am hard pressed to see why that would change when it is used in procedures. And that makes PERFORM unnecessary, IME.

> You should not thinking about procedures like void functions, because it is a little bit different creature - and void functions is significantly limited in functionality.
>
> My proposal is consistent - no result goes to /dev/null without special mark. It is disabled (in function) or it goes to client (in procedures).

Consistent, yes. But I’m not convinced -- and I’m *certainly* not convinced that PERFORM should be required to discard query results in PL/pgSQL *functions*, which is the issue on the table now.

Best,

David

On 08/28/2013 12:10 AM, Pavel Stehule wrote:
>
>
> so if I write on client side
>
> BEGIN;
> SELECT 1,2;
> SELECT 2;
> SELECT 3,4;
> END;
>
> then I expect results
>
> 1,2
> 2
> 3,4
And you are perfectly ok to discard the results
Actually it would be much more helpful to have
"discard the results" syntax from client side, as
in this case they take up network resources.
>
> Procedure is some batch moved and wrapped on server side
>
> CREATE PROCEDURE foo()
> BEGIN
> SELECT 1,2;
> SELECT 2;
> SELECT 3,4
> END;
>
> And is not strange expect a result
>
> CALL foo()
>
> 1,2
> 2
> 3,4
>
> Procedure is a script (batch) moved to server side for better
> performance and better reuse.
And you are perfectly ok to discard the results here as well

In a function I do expect the result from select but I also
expect that I can silently ignore the result.

> My proposal is consistent - no result goes to /dev/null without
> special mark. It is disabled (in function) or it goes to client (in
> procedures).
So you can ignore the result in a procedure (by just skipping / not
assigning it on client) but not in a function ?

Can you point out some other languages which *require* you
to store the result of a function call or have a special syntax/keyword
when you do not want to store it ?

Cheer

--
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ

On Tue, Aug 27, 2013 at 6:10 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> what is magical?
>
> Stored procedures - we talk about this technology was a originally simple
> script moved from client side to server side.
>
> so if I write on client side
>
> BEGIN;
> SELECT 1,2;
> SELECT 2;
> SELECT 3,4;
> END;
>
> then I expect results
>
> 1,2
> 2
> 3,4

The biggest problem with this idea is that people will do it by
accident with unacceptable frequency. During the decade or so I
worked as a web programmer, I made this mistake a number of times, and
judging by the comments on this thread, Josh Berkus has made it with
some regularity as well. If experienced PostgreSQL hackers who know
the system inside and out make such mistakes with some regularity, I
think we can anticipate that novices will make them even more often.

And, TBH, as others have said here, I find the requirement to use
PERFORM rather than SELECT rather ridiculous. The clash with CTEs has
been there since we added CTEs, and I've hit it more than once. Yeah,
you can work around it, but it's annoying. And why annoy people? So
+1 from me for de-requiring the use of PERFORM (though I think we
should definitely continue to accept that syntax, for backward
compatibility).

At the end of the day, procedural languages in PostgreSQL are
pluggable. So if we someday have the ability to return extra result
sets on the fly, and if Pavel doesn't like the syntax we choose to use
in PL/pgsql, he can (and, given previous history, very possibly will!)
publish his own PL with different syntax. But I'm with the crowd that
says that's not the right decision for PL/pgsql.

Also, even if we did adopt Pavel's proposed meaning for "SELECT 1,2",
we still have a problem to solve, which is what the user should write
when they want to run a query and ignore the results. The PERFORM
solution was adequate at a time when all select queries started with
SELECT, but now they can start with WITH or VALUES or TABLE as well,
and while VALUES and TABLE may be ignorable, WITH certainly isn't.
Requiring people to use silly workarounds like selecting into an
otherwise-pointless dummy variable is not cool. If we reserve the
undecorated-SELECT syntax to mean something else, then we've got to
come up with some other way of solving David's original problem, and I
don't think there are going to be many elegant options.

Finally, I'd like to note that it's been longstanding frustration of
mine that the PERFORM->SELECT transformation is leaky. For example,
consider:

rhaas=# do $$begin perform amazingly_well(); end;$$;
ERROR: function amazingly_well() does not exist
LINE 1: SELECT amazingly_well()
^
HINT: No function matches the given name and argument types. You
might need to add explicit type casts.
QUERY: SELECT amazingly_well()
CONTEXT: PL/pgSQL function inline_code_block line 1 at PERFORM

Hmm, the user might say. I didn't type the word SELECT anywhere, yet
it shows up in the error message. How confusing! With a big enough
hammer we could perhaps paper over this problem a bit more thoroughly,
but since I've never liked the syntax to begin with, I advance this as
another argument for killing it.

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

On Wed, Aug 28, 2013 at 2:59 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Tue, Aug 27, 2013 at 6:10 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>> what is magical?
>>
>> Stored procedures - we talk about this technology was a originally simple
>> script moved from client side to server side.
>>
>> so if I write on client side
>>
>> BEGIN;
>> SELECT 1,2;
>> SELECT 2;
>> SELECT 3,4;
>> END;
>>
>> then I expect results
>>
>> 1,2
>> 2
>> 3,4
>
> The biggest problem with this idea is that people will do it by
> accident with unacceptable frequency. During the decade or so I
> worked as a web programmer, I made this mistake a number of times, and
> judging by the comments on this thread, Josh Berkus has made it with
> some regularity as well. If experienced PostgreSQL hackers who know
> the system inside and out make such mistakes with some regularity, I
> think we can anticipate that novices will make them even more often.
>
> And, TBH, as others have said here, I find the requirement to use
> PERFORM rather than SELECT rather ridiculous. The clash with CTEs has
> been there since we added CTEs, and I've hit it more than once. Yeah,
> you can work around it, but it's annoying. And why annoy people? So
> +1 from me for de-requiring the use of PERFORM (though I think we
> should definitely continue to accept that syntax, for backward
> compatibility).
>
> At the end of the day, procedural languages in PostgreSQL are
> pluggable. So if we someday have the ability to return extra result
> sets on the fly, and if Pavel doesn't like the syntax we choose to use
> in PL/pgsql, he can (and, given previous history, very possibly will!)
> publish his own PL with different syntax. But I'm with the crowd that
> says that's not the right decision for PL/pgsql.
>
> Also, even if we did adopt Pavel's proposed meaning for "SELECT 1,2",
> we still have a problem to solve, which is what the user should write
> when they want to run a query and ignore the results. The PERFORM
> solution was adequate at a time when all select queries started with
> SELECT, but now they can start with WITH or VALUES or TABLE as well,
> and while VALUES and TABLE may be ignorable, WITH certainly isn't.
> Requiring people to use silly workarounds like selecting into an
> otherwise-pointless dummy variable is not cool. If we reserve the
> undecorated-SELECT syntax to mean something else, then we've got to
> come up with some other way of solving David's original problem, and I
> don't think there are going to be many elegant options.
>
> Finally, I'd like to note that it's been longstanding frustration of
> mine that the PERFORM->SELECT transformation is leaky. For example,
> consider:
>
> rhaas=# do $$begin perform amazingly_well(); end;$$;
> ERROR: function amazingly_well() does not exist
> LINE 1: SELECT amazingly_well()
> ^
> HINT: No function matches the given name and argument types. You
> might need to add explicit type casts.
> QUERY: SELECT amazingly_well()
> CONTEXT: PL/pgSQL function inline_code_block line 1 at PERFORM
>
> Hmm, the user might say. I didn't type the word SELECT anywhere, yet
> it shows up in the error message. How confusing! With a big enough
> hammer we could perhaps paper over this problem a bit more thoroughly,
> but since I've never liked the syntax to begin with, I advance this as
> another argument for killing it.

Right. Another pain point for me is that I frequently have to
'up-convert' functions from sql to pgsql (and sometimes the other way
too). The perform requirement turns that into a headache. It looks
like we are mostly ok on Oracle compatibility too.

I'm a fan of David's 'YIELD' syntax concept as a line of analysis for
'mid procedure set returning' when we get there.

merlin

On 08/28/2013 09:59 PM, Robert Haas wrote:
> On Tue, Aug 27, 2013 at 6:10 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>> what is magical?
>>
>> Stored procedures - we talk about this technology was a originally simple
>> script moved from client side to server side.
>>
>> so if I write on client side
>>
>> BEGIN;
>> SELECT 1,2;
>> SELECT 2;
>> SELECT 3,4;
>> END;
>>
>> then I expect results
>>
>> 1,2
>> 2
>> 3,4
> The biggest problem with this idea is that people will do it by
> accident with unacceptable frequency. During the decade or so I
> worked as a web programmer, I made this mistake a number of times, and
> judging by the comments on this thread, Josh Berkus has made it with
> some regularity as well. If experienced PostgreSQL hackers who know
> the system inside and out make such mistakes with some regularity, I
> think we can anticipate that novices will make them even more often.
Usually yo test your queries fom psql prompt and then copy/paste
into your function.
As ignoring the results need no conscious effort at psql prompt, it
will always be a mild surprise that you have to jump through hoops
to do it in pl/pgsql.
And I can easily do this for example in pl/python - just do not assign
the result from plpy.execute() and they get ignored with no extra
effort whatsoever.

> ...
> Finally, I'd like to note that it's been longstanding frustration of
> mine that the PERFORM->SELECT transformation is leaky. For example,
> consider:
>
> rhaas=# do $$begin perform amazingly_well(); end;$$;
> ERROR: function amazingly_well() does not exist
> LINE 1: SELECT amazingly_well()
> ^
> HINT: No function matches the given name and argument types. You
> might need to add explicit type casts.
> QUERY: SELECT amazingly_well()
> CONTEXT: PL/pgSQL function inline_code_block line 1 at PERFORM
>
> Hmm, the user might say. I didn't type the word SELECT anywhere, yet
> it shows up in the error message. How confusing! With a big enough
> hammer we could perhaps paper over this problem a bit more thoroughly,
> but since I've never liked the syntax to begin with, I advance this as
> another argument for killing it.
>
Totally agree.

Cheers

--
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ

2013/8/28 Hannu Krosing <hannu(at)2ndquadrant(dot)com>

> On 08/28/2013 12:10 AM, Pavel Stehule wrote:
> >
> >
> > so if I write on client side
> >
> > BEGIN;
> > SELECT 1,2;
> > SELECT 2;
> > SELECT 3,4;
> > END;
> >
> > then I expect results
> >
> > 1,2
> > 2
> > 3,4
> And you are perfectly ok to discard the results
> Actually it would be much more helpful to have
> "discard the results" syntax from client side, as
> in this case they take up network resources.
> >
> > Procedure is some batch moved and wrapped on server side
> >
> > CREATE PROCEDURE foo()
> > BEGIN
> > SELECT 1,2;
> > SELECT 2;
> > SELECT 3,4
> > END;
> >
> > And is not strange expect a result
> >
> > CALL foo()
> >
> > 1,2
> > 2
> > 3,4
> >
> > Procedure is a script (batch) moved to server side for better
> > performance and better reuse.
> And you are perfectly ok to discard the results here as well
>

sure, depends how would to take a definition of procedure. Procedure is a
classic procedure in PL/SQL - based on ADA procedures , or more like batch
in T-SQL based on Sybase research, or some between in PSM in DB2. Every
design has some advantage and disadvantage. But hardly to say what is a
perfect design. I like a PL/SQL, but a procedures design (transaction
control) is more obscure, than in T-SQL. DB2 procedures can use parameter
list and can returns a status - it is a third design.

I don't propose procedures like syntactic sugar for current PostgreSQL
behave. If we can support procedures one times, then we should to get a new
functionality, that is not possible (or not simple possible) now.

>
> In a function I do expect the result from select but I also
> expect that I can silently ignore the result.
>

> > My proposal is consistent - no result goes to /dev/null without
> > special mark. It is disabled (in function) or it goes to client (in
> > procedures).
> So you can ignore the result in a procedure (by just skipping / not
> assigning it on client) but not in a function ?
>

SQL function that is called from SELECT statement should to return only one
result - without any side effect. It is a very good example, how clean and
simple is using PostgreSQL functions that returns scalar or table, and how
less clean and user friendly is usage functions that returns refcursors. I
like a PostgreSQL design, that use a explicit or implicit transaction for
every SELECT statement - and every function evaluation. It is simple, it is
clean, and it is significant limit for some usage, where we can work more
complexly with transactions. We must to break some code to more
cliend-server calls. T-SQL is strict in this area, and disallow any side
effect.

>
> Can you point out some other languages which *require* you
> to store the result of a function call or have a special syntax/keyword
> when you do not want to store it ?
>

ADA is very strict about it.

Regards

Pavel

>
> Cheer
>
>
>
>
>
>
>
>
>
> --
> Hannu Krosing
> PostgreSQL Consultant
> Performance, Scalability and High Availability
> 2ndQuadrant Nordic OÜ
>
>

2013/8/28 Robert Haas <robertmhaas(at)gmail(dot)com>

> On Tue, Aug 27, 2013 at 6:10 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
> > what is magical?
> >
> > Stored procedures - we talk about this technology was a originally simple
> > script moved from client side to server side.
> >
> > so if I write on client side
> >
> > BEGIN;
> > SELECT 1,2;
> > SELECT 2;
> > SELECT 3,4;
> > END;
> >
> > then I expect results
> >
> > 1,2
> > 2
> > 3,4
>
> The biggest problem with this idea is that people will do it by
> accident with unacceptable frequency. During the decade or so I
> worked as a web programmer, I made this mistake a number of times, and
> judging by the comments on this thread, Josh Berkus has made it with
> some regularity as well. If experienced PostgreSQL hackers who know
> the system inside and out make such mistakes with some regularity, I
> think we can anticipate that novices will make them even more often.
>
> And, TBH, as others have said here, I find the requirement to use
> PERFORM rather than SELECT rather ridiculous. The clash with CTEs has
> been there since we added CTEs, and I've hit it more than once. Yeah,
> you can work around it, but it's annoying. And why annoy people? So
> +1 from me for de-requiring the use of PERFORM (though I think we
> should definitely continue to accept that syntax, for backward
> compatibility).
>
> At the end of the day, procedural languages in PostgreSQL are
> pluggable. So if we someday have the ability to return extra result
> sets on the fly, and if Pavel doesn't like the syntax we choose to use
> in PL/pgsql, he can (and, given previous history, very possibly will!)
> publish his own PL with different syntax. But I'm with the crowd that
> says that's not the right decision for PL/pgsql.
>

I cannot to say what is good design for PL/pgSQL - only I feel so some
variant of RETURN statement is not good, because semantic is significantly
different. And I see a increasing inconsistency between a original ADA and
PL/pgSQL.

Sure, When I am thinking about PSM, I am thinking about T-SQL syntax, but
there is little bit simpler situation - there is a precedent in PSM
implementation in MySQL and some other new databases.

>
> Also, even if we did adopt Pavel's proposed meaning for "SELECT 1,2",
> we still have a problem to solve, which is what the user should write
> when they want to run a query and ignore the results. The PERFORM
> solution was adequate at a time when all select queries started with
> SELECT, but now they can start with WITH or VALUES or TABLE as well,
> and while VALUES and TABLE may be ignorable, WITH certainly isn't.
> Requiring people to use silly workarounds like selecting into an
> otherwise-pointless dummy variable is not cool. If we reserve the
> undecorated-SELECT syntax to mean something else, then we've got to
> come up with some other way of solving David's original problem, and I
> don't think there are going to be many elegant options.
>
> Finally, I'd like to note that it's been longstanding frustration of
> mine that the PERFORM->SELECT transformation is leaky. For example,
> consider:
>
> rhaas=# do $$begin perform amazingly_well(); end;$$;
> ERROR: function amazingly_well() does not exist
> LINE 1: SELECT amazingly_well()
>

I am thinking, so we are near a merit of problem - if I understand well, a
PERFORM was originally designed instead a CALL statement. Due
implementation it was used for some other SQL calls too.

Origin PL/SQL doesn't allow SELECT without INTO.

your example is good and important, because almost all described issues are
related to unsuccessfully solved or a missing procedures.

so main problem is a impossibility to write

BEGIN
CALL fce()

or

BEGIN
fce();

A workaround in Postgres is PERFORM - and I really has nothing again to
remove PERFORM for start of VOID functions!

A unhelpful error message has zero relevant to topic - just almost all in
PL/pgSQL is SELECT.

Do you would to remove a ":=" statement too?

postgres=# do $$declare x int; begin x := notexisting(10); end; $$ ;
ERROR: function notexisting(integer) does not exist
LINE 1: SELECT notexisting(10)
^
HINT: No function matches the given name and argument types. You might
need to add explicit type casts.
QUERY: SELECT notexisting(10)
CONTEXT: PL/pgSQL function inline_code_block line 1 at assignment
Time: 148.760 ms

Regards

Pavel

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

On Aug 29, 2013, at 1:11 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:

> I cannot to say what is good design for PL/pgSQL - only I feel so some variant of RETURN statement is not good, because semantic is significantly different. And I see a increasing inconsistency between a original ADA and PL/pgSQL.

So YIELD or implement PL/PSM.

> Sure, When I am thinking about PSM, I am thinking about T-SQL syntax, but there is little bit simpler situation - there is a precedent in PSM implementation in MySQL and some other new databases.

PL/pgSQL is not PSM.

> so main problem is a impossibility to write
>
> BEGIN
> CALL fce()
>
> or
>
> BEGIN
> fce();
>
> A workaround in Postgres is PERFORM - and I really has nothing again to remove PERFORM for start of VOID functions!

No reason SELECT could not work just a well.

> A unhelpful error message has zero relevant to topic - just almost all in PL/pgSQL is SELECT.

Well, it was an aside, but points out another problem with PERFORM: It doesn't really exist. I gets replaced with SELECT internally, leading to confusing error messages. Solution: Allow SELECT instead of PERFORM.

> Do you would to remove a ":=" statement too?
>
> postgres=# do $$declare x int; begin x := notexisting(10); end; $$ ;
> ERROR: function notexisting(integer) does not exist
> LINE 1: SELECT notexisting(10)
> ^
> HINT: No function matches the given name and argument types. You might need to add explicit type casts.
> QUERY: SELECT notexisting(10)
> CONTEXT: PL/pgSQL function inline_code_block line 1 at assignment

I agree it would be nice if it didn't report SELECT there, but at least it's not *removing* anything from what you see in the source.

Best,

David

2013/8/29 David E. Wheeler <david(at)justatheory(dot)com>

> On Aug 29, 2013, at 1:11 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
>
> > I cannot to say what is good design for PL/pgSQL - only I feel so some
> variant of RETURN statement is not good, because semantic is significantly
> different. And I see a increasing inconsistency between a original ADA and
> PL/pgSQL.
>
> So YIELD or implement PL/PSM.
>

We can discussed about syntax later - now it is offtopic and it is too
early - still we miss a procedures.

>
> > Sure, When I am thinking about PSM, I am thinking about T-SQL syntax,
> but there is little bit simpler situation - there is a precedent in PSM
> implementation in MySQL and some other new databases.
>
> PL/pgSQL is not PSM.
>

yes, I know it well - although some syntax is shared - CASE statements

>
> > so main problem is a impossibility to write
> >
> > BEGIN
> > CALL fce()
> >
> > or
> >
> > BEGIN
> > fce();
> >
> > A workaround in Postgres is PERFORM - and I really has nothing again to
> remove PERFORM for start of VOID functions!
>
> No reason SELECT could not work just a well.
>

No, originally, there was a target of compatibility with PL/SQL (more or
less in some time), and PL/SQL disallow unbound SELECT.

More - PL/SQL allow a direct procedure call - so some like PERFORM is
useless there.

>
> > A unhelpful error message has zero relevant to topic - just almost all
> in PL/pgSQL is SELECT.
>
> Well, it was an aside, but points out another problem with PERFORM: It
> doesn't really exist. I gets replaced with SELECT internally, leading to
> confusing error messages. Solution: Allow SELECT instead of PERFORM.
>
> > Do you would to remove a ":=" statement too?
> >
> > postgres=# do $$declare x int; begin x := notexisting(10); end; $$ ;
> > ERROR: function notexisting(integer) does not exist
> > LINE 1: SELECT notexisting(10)
> > ^
> > HINT: No function matches the given name and argument types. You might
> need to add explicit type casts.
> > QUERY: SELECT notexisting(10)
> > CONTEXT: PL/pgSQL function inline_code_block line 1 at assignment
>
> I agree it would be nice if it didn't report SELECT there, but at least
> it's not *removing* anything from what you see in the source.
>

It was a little bit a irony. I am think now so all problems about PERFORM
is based on porting PL/SQL environment (that was a classic simplified ADA)
to PostgreSQL without procedures. So PERFORM was a designed for evaluation
of something like procedures - but there was nothing in this time - a VOID
functions are younger. Without PERFORM we didn't do this talk.

Still I don't think so correct solution is enabling a unbound SELECTs, but
correct is a fix a PERFORM and remove a necessity to use a PERFORM for call
of VOID functions.

Regards

Pavel

>
> Best,
>
> David
>
>
>

On Aug 29, 2013, at 2:22 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:

> Still I don't think so correct solution is enabling a unbound SELECTs, but correct is a fix a PERFORM and remove a necessity to use a PERFORM for call of VOID functions.

Well, in this thread, I believe you are the only person who feels that way. And this proposal still would not let PERFORM work with CTEs.

Best,

David

On 2013-08-29 14:31:55 -0700, David E. Wheeler wrote:
> On Aug 29, 2013, at 2:22 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>
> > Still I don't think so correct solution is enabling a unbound SELECTs, but correct is a fix a PERFORM and remove a necessity to use a PERFORM for call of VOID functions.
>
> Well, in this thread, I believe you are the only person who feels that way. And this proposal still would not let PERFORM work with CTEs.

I haven't made up my mind on whether PERFORM is a good idea or not, but
independently from that we certainly could patch plpgsql to allow
PERFORM WITH .... Doesn't look to hard to me from a quick look.

Greetings,

Andres Freund

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

On 08/29/2013 02:22 PM, Pavel Stehule wrote:
> Still I don't think so correct solution is enabling a unbound SELECTs, but
> correct is a fix a PERFORM and remove a necessity to use a PERFORM for call
> of VOID functions.

You have yet to supply any arguments which support this position.

Several people have pointed out that requiring PERFORM needlessly makes
life hard for PL/pgSQL programmers, especially new ones. You have not
given us any benefit it supplies in return.

And no, I don't accept the idea that we might someday have some kind of
conflicting syntax for stored procedures which nobody is working on as a
valid argument.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

2013/8/29 David E. Wheeler <david(at)justatheory(dot)com>

> On Aug 29, 2013, at 2:22 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
>
> > Still I don't think so correct solution is enabling a unbound SELECTs,
> but correct is a fix a PERFORM and remove a necessity to use a PERFORM for
> call of VOID functions.
>
> Well, in this thread, I believe you are the only person who feels that
> way. And this proposal still would not let PERFORM work with CTEs.
>

I am thinking, so I propose a enough solution for you - when you use CTE
for execution of VOID function, then result vill be VOID set, what we can
accept as undefined result, and in this case a PERFORM should not be
required. If CTE will return some result, then PERFORM should be required
and PERFORM must to support CTE in all possible modes - updateable or not
updateable queries.

Regards

Pavel

>
> Best,
>
> David
>
>

On 08/29/2013 11:01 PM, David E. Wheeler wrote:
> On Aug 29, 2013, at 1:11 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>
>> I cannot to say what is good design for PL/pgSQL - only I feel so some variant of RETURN statement is not good, because semantic is significantly different. And I see a increasing inconsistency between a original ADA and PL/pgSQL.
> So YIELD or implement PL/PSM.
We already have RETURN NEXT as equivalent to YIELD.

--
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ

On Aug 29, 2013, at 2:41 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:

> I am thinking, so I propose a enough solution for you - when you use CTE for execution of VOID function, then result vill be VOID set, what we can accept as undefined result, and in this case a PERFORM should not be required. If CTE will return some result, then PERFORM should be required and PERFORM must to support CTE in all possible modes - updateable or not updateable queries.

If you can make PERFORM work with CTEs, that would be an improvement over the status quo. But I think there is no good reason not to let SELECT results be discarded, either. I know you think there are good reasons, but no one else in this thread is convince, AFAICT.

Best,

David

On 2013-08-29 14:40:24 -0700, Josh Berkus wrote:
> On 08/29/2013 02:22 PM, Pavel Stehule wrote:
> > Still I don't think so correct solution is enabling a unbound SELECTs, but
> > correct is a fix a PERFORM and remove a necessity to use a PERFORM for call
> > of VOID functions.
>
> You have yet to supply any arguments which support this position.

I am not convinced that's enough of a reason, but the requirement to use
PERFORM for SELECTs that aren't stored anywhere actually has prevented
bugs for me. I am not convinced that's worth the cost since I also have
been annoyed by it several times, but it's not as crystal clear as you
paint it.

Greetings,

Andres Freund

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

2013/8/29 Josh Berkus <josh(at)agliodbs(dot)com>

> On 08/29/2013 02:22 PM, Pavel Stehule wrote:
> > Still I don't think so correct solution is enabling a unbound SELECTs,
> but
> > correct is a fix a PERFORM and remove a necessity to use a PERFORM for
> call
> > of VOID functions.
>
> You have yet to supply any arguments which support this position.
>
> Several people have pointed out that requiring PERFORM needlessly makes
> life hard for PL/pgSQL programmers, especially new ones. You have not
> given us any benefit it supplies in return.
>
> And no, I don't accept the idea that we might someday have some kind of
> conflicting syntax for stored procedures which nobody is working on as a
> valid argument.
>

The more stronger argument is not allow a useless execution.

PL/pgSQL is a verbose language and it is based on very strict ADA language
- a few a secure mechanism we dropped (and some from good reasons).

So questions is - how much we would to go against a ADA ideas and PL/SQL
rules.

No think so PERFORM is a significant problem. A mayor problem for beginners
is usually a fact, so PL/pgSQL is ALGOL like languages - and they don't
know with these languages. Second problem is missing a more dynamic data
structures. Next a really different syntax and usage of OUT variables, ...

Regards

Pavel

>
> --
> Josh Berkus
> PostgreSQL Experts Inc.
> http://pgexperts.com
>

On Aug 29, 2013, at 2:48 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:

>> You have yet to supply any arguments which support this position.
>
> I am not convinced that's enough of a reason, but the requirement to use
> PERFORM for SELECTs that aren't stored anywhere actually has prevented
> bugs for me. I am not convinced that's worth the cost since I also have
> been annoyed by it several times, but it's not as crystal clear as you
> paint it.

So now we can revise Josh’s assertion to: “I have seen only tepid, unconvincing arguments which support this position.”

I have thought that PERFORM was useful to mark queries that discard results in the past, but I think now that the mental load is higher, even if it can be fixed with CTEs, it’s more trouble than it’s worth.

Best,

David

2013/8/29 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>

>
>
>
> 2013/8/29 Josh Berkus <josh(at)agliodbs(dot)com>
>
>> On 08/29/2013 02:22 PM, Pavel Stehule wrote:
>> > Still I don't think so correct solution is enabling a unbound SELECTs,
>> but
>> > correct is a fix a PERFORM and remove a necessity to use a PERFORM for
>> call
>> > of VOID functions.
>>
>> You have yet to supply any arguments which support this position.
>>
>> Several people have pointed out that requiring PERFORM needlessly makes
>> life hard for PL/pgSQL programmers, especially new ones. You have not
>> given us any benefit it supplies in return.
>>
>> And no, I don't accept the idea that we might someday have some kind of
>> conflicting syntax for stored procedures which nobody is working on as a
>> valid argument.
>>
>
> The more stronger argument is not allow a useless execution.
>
> PL/pgSQL is a verbose language and it is based on very strict ADA language
> - a few a secure mechanism we dropped (and some from good reasons).
>
> So questions is - how much we would to go against a ADA ideas and PL/SQL
> rules.
>
> No think so PERFORM is a significant problem. A mayor problem for
> beginners is usually a fact, so PL/pgSQL is ALGOL like languages - and they
> don't know with these languages. Second problem is missing a more dynamic
> data structures. Next a really different syntax and usage of OUT variables,
> ...
>

look to stackoverflow for often questions - the big issue is impossibility
to iterate over record -- and return really dynamic result - pivot tables.

Regards

Pavel

>
> Regards
>
> Pavel
>
>
>>
>> --
>> Josh Berkus
>> PostgreSQL Experts Inc.
>> http://pgexperts.com
>>
>
>

On 2013-08-29 23:54:28 +0200, Pavel Stehule wrote:
> > No think so PERFORM is a significant problem. A mayor problem for
> > beginners is usually a fact, so PL/pgSQL is ALGOL like languages - and they
> > don't know with these languages. Second problem is missing a more dynamic
> > data structures. Next a really different syntax and usage of OUT variables,
> > ...
> >
>
> look to stackoverflow for often questions - the big issue is impossibility
> to iterate over record -- and return really dynamic result - pivot tables.

So what? That's completely orthogonal to the discussion at hand. We're
discussion about specific change, that there are other features people
badly want shouldn't be stopping this.

Greetings,

Andres Freund

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

2013/8/29 David E. Wheeler <david(at)justatheory(dot)com>

> On Aug 29, 2013, at 2:48 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
>
> >> You have yet to supply any arguments which support this position.
> >
> > I am not convinced that's enough of a reason, but the requirement to use
> > PERFORM for SELECTs that aren't stored anywhere actually has prevented
> > bugs for me. I am not convinced that's worth the cost since I also have
> > been annoyed by it several times, but it's not as crystal clear as you
> > paint it.
>
> So now we can revise Josh’s assertion to: “I have seen only tepid,
> unconvincing arguments which support this position.”
>
> I have thought that PERFORM was useful to mark queries that discard
> results in the past, but I think now that the mental load is higher, even
> if it can be fixed with CTEs, it’s more trouble than it’s worth.
>

when we fix a correct call of VOID function, then half of problem goes out.
Second half is subjective.

I remember, some years ago there was a proposal to change syntax and remove
all verbosity features from PL/pgSQL - for example - using only END instead
END IF, END LOOP, ...

For me, this talk is similar - we have a language, that was designed be
secure and verbose, what means, so developer must to write some chars more.
You cannot to have both - short language and secure.

Regards

Pavel

>
> Best,
>
> David
>
>

On 08/29/2013 05:31 PM, David E. Wheeler wrote:
> On Aug 29, 2013, at 2:22 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>
>> Still I don't think so correct solution is enabling a unbound SELECTs, but correct is a fix a PERFORM and remove a necessity to use a PERFORM for call of VOID functions.
> Well, in this thread, I believe you are the only person who feels that way. And this proposal still would not let PERFORM work with CTEs.
>

Perhaps we could provide for SELECT INTO NULL or some such as a
different spelling of PERFORM to indicate that the result should be
discarded.

cheers

andrew

2013/8/29 Andres Freund <andres(at)2ndquadrant(dot)com>

> On 2013-08-29 23:54:28 +0200, Pavel Stehule wrote:
> > > No think so PERFORM is a significant problem. A mayor problem for
> > > beginners is usually a fact, so PL/pgSQL is ALGOL like languages - and
> they
> > > don't know with these languages. Second problem is missing a more
> dynamic
> > > data structures. Next a really different syntax and usage of OUT
> variables,
> > > ...
> > >
> >
> > look to stackoverflow for often questions - the big issue is
> impossibility
> > to iterate over record -- and return really dynamic result - pivot
> tables.
>
> So what? That's completely orthogonal to the discussion at hand. We're
> discussion about specific change, that there are other features people
> badly want shouldn't be stopping this.
>

I am sorry, this is too offtopic.

Regards

Pavel

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

On 08/30/2013 12:04 AM, Pavel Stehule wrote:
>
>
>
> 2013/8/29 David E. Wheeler <david(at)justatheory(dot)com
> <mailto:david(at)justatheory(dot)com>>
>
> On Aug 29, 2013, at 2:48 PM, Andres Freund <andres(at)2ndquadrant(dot)com
> <mailto:andres(at)2ndquadrant(dot)com>> wrote:
>
> >> You have yet to supply any arguments which support this position.
> >
> > I am not convinced that's enough of a reason, but the
> requirement to use
> > PERFORM for SELECTs that aren't stored anywhere actually has
> prevented
> > bugs for me. I am not convinced that's worth the cost since I
> also have
> > been annoyed by it several times, but it's not as crystal clear
> as you
> > paint it.
>
> So now we can revise Josh’s assertion to: “I have seen only tepid,
> unconvincing arguments which support this position.”
>
> I have thought that PERFORM was useful to mark queries that
> discard results in the past, but I think now that the mental load
> is higher, even if it can be fixed with CTEs, it’s more trouble
> than it’s worth.
>
>
> when we fix a correct call of VOID function, then half of problem goes
> out. Second half is subjective.
>
> I remember, some years ago there was a proposal to change syntax and
> remove all verbosity features from PL/pgSQL - for example - using only
> END instead END IF, END LOOP, ...
This has a bit more value as it allows you to detect some (though not
all) structural errors.

PERFORM just forces you to replace some SELECTs just to confirm
that you really did not want to capture the result.

If the original aim was somehow connected with allowing direct
function calls for PL/SQL compatibility, then why not just implement
direct function calls instead and let us have plain SELECT back ?
>
> For me, this talk is similar - we have a language, that was designed
> be secure and verbose,
If you want a verbose expression for ignoring the result I'd suggest
something like "SELECT ... IGNORING RESULT" or "SELECT ... INTO VOID"
It is self-describing like most of SQL, instead of making you wander
each time if the word you want to replace SELECT with to ignore
the result was PERFORM or EXECUTE :)

But I can *not* see how allowing just SELECT and discarding the result has
any less "security", for any definition of security I can think of.

For me it is just an arbitrary nuisance, with which I can live, but I'd
prefer not to.

Cheers

--
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ