Обсуждение: 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

Hi Pavel,

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

>>     david=# DO $$
>>     david$# BEGIN
>>     david$#     WITH now AS (SELECT now())
>>     david$#     PERFORM * from now;
>>     david$# END;
>>     david$# $$;
>>     ERROR:  syntax error at or near "PERFORM"
>>     LINE 4:     PERFORM * from now;
>>                 ^
>> Parser bug in PL/pgSQL, perhaps?
>
> no
>
> you cannot use a PL/pgSQL statement inside SQL statement.

Well, there ought to be *some* way to tell PL/pgSQL to discard the result. Right now I am adding a variable to select
intobut 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

On 2013-08-20 14:15:55 +0200, David E. Wheeler wrote:
> Hi Pavel,
>
> On Aug 20, 2013, at 2:11 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>
> >>     david=# DO $$
> >>     david$# BEGIN
> >>     david$#     WITH now AS (SELECT now())
> >>     david$#     PERFORM * from now;
> >>     david$# END;
> >>     david$# $$;
> >>     ERROR:  syntax error at or near "PERFORM"
> >>     LINE 4:     PERFORM * from now;
> >>                 ^
> >> Parser bug in PL/pgSQL, perhaps?
> >
> > no
> >
> > you cannot use a PL/pgSQL statement inside SQL statement.
>
> Well, there ought to be *some* way to tell PL/pgSQL to discard the result. Right now I am adding a variable to select
intobut 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 8/20/13 2:21 PM, Pavel Stehule wrote:
> 2013/8/20 David E. Wheeler <david@justatheory.com>
>> Well, there ought to be *some* way to tell PL/pgSQL to discard the result.
>> Right now I am adding a variable to select into but never otherwise use.
>> Inelegant, IMHO. Perhaps I’m missing some other way to do it?
>>
>> If so, it would help if the hint suggesting the use of PERFORM pointed to
>> such alternatives.
>>
>
> postgres=# DO $$
>   BEGIN
>     PERFORM * FROM (WITH now AS (SELECT now())
>    SELECT * from now) x;
>   END;
> $$;
> DO

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


Regards,
Marko Tiikkaja

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

>> postgres=# DO $$
>>  BEGIN
>>    PERFORM * FROM (WITH now AS (SELECT now())
>>   SELECT * from now) x;
>>  END;
>> $$;
>> DO
> 
> .. which doesn't work if you want to use table-modifying CTEs.

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

Best,

David

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

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

But this does not:

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

Best,

David

On Tue, Aug 20, 2013 at 7:25 AM, Andres Freund <andres@2ndquadrant.com> wrote:
> On 2013-08-20 14:15:55 +0200, David E. Wheeler wrote:
>> Hi Pavel,
>>
>> On Aug 20, 2013, at 2:11 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>>
>> >>     david=# DO $$
>> >>     david$# BEGIN
>> >>     david$#     WITH now AS (SELECT now())
>> >>     david$#     PERFORM * from now;
>> >>     david$# END;
>> >>     david$# $$;
>> >>     ERROR:  syntax error at or near "PERFORM"
>> >>     LINE 4:     PERFORM * from now;
>> >>                 ^
>> >> Parser bug in PL/pgSQL, perhaps?
>> >
>> > no
>> >
>> > you cannot use a PL/pgSQL statement inside SQL statement.
>>
>> Well, there ought to be *some* way to tell PL/pgSQL to discard the result. Right now I am adding a variable to
selectinto 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

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

> yes, in this context you should not use a PERFORM
>
> PL/pgSQL protect you before useless queries - so you can use a CTE without returned result directly or CTE with
resultvia 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.stehule@gmail.com> wrote:
>
>> but it works
>>
>> postgres=# do $$begin with x as (select 10) insert into omega select * from x; end;$$;
>> DO
> But this does not:
>
> david=# DO $$
> david$# BEGIN
> david$#     PERFORM * FROM (
> david$#         WITH inserted AS (
> david$#             INSERT INTO foo values (1) RETURNING id
> david$#         ) SELECT inserted.id
> david$#     ) x;
> david$# END;
> david$# $$;
> ERROR:  WITH clause containing a data-modifying statement must be at the top level
> LINE 2:         WITH inserted AS (
>                       ^
> QUERY:  SELECT * FROM (
>          WITH inserted AS (
>              INSERT INTO foo values (1) RETURNING id
>          ) SELECT inserted.id
>      ) x
> CONTEXT:  PL/pgSQL function inline_code_block line 3 at PERFORM

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

Proof:

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


Best regards,
Zoltán Böszörményi

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

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

> I think the way forward is to remove the restriction such that data
> returning queries must be PERFORM'd
> 
> I disagree, current rule has sense.

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

Best,

David

On Tue, Aug 20, 2013 at 7:44 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>
>
>
> 2013/8/20 Merlin Moncure <mmoncure@gmail.com>
>>
>> On Tue, Aug 20, 2013 at 7:25 AM, Andres Freund <andres@2ndquadrant.com>
>> wrote:
>> > On 2013-08-20 14:15:55 +0200, David E. Wheeler wrote:
>> >> Hi Pavel,
>> >>
>> >> On Aug 20, 2013, at 2:11 PM, Pavel Stehule <pavel.stehule@gmail.com>
>> >> wrote:
>> >>
>> >> >>     david=# DO $$
>> >> >>     david$# BEGIN
>> >> >>     david$#     WITH now AS (SELECT now())
>> >> >>     david$#     PERFORM * from now;
>> >> >>     david$# END;
>> >> >>     david$# $$;
>> >> >>     ERROR:  syntax error at or near "PERFORM"
>> >> >>     LINE 4:     PERFORM * from now;
>> >> >>                 ^
>> >> >> Parser bug in PL/pgSQL, perhaps?
>> >> >
>> >> > no
>> >> >
>> >> > you cannot use a PL/pgSQL statement inside SQL statement.
>> >>
>> >> Well, there ought to be *some* way to tell PL/pgSQL to discard the
>> >> result. Right now I am adding a variable to select into but never otherwise
>> >> use. Inelegant, IMHO. Perhaps I’m missing some other way to do it?
>> >>
>> >> If so, it would help if the hint suggesting the use of PERFORM pointed
>> >> to such alternatives.
>> >
>> > Not that that's elegant but IIRC PERFORM (WITH ...) ought to work. I
>> > don't think the intermingled plpgsql/sql grammars allow a nice way right
>> > now.
>>
>> I think the way forward is to remove the restriction such that data
>> returning queries must be PERFORM'd
>
>
> I disagree, current rule has sense.

Curious what your thinking is there.

merlin

On 8/20/13 2:53 PM, Pavel Stehule wrote:
> 2013/8/20 David E. Wheeler <david@justatheory.com>
>> I am passing the values returned from a CTE to a call to pg_notify(). I do
>> not care to collect the output of pg_notify(), which returns VOID.
>>
>
> it is little bit different issue - PL/pgSQL doesn't check if returned type
> is VOID - it can be allowed, I am thinking. So check of empty result can be
> enhanced.

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

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


Regards,
Marko Tiikkaja

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

>> I am passing the values returned from a CTE to a call to pg_notify(). I do not care to collect the output of
pg_notify(),which returns VOID. 
>
> it is little bit different issue - PL/pgSQL doesn't check if returned type is VOID - it can be allowed, I am
thinking.So check of empty result can be enhanced. 

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

Best,

David

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

> When you would to ignore result, then you should to use a PERFORM - actually, it is limited now and should be fixed.
Haveno 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
withoutit. 

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

Best,

David

On Aug 20, 2013, at 3:18 PM, Pavel Stehule <pavel.stehule@gmail.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       );
RETURNFOUND;   END;   $$;
 

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

Best,

David

On Aug 20, 2013, at 3:38 PM, Pavel Stehule <pavel.stehule@gmail.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
functiondoes 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:
sometimesyou 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
morecases. 

Best,

David

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

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

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

On Fri, Aug 23, 2013 at 1:38 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>
>
>
> 2013/8/23 Merlin Moncure <mmoncure@gmail.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

On Aug 23, 2013, at 8:51 PM, Pavel Stehule <pavel.stehule@gmail.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
whenit 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
thanyour objections, of course. 

Best,

David

On 08/23/2013 11:30 AM, Pavel Stehule wrote:
> 2013/8/23 Josh Berkus <josh@agliodbs.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@agliodbs.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

Josh Berkus <josh@agliodbs.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 <<a href="mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>> wrote:<br
/>>Josh Berkus <<a href="mailto:josh@agliodbs.com">josh@agliodbs.com</a>> writes:<br />>> Currently the
onlyway to return query results to the caller is to use<br /> >> some form of RETURN.  It is 100% consistent.<br
/>><br/>> I don't find it consistent at all, because what that means is that the<br />> data is to be returned
tothe SQL statement that called the function.<br /> ><br />> What's more, the point of any such extension needs
tobe to allow<br />> *multiple* resultsets to be returned to the client --- if you only need<br />> one, you can
havethat functionality today with plain old SELECT FROM<br /> > myfunction().  And returning some data but
continuingexecution is surely<br />> not consistent with RETURN.<br /><br />With set returning functions, RETURN
QUERYetc means 'yield this data' -- which is pretty weird -- so your point only holds true for unadorned return (not
RETURNNEXT , 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),
soI agree (after some reflection) with the spirit of your point.  It's not good to have principle keywords do markedly
differentthings.<br /><br />> Basically it seems that we have two choices for how to represent this<br />>
(hypothetical)future functionality:<br />><br />> 1. Define SELECT without INTO as meaning return results
directlyto client;<br />><br /> > 2. Invent some new syntax to do it.<br />><br />> In a green field I
thinkwe'd want to do #2, because #1 seems rather<br />> error-prone and unobvious.  The only real attraction of #1,
IMO,is that<br />> it's consistent with T-SQL.  But that's not a terribly strong argument<br /> > given the many
existinginconsistencies between T-SQL and plpgsql.<br /><br />Very good points.  I think the only compelling case for
#1that 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.<br /><br />> BTW, what
aboutINSERT/UPDATE/DELETE RETURNING?  Do we want to let<br />> these execute and throw away the data?  The argument
thatthis would<br />> be a feature seems a lot weaker than for SELECT, because after all you<br /> > could
usuallyjust leave off the RETURNING clause.  But I'm sure somebody<br />> will say they want to put a function with
side-effectsinto RETURNING<br />> and then ignore its output.<br /><br />If we agree to relax PERFORM, those should
berelaxed on the same basis.  In fact, this is conclusive evidence that PERFORM is obsolete: it hails from the days
whereSELECT was the only data returning DML.<br /><br />merlin<br /> 

Pavel Stehule <pavel.stehule@gmail.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

On Aug 27, 2013, at 12:30 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

> I disagree - Tom K. speaking about what he likes or dislikes (and about what he didn't use) He forgot about strong
pointsof 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
formof 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
whateverto 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
dataas it processes, rather than buffering the results and returning them all at once, perhaps we could add YIELD,
YEILDQUERY, 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
notreturn, 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.stehule@gmail.com> wrote:
>
>> I disagree - Tom K. speaking about what he likes or dislikes (and about what he didn't use) He forgot about strong
pointsof 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
formof 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,
aswell.
 
>
> However, I do not think it should be implicit. If a function or procedure wants to return values or query results or
whateverto 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
dataas it processes, rather than buffering the results and returning them all at once, perhaps we could add YIELD,
YEILDQUERY, 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Ü

Pavel Stehule <pavel.stehule@gmail.com> writes:
> 2013/8/27 David E. Wheeler <david@justatheory.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

On Aug 27, 2013, at 1:36 PM, Pavel Stehule <pavel.stehule@gmail.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
shouldinstead 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,
inthe 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
seemstoo magical. I strongly prefer that the scope of the code executed in a function or procedure be limited to the
scopeof the function or procedure itself, and only return data to the caller if I explicitly tell it to. Much less
magical,IMHO. 

Best,

David

On Aug 27, 2013, at 3:10 PM, Pavel Stehule <pavel.stehule@gmail.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
theybe 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
sensethere, in which case, when you develop such functionality to Postgres, you would need to figure out how to get
PERFORMto work with CTEs. But PL/pgSQL requires an explicit key word to return data, and I am hard pressed to see why
thatwould 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
voidfunctions 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
toclient (in procedures). 

Consistent, yes. But I’m not convinced -- and I’m *certainly* not convinced that PERFORM should be required to discard
queryresults 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.stehule@gmail.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@gmail.com> wrote:
> On Tue, Aug 27, 2013 at 6:10 PM, Pavel Stehule <pavel.stehule@gmail.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.stehule@gmail.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Ü

On Aug 29, 2013, at 1:11 PM, Pavel Stehule <pavel.stehule@gmail.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,
becausesemantic 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 -
thereis 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
SELECTinternally, 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
inthe source. 

Best,

David

On Aug 29, 2013, at 2:22 PM, Pavel Stehule <pavel.stehule@gmail.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
necessityto 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
PERFORMwork 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.stehule@gmail.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
necessityto 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
PERFORMwork 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

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

> I am thinking, so I propose a enough solution for you - when you use CTE for execution of VOID function, then result
villbe VOID set, what we can accept as undefined result, and in this case a PERFORM should not be required. If CTE will
returnsome result, then PERFORM should be required and PERFORM must to support CTE in all possible modes - updateable
ornot 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
reasonnot to let SELECT results be discarded, either. I know you think there are good reasons, but no one else in this
threadis convince, AFAICT. 

Best,

David

On 08/29/2013 11:01 PM, David E. Wheeler wrote:
> On Aug 29, 2013, at 1:11 PM, Pavel Stehule <pavel.stehule@gmail.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,
becausesemantic 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 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

On Aug 29, 2013, at 2:48 PM, Andres Freund <andres@2ndquadrant.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
mentalload is higher, even if it can be fixed with CTEs, it’s more trouble than it’s worth. 

Best,

David

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

On 08/29/2013 05:31 PM, David E. Wheeler wrote:
> On Aug 29, 2013, at 2:22 PM, Pavel Stehule <pavel.stehule@gmail.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
necessityto 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
PERFORMwork 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 David E. Wheeler <david@justatheory.com>

On Aug 29, 2013, at 2:48 PM, Andres Freund <andres@2ndquadrant.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,

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