Thread: 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@justatheory.com>
Hackers,
This seems reasonable:
david=# DO $$
david$# BEGIN
david$# WITH now AS (SELECT now())
david$# SELECT * from now;
david$# END;
david$# $$;
ERROR: query has no destination for result data
HINT: If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT: PL/pgSQL function inline_code_block line 3 at SQL statement
This not so much:
david=# DO $$
david$# BEGIN
david$# WITH now AS (SELECT now())
david$# PERFORM * from now;
david$# END;
david$# $$;
ERROR: syntax error at or near "PERFORM"
LINE 4: PERFORM * from now;
^
Parser bug in PL/pgSQL, perhaps?
no
you cannot use a PL/pgSQL statement inside SQL statement.
Regards
Pavel
Pavel
Best,
David
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
2013/8/20 David E. Wheeler <david@justatheory.com>
postgres=# DO $$
BEGIN
PERFORM * FROM (WITH now AS (SELECT now())
SELECT * from now) x;
END;
$$;
DO
postgres=#
Hi Pavel,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?
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.
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
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
2013/8/20 Andres Freund <andres@2ndquadrant.com>
+1
Not that that's elegant but IIRC PERFORM (WITH ...) ought to work. IOn 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.
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 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
2013/8/20 David E. Wheeler <david@justatheory.com>
On Aug 20, 2013, at 2:24 PM, Marko Tiikkaja <marko@joh.to> wrote:Which, in fact, is exactly my use case (though not what I posted upthread).
>> 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.
but it works
postgres=# do $$begin with x as (select 10) insert into omega select * from x; end;$$;
DO
postgres=# do $$begin with x as (select 10) insert into omega select * from x; end;$$;
DO
Regards
Pavel
Pavel
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
2013/8/20 David E. Wheeler <david@justatheory.com>
On Aug 20, 2013, at 2:31 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:But this does not:
> but it works
>
> postgres=# do $$begin with x as (select 10) insert into omega select * from x; end;$$;
> DOdavid$# PERFORM * FROM (
david=# DO $$
david$# BEGIN
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@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
2013/8/20 Merlin Moncure <mmoncure@gmail.com>
On Tue, Aug 20, 2013 at 7:25 AM, Andres Freund <andres@2ndquadrant.com> wrote:I think the way forward is to remove the restriction such that data
> 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.
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.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
2013/8/20 David E. Wheeler <david@justatheory.com>
On Aug 20, 2013, at 2:41 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.
> 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?
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@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
2013/8/20 David E. Wheeler <david@justatheory.com>
On Aug 20, 2013, at 2:53 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote: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.
>> 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.
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.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
2013/8/20 David E. Wheeler <david@justatheory.com>
On Aug 20, 2013, at 3:05 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:Glad to have you on board. :-)
> 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 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.
> I don't would to enable a free unbound statement that returns result.
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.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
2013/8/20 David E. Wheeler <david@justatheory.com>
On Aug 20, 2013, at 3:18 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:This is not dissimilar to what I am actually doing:
> can you show some examples, please
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
Pavel
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
2013/8/20 David E. Wheeler <david@justatheory.com>
On Aug 20, 2013, at 3:38 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote: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.
> pg_notify returns void, so there are no necessary casting to void
>
> so enhanced check - so all returned columns are void should be enough
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
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.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
2013/8/23 Josh Berkus <josh@agliodbs.com>
Tom,For my own part, I have to correct forgetting to substitute "PERORM" for
> 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.
"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
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
2013/8/23 Josh Berkus <josh@agliodbs.com>
Pavel,Are you planning to implement that in PL/pgSQL?
> 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.
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
Pavel
2013/8/23 Merlin Moncure <mmoncure@gmail.com>
On Fri, Aug 23, 2013 at 12:51 PM, Josh Berkus <josh@agliodbs.com> wrote:I don't think Pavel's point makes sense in the context of functions.
> 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.
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
Pavel
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
2013/8/23 Merlin Moncure <mmoncure@gmail.com>
On Fri, Aug 23, 2013 at 1:38 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:>It's not really different -- it means 'return if able'. Also there
>
>
> 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).
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.Completely disagree. There are many cases where this is *not*
> 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.
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
Pavel
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
2013/8/23 David E. Wheeler <david@justatheory.com>
On Aug 23, 2013, at 8:51 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote: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:
> 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.
* 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@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
2013/8/23 Josh Berkus <josh@agliodbs.com>
On 08/23/2013 11:30 AM, Pavel Stehule wrote:Is there some reason we wouldn't use RETURN QUERY in that case, instead
> 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
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 <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 />
2013/8/24 Merlin Moncure <mmoncure@gmail.com>
On Fri, Aug 23, 2013 at 5:07 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: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.
> Josh Berkus <josh@agliodbs.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.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.
> 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.
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
Pavel
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.
> 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.
merlin
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
2013/8/24 Tom Lane <tgl@sss.pgh.pa.us>
Pavel Stehule <pavel.stehule@gmail.com> writes:That article is worth reading, because Tom K. points out exactly why
> 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
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.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Ü
2013/8/27 David E. Wheeler <david@justatheory.com>
On Aug 27, 2013, at 12:30 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:As a dynamic language programmer, I can see this, as long as it’s not to the exclusion of strong typing interfaces, as well.
> 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.
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.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
2013/8/27 Tom Lane <tgl@sss.pgh.pa.us>
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 returnWe aren't following T-SQL on any other syntax detail, so why would we
>> 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.
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
Pavel
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
2013/8/27 David E. Wheeler <david@justatheory.com>
2
3,4
On Aug 27, 2013, at 1:36 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:That does not make it a bad idea. Let me summarize:
> 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.
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
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,22
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.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Ü
2013/8/28 Hannu Krosing <hannu@2ndquadrant.com>
On 08/28/2013 12:10 AM, Pavel Stehule wrote:And you are perfectly ok to discard the results
>
>
> 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
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.>And you are perfectly ok to discard the results here as well
> 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.
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.
So you can ignore the result in a procedure (by just skipping / not
> 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).
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@gmail.com>
On Tue, Aug 27, 2013 at 6:10 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:The biggest problem with this idea is that people will do it by
> 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
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
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.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
2013/8/29 David E. Wheeler <david@justatheory.com>
On Aug 29, 2013, at 1:11 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:So YIELD or implement PL/PSM.
> 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.
We can discussed about syntax later - now it is offtopic and it is too early - still we miss a procedures.
PL/pgSQL is not 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.
yes, I know it well - although some syntax is shared - CASE statements
No reason SELECT could not work just a well.
> 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, 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.
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.
> A unhelpful error message has zero relevant to topic - just almost all in PL/pgSQL is SELECT.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.
> 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
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
Pavel
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
2013/8/29 David E. Wheeler <david@justatheory.com>
On Aug 29, 2013, at 2:22 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote: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.
> 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.
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
Pavel
Best,
David
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
2013/8/29 Josh Berkus <josh@agliodbs.com>
On 08/29/2013 02:22 PM, Pavel Stehule wrote:You have yet to supply any arguments which support this position.
> 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.
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
Pavel
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
2013/8/29 Pavel Stehule <pavel.stehule@gmail.com>
2013/8/29 Josh Berkus <josh@agliodbs.com>On 08/29/2013 02:22 PM, Pavel Stehule wrote:You have yet to supply any arguments which support this position.
> 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.
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
Pavel
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@justatheory.com>
On Aug 29, 2013, at 2:48 PM, Andres Freund <andres@2ndquadrant.com> wrote:So now we can revise Josh’s assertion to: “I have seen only tepid, unconvincing arguments which support this position.”
>> 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.
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
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.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 Andres Freund <andres@2ndquadrant.com>
On 2013-08-29 23:54:28 +0200, Pavel Stehule wrote:So what? That's completely orthogonal to the discussion at hand. We're
> > 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.
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
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:
This has a bit more value as it allows you to detect some (though not all) structural errors.2013/8/29 David E. Wheeler <david@justatheory.com>On Aug 29, 2013, at 2:48 PM, Andres Freund <andres@2ndquadrant.com> wrote:So now we can revise Josh’s assertion to: “I have seen only tepid, unconvincing arguments which support this position.”
>> 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.
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, ...
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 ?
If you want a verbose expression for ignoring the result I'd suggestFor me, this talk is similar - we have a language, that was designed be secure and verbose,
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Ü