Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-29 Thread Pavel Stehule
2013/8/28 Hannu Krosing ha...@2ndquadrant.com On 08/28/2013 12:10 AM, Pavel Stehule wrote: so if I write on client side BEGIN; SELECT 1,2; SELECT 2; SELECT 3,4; END; then I expect results 1,2 2 3,4 And you are perfectly ok to discard the results Actually it

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-29 Thread Pavel Stehule
2013/8/28 Robert Haas robertmh...@gmail.com On Tue, Aug 27, 2013 at 6:10 PM, Pavel Stehule pavel.steh...@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

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-29 Thread David E. Wheeler
On Aug 29, 2013, at 1:11 PM, Pavel Stehule pavel.steh...@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, because semantic is significantly different. And I see a increasing inconsistency between a original ADA

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-29 Thread Pavel Stehule
2013/8/29 David E. Wheeler da...@justatheory.com On Aug 29, 2013, at 1:11 PM, Pavel Stehule pavel.steh...@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, because semantic is significantly different. And I see

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-29 Thread David E. Wheeler
On Aug 29, 2013, at 2:22 PM, Pavel Stehule pavel.steh...@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 necessity to use a PERFORM for call of VOID functions. Well, in this thread, I believe you are the

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-29 Thread Andres Freund
On 2013-08-29 14:31:55 -0700, David E. Wheeler wrote: On Aug 29, 2013, at 2:22 PM, Pavel Stehule pavel.steh...@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 necessity to use a PERFORM for call of VOID

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-29 Thread Josh Berkus
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

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-29 Thread Pavel Stehule
2013/8/29 David E. Wheeler da...@justatheory.com On Aug 29, 2013, at 2:22 PM, Pavel Stehule pavel.steh...@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 necessity to use a PERFORM for call of VOID

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-29 Thread Hannu Krosing
On 08/29/2013 11:01 PM, David E. Wheeler wrote: On Aug 29, 2013, at 1:11 PM, Pavel Stehule pavel.steh...@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, because semantic is significantly different. And I see a

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-29 Thread David E. Wheeler
On Aug 29, 2013, at 2:41 PM, Pavel Stehule pavel.steh...@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 vill be VOID set, what we can accept as undefined result, and in this case a PERFORM should not be

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-29 Thread Andres Freund
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

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-29 Thread Pavel Stehule
2013/8/29 Josh Berkus j...@agliodbs.com On 08/29/2013 02:22 PM, Pavel Stehule wrote: Still I don't think so correct solution is enabling a unbound SELECTs, but correct is a fix a PERFORM and remove a necessity to use a PERFORM for call of VOID functions. You have yet to supply any

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-29 Thread David E. Wheeler
On Aug 29, 2013, at 2:48 PM, Andres Freund and...@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

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-29 Thread Pavel Stehule
2013/8/29 Pavel Stehule pavel.steh...@gmail.com 2013/8/29 Josh Berkus j...@agliodbs.com On 08/29/2013 02:22 PM, Pavel Stehule wrote: Still I don't think so correct solution is enabling a unbound SELECTs, but correct is a fix a PERFORM and remove a necessity to use a PERFORM for call

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-29 Thread Andres Freund
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

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-29 Thread Pavel Stehule
2013/8/29 David E. Wheeler da...@justatheory.com On Aug 29, 2013, at 2:48 PM, Andres Freund and...@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

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-29 Thread Andrew Dunstan
On 08/29/2013 05:31 PM, David E. Wheeler wrote: On Aug 29, 2013, at 2:22 PM, Pavel Stehule pavel.steh...@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 necessity to use a PERFORM for call of VOID functions.

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-29 Thread Pavel Stehule
2013/8/29 Andres Freund and...@2ndquadrant.com On 2013-08-29 23:54:28 +0200, Pavel Stehule wrote: No think so PERFORM is a significant problem. A mayor problem for beginners is usually a fact, so PL/pgSQL is ALGOL like languages - and they don't know with these languages. Second

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-29 Thread Hannu Krosing
On 08/30/2013 12:04 AM, Pavel Stehule wrote: 2013/8/29 David E. Wheeler da...@justatheory.com mailto:da...@justatheory.com On Aug 29, 2013, at 2:48 PM, Andres Freund and...@2ndquadrant.com mailto:and...@2ndquadrant.com wrote: You have yet to supply any arguments which

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-28 Thread Robert Haas
On Tue, Aug 27, 2013 at 6:10 PM, Pavel Stehule pavel.steh...@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

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-28 Thread Merlin Moncure
On Wed, Aug 28, 2013 at 2:59 PM, Robert Haas robertmh...@gmail.com wrote: On Tue, Aug 27, 2013 at 6:10 PM, Pavel Stehule pavel.steh...@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.

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-28 Thread Hannu Krosing
On 08/28/2013 09:59 PM, Robert Haas wrote: On Tue, Aug 27, 2013 at 6:10 PM, Pavel Stehule pavel.steh...@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

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-27 Thread Pavel Stehule
2013/8/24 Tom Lane t...@sss.pgh.pa.us Pavel Stehule pavel.steh...@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

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-27 Thread David E. Wheeler
On Aug 27, 2013, at 12:30 AM, Pavel Stehule pavel.steh...@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 points of implicit result or interesting points. Clients usually has no problem with dynamic datasets

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-27 Thread Hannu Krosing
On 08/27/2013 08:32 PM, David E. Wheeler wrote: On Aug 27, 2013, at 12:30 AM, Pavel Stehule pavel.steh...@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 points of implicit result or interesting points.

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-27 Thread Pavel Stehule
2013/8/27 David E. Wheeler da...@justatheory.com On Aug 27, 2013, at 12:30 AM, Pavel Stehule pavel.steh...@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 points of implicit result or interesting points.

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-27 Thread Tom Lane
Pavel Stehule pavel.steh...@gmail.com writes: 2013/8/27 David E. Wheeler da...@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

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-27 Thread Pavel Stehule
2013/8/27 Tom Lane t...@sss.pgh.pa.us Pavel Stehule pavel.steh...@gmail.com writes: 2013/8/27 David E. Wheeler da...@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

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-27 Thread David E. Wheeler
On Aug 27, 2013, at 1:36 PM, Pavel Stehule pavel.steh...@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

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-27 Thread Pavel Stehule
2013/8/27 David E. Wheeler da...@justatheory.com On Aug 27, 2013, at 1:36 PM, Pavel Stehule pavel.steh...@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

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-27 Thread David E. Wheeler
On Aug 27, 2013, at 3:10 PM, Pavel Stehule pavel.steh...@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

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-27 Thread Hannu Krosing
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

[HACKERS] PL/pgSQL PERFORM with CTE

2013-08-24 Thread Merlin Moncure
On Fri, Aug 23, 2013 at 5:07 PM, Tom Lane t...@sss.pgh.pa.us wrote: Josh Berkus j...@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

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-24 Thread Pavel Stehule
2013/8/24 Merlin Moncure mmonc...@gmail.com On Fri, Aug 23, 2013 at 5:07 PM, Tom Lane t...@sss.pgh.pa.us wrote: Josh Berkus j...@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

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-24 Thread Tom Lane
Pavel Stehule pavel.steh...@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

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-23 Thread Josh Berkus
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

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-23 Thread Merlin Moncure
On Fri, Aug 23, 2013 at 12:51 PM, Josh Berkus j...@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

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-23 Thread Pavel Stehule
2013/8/23 Josh Berkus j...@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

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-23 Thread Pavel Stehule
2013/8/23 Merlin Moncure mmonc...@gmail.com On Fri, Aug 23, 2013 at 12:51 PM, Josh Berkus j...@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

Re: [HACKERS] PL/pgSQL PERFORM with CTE

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

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-23 Thread Pavel Stehule
2013/8/23 Merlin Moncure mmonc...@gmail.com On Fri, Aug 23, 2013 at 1:38 PM, Pavel Stehule pavel.steh...@gmail.com wrote: 2013/8/23 Merlin Moncure mmonc...@gmail.com I think so is not good if some programming language functionality does one in one context (functions) and does

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-23 Thread David E. Wheeler
On Aug 23, 2013, at 8:51 PM, Pavel Stehule pavel.steh...@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.

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-23 Thread Pavel Stehule
2013/8/23 David E. Wheeler da...@justatheory.com On Aug 23, 2013, at 8:51 PM, Pavel Stehule pavel.steh...@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,

Re: [HACKERS] PL/pgSQL PERFORM with CTE

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

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-23 Thread Marko Tiikkaja
On 2013-08-23 22:02, Josh Berkus wrote: On 08/23/2013 11:30 AM, Pavel Stehule wrote: 2013/8/23 Josh Berkus j...@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

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-23 Thread Josh Berkus
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

Re: [HACKERS] PL/pgSQL PERFORM with CTE

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

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-23 Thread Tom Lane
Josh Berkus j...@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

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-22 Thread Josh Berkus
On 08/20/2013 05:48 AM, Merlin Moncure wrote: On Tue, Aug 20, 2013 at 7:44 AM, Pavel Stehule pavel.steh...@gmail.com wrote: 2013/8/20 Merlin Moncure mmonc...@gmail.com On Tue, Aug 20, 2013 at 7:25 AM, Andres Freund and...@2ndquadrant.com I think the way forward is to remove the

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-22 Thread Tom Lane
Josh Berkus j...@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

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-22 Thread Josh Berkus
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

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-22 Thread Pavel Stehule
2013/8/23 Josh Berkus j...@agliodbs.com Tom, Jan might remember more about his thought process here, but I'm thinking that he copied the SELECT-must-have-INTO rule and then chose to invent a new statement for the case of wanting to discard the result. I think you could make an

[HACKERS] PL/pgSQL PERFORM with CTE

2013-08-20 Thread David E. Wheeler
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

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-20 Thread Pavel Stehule
Hello 2013/8/20 David E. Wheeler da...@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

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-20 Thread David E. Wheeler
Hi Pavel, On Aug 20, 2013, at 2:11 PM, Pavel Stehule pavel.steh...@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:

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-20 Thread Pavel Stehule
2013/8/20 David E. Wheeler da...@justatheory.com Hi Pavel, On Aug 20, 2013, at 2:11 PM, Pavel Stehule pavel.steh...@gmail.com wrote: david=# DO $$ david$# BEGIN david$# WITH now AS (SELECT now()) david$# PERFORM * from now; david$# END; david$# $$;

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-20 Thread Andres Freund
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.steh...@gmail.com wrote: david=# DO $$ david$# BEGIN david$# WITH now AS (SELECT now()) david$# PERFORM * from now; david$# END;

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-20 Thread Marko Tiikkaja
On 8/20/13 2:21 PM, Pavel Stehule wrote: 2013/8/20 David E. Wheeler da...@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

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-20 Thread Pavel Stehule
2013/8/20 Andres Freund and...@2ndquadrant.com On 2013-08-20 14:15:55 +0200, David E. Wheeler wrote: Hi Pavel, On Aug 20, 2013, at 2:11 PM, Pavel Stehule pavel.steh...@gmail.com wrote: david=# DO $$ david$# BEGIN david$# WITH now AS (SELECT now()) david$#

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-20 Thread David E. Wheeler
On Aug 20, 2013, at 2:24 PM, Marko Tiikkaja ma...@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

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-20 Thread Pavel Stehule
2013/8/20 David E. Wheeler da...@justatheory.com On Aug 20, 2013, at 2:24 PM, Marko Tiikkaja ma...@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

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-20 Thread David E. Wheeler
On Aug 20, 2013, at 2:31 PM, Pavel Stehule pavel.steh...@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 (

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-20 Thread Pavel Stehule
2013/8/20 David E. Wheeler da...@justatheory.com On Aug 20, 2013, at 2:31 PM, Pavel Stehule pavel.steh...@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

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-20 Thread Merlin Moncure
On Tue, Aug 20, 2013 at 7:25 AM, Andres Freund and...@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.steh...@gmail.com wrote: david=# DO $$ david$# BEGIN david$# WITH now AS (SELECT

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-20 Thread Pavel Stehule
2013/8/20 Merlin Moncure mmonc...@gmail.com On Tue, Aug 20, 2013 at 7:25 AM, Andres Freund and...@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.steh...@gmail.com wrote: david=# DO $$

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-20 Thread David E. Wheeler
On Aug 20, 2013, at 2:41 PM, Pavel Stehule pavel.steh...@gmail.com wrote: yes, in this context you should not use a PERFORM PL/pgSQL protect you before useless queries - so you can use a CTE without returned result directly or CTE with result via PERFORM statement (and in this case it

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-20 Thread Boszormenyi Zoltan
2013-08-20 14:35 keltezéssel, David E. Wheeler írta: On Aug 20, 2013, at 2:31 PM, Pavel Stehule pavel.steh...@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$#

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-20 Thread David E. Wheeler
On Aug 20, 2013, at 2:44 PM, Pavel Stehule pavel.steh...@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?

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-20 Thread Merlin Moncure
On Tue, Aug 20, 2013 at 7:44 AM, Pavel Stehule pavel.steh...@gmail.com wrote: 2013/8/20 Merlin Moncure mmonc...@gmail.com On Tue, Aug 20, 2013 at 7:25 AM, Andres Freund and...@2ndquadrant.com wrote: On 2013-08-20 14:15:55 +0200, David E. Wheeler wrote: Hi Pavel, On Aug 20, 2013, at

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-20 Thread Pavel Stehule
2013/8/20 David E. Wheeler da...@justatheory.com On Aug 20, 2013, at 2:41 PM, Pavel Stehule pavel.steh...@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

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-20 Thread Marko Tiikkaja
On 8/20/13 2:53 PM, Pavel Stehule wrote: 2013/8/20 David E. Wheeler da...@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

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-20 Thread David E. Wheeler
On Aug 20, 2013, at 2:53 PM, Pavel Stehule pavel.steh...@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

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-20 Thread Pavel Stehule
2013/8/20 David E. Wheeler da...@justatheory.com On Aug 20, 2013, at 2:53 PM, Pavel Stehule pavel.steh...@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

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-20 Thread David E. Wheeler
On Aug 20, 2013, at 3:05 PM, Pavel Stehule pavel.steh...@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. Have no problem with it. Glad to have you on board. :-) I don't would to enable a free unbound

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-20 Thread Pavel Stehule
2013/8/20 David E. Wheeler da...@justatheory.com On Aug 20, 2013, at 3:05 PM, Pavel Stehule pavel.steh...@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. Have no problem with it. Glad to have you on

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-20 Thread David E. Wheeler
On Aug 20, 2013, at 3:18 PM, Pavel Stehule pavel.steh...@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[]

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-20 Thread Pavel Stehule
2013/8/20 David E. Wheeler da...@justatheory.com On Aug 20, 2013, at 3:18 PM, Pavel Stehule pavel.steh...@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

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-20 Thread David E. Wheeler
On Aug 20, 2013, at 3:38 PM, Pavel Stehule pavel.steh...@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

Re: [HACKERS] PL/pgSQL PERFORM with CTE

2013-08-20 Thread Pavel Stehule
2013/8/20 David E. Wheeler da...@justatheory.com On Aug 20, 2013, at 3:38 PM, Pavel Stehule pavel.steh...@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