Re: [HACKERS] Anonymous code block with parameters
On 9/18/14 7:40 AM, Andres Freund wrote: I fail to see why that is so much preferrable for you to passing parameter to DO? 1) You need to think about unique names for functions 2) Doesn't work on HOT STANDBYs 3) Causes noticeable amount of catalog bloat 4) Is about a magnitude or two more expensive Doesn't this apply to all temporary objects? It would also be great to have temporary tables, temporary indexes, temporary triggers, temporary extensions, etc. that don't have the above problems. I think inventing a separate mechanism for working around each instance of this problem would end up being very confusing. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Anonymous code block with parameters
On 2014-09-22 15:46:48 -0400, Peter Eisentraut wrote: On 9/18/14 7:40 AM, Andres Freund wrote: I fail to see why that is so much preferrable for you to passing parameter to DO? 1) You need to think about unique names for functions 2) Doesn't work on HOT STANDBYs 3) Causes noticeable amount of catalog bloat 4) Is about a magnitude or two more expensive Doesn't this apply to all temporary objects? It would also be great to have temporary tables, temporary indexes, temporary triggers, temporary extensions, etc. that don't have the above problems. I think inventing a separate mechanism for working around each instance of this problem would end up being very confusing. Meh. Those aren't comparable. TEMPORARY TABLES/INDEXES/... all live beyond a single statement. What's being discussed here doesn't. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Anonymous code block with parameters
On Mon, Sep 22, 2014 at 2:49 PM, Andres Freund and...@2ndquadrant.com wrote: On 2014-09-22 15:46:48 -0400, Peter Eisentraut wrote: On 9/18/14 7:40 AM, Andres Freund wrote: I fail to see why that is so much preferrable for you to passing parameter to DO? 1) You need to think about unique names for functions 2) Doesn't work on HOT STANDBYs 3) Causes noticeable amount of catalog bloat 4) Is about a magnitude or two more expensive Doesn't this apply to all temporary objects? It would also be great to have temporary tables, temporary indexes, temporary triggers, temporary extensions, etc. that don't have the above problems. I think inventing a separate mechanism for working around each instance of this problem would end up being very confusing. Meh. Those aren't comparable. TEMPORARY TABLES/INDEXES/... all live beyond a single statement. What's being discussed here doesn't. Even if that wasn't true, 'DO' doesn't involve changes to system catalogs whereas temporary functions would. With a little imagination I could come up a with a scenario involving a script of a whole bunch of repeated trivial DO statements which would involve a lot less beating on the system catalogs. When the data-modifying-with feature was considered, an implementation that relied on temp tables was rejected at least in part because of system catalog thrash and poorer performance for very trivial queries. So, to me, DO vs CREATE FUNCTION has nothing to do with passing arguments and/or returning data. It has to do with lifespan; single call of the function body only, use DO, otherwise, create a function. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Anonymous code block with parameters
On 22/09/14 22:58, Merlin Moncure wrote: Meh. Those aren't comparable. TEMPORARY TABLES/INDEXES/... all live beyond a single statement. What's being discussed here doesn't. Even if that wasn't true, 'DO' doesn't involve changes to system catalogs whereas temporary functions would. With a little imagination I could come up a with a scenario involving a script of a whole bunch of repeated trivial DO statements which would involve a lot less beating on the system catalogs. When the data-modifying-with feature was considered, an implementation that relied on temp tables was rejected at least in part because of system catalog thrash and poorer performance for very trivial queries. So, to me, DO vs CREATE FUNCTION has nothing to do with passing arguments and/or returning data. It has to do with lifespan; single call of the function body only, use DO, otherwise, create a function. Actually same thing happened with the DO implementation itself - creating anonymous/hidden temporary functions in the background was also considered but was decided it's not acceptable (for similar reason temp tables were rejected for WITH). So we decided at least twice already that this kind of solution is bad, I don't know of any change that would invalidate the reasons for deciding that way so I don't see why they would suddenly become acceptable... -- Petr Jelinek http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Anonymous code block with parameters
On 09/23/2014 07:20 AM, Petr Jelinek wrote: So, to me, DO vs CREATE FUNCTION has nothing to do with passing arguments and/or returning data. It has to do with lifespan; single call of the function body only, use DO, otherwise, create a function. Actually same thing happened with the DO implementation itself - creating anonymous/hidden temporary functions in the background was also considered but was decided it's not acceptable (for similar reason temp tables were rejected for WITH). So we decided at least twice already that this kind of solution is bad, I don't know of any change that would invalidate the reasons for deciding that way so I don't see why they would suddenly become acceptable... All good points. I was wrong to suggest just going for TEMPORARY FUNCTION before, there's clearly a useful place for DO with parameters. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Anonymous code block with parameters
On Thu, Sep 18, 2014 at 5:22 PM, Hannu Krosing ha...@2ndquadrant.com wrote: Though it would be even nicer to have fully in-line type definition SELECT (tup).* FROM ( SELECT CASE WHEN .. THEN ROW(1,2,3)::(a int, b text, c int2) WHEN .. THEN ROW(2,3,4) ELSE ROW (3,4,5) END AS tup FROM .. ) ss +1. Workaround at present (which I mostly use during json serialization) is: SELECT (tup).* FROM ( SELECT CASE WHEN .. THEN (SELECT q FROM (SELECT 1, 2, 3) q) WHEN .. THEN (SELECT q FROM (SELECT 2, 3, 4) q) ELSE (SELECT q FROM (SELECT 3, 4, 5) q) END AS tup FROM .. ) ss If you're talking in line type definitions (which is kinda off topic) though, it'd be nice to consider: * nested type definition: create type foo_t as ( a text, b int, bars bar_t[] as ( c int, d text ), baz baz_t as ( e text, f text ) ); * ...and recursive type references (not being able to recursively serialize json is a major headache) create type foo_t as ( path text, children foo_t[] ); merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Anonymous code block with parameters
On Fri, Sep 19, 2014 at 9:26 AM, Merlin Moncure mmonc...@gmail.com wrote: On Thu, Sep 18, 2014 at 5:22 PM, Hannu Krosing ha...@2ndquadrant.com wrote: Though it would be even nicer to have fully in-line type definition SELECT (tup).* FROM ( SELECT CASE WHEN .. THEN ROW(1,2,3)::(a int, b text, c int2) WHEN .. THEN ROW(2,3,4) ELSE ROW (3,4,5) END AS tup FROM .. ) ss +1. Workaround at present (which I mostly use during json serialization) is: SELECT (tup).* FROM ( SELECT CASE WHEN .. THEN (SELECT q FROM (SELECT 1, 2, 3) q) WHEN .. THEN (SELECT q FROM (SELECT 2, 3, 4) q) ELSE (SELECT q FROM (SELECT 3, 4, 5) q) END AS tup FROM .. ) ss actually, this trick *only* works during json serialization -- it allows control over the column names that row() masks over. trying to expand (tup).* still gives the dreaded ERROR: record type has not been registered. That's because this works: select (q).* from (select 1 as a, 2 as b) q; but this doesn't: select ((select q from (select a,b) q)).* from (select 1 as a, 2 as b) q; merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Anonymous code block with parameters
On 2014-09-19 8:20 PM, Merlin Moncure wrote: actually, this trick *only* works during json serialization -- it allows control over the column names that row() masks over. trying to expand (tup).* still gives the dreaded ERROR: record type has not been registered. That's because this works: select (q).* from (select 1 as a, 2 as b) q; but this doesn't: select ((select q from (select a,b) q)).* from (select 1 as a, 2 as b) q; Yeah. This is a seriously missing feature and a PITA. :-( .marko -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Anonymous code block with parameters
On Wed, Sep 17, 2014 at 10:17:22PM +0200, Pavel Stehule wrote: Because you still have to do SELECT pg_temp.my_temp_function(blah); to execute it. this problem should be solvable. I can to use a temporary tables without using pg_temp schema. Umm, IIRC it used to work that way but was changed to work like this. IIRC the reason was that anyone can create functions in the temp tablespace and thus hijack other functions that more priviledged functions might call. Or something like that. I think it was even a CVE. Have a nice dat, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [HACKERS] Anonymous code block with parameters
On 2014-09-17 22:17:22 +0200, Pavel Stehule wrote: 2014-09-17 22:07 GMT+02:00 Vik Fearing vik.fear...@dalibo.com: On 09/16/2014 10:09 AM, Heikki Linnakangas wrote: On 09/16/2014 10:57 AM, Craig Ringer wrote: On 09/16/2014 03:15 PM, Pavel Stehule wrote: Why we don't introduce a temporary functions instead? I think that'd be a lot cleaner and simpler. It's something I've frequently wanted, and as Hekki points out it's already possible by creating the function in pg_temp, there just isn't the syntax sugar for CREATE TEMPORARY FUNCTION. So why not just add CREATE TEMPORARY FUNCTION? Sure, why not. Because you still have to do SELECT pg_temp.my_temp_function(blah); to execute it. this problem should be solvable. I can to use a temporary tables without using pg_temp schema. I fail to see why that is so much preferrable for you to passing parameter to DO? 1) You need to think about unique names for functions 2) Doesn't work on HOT STANDBYs 3) Causes noticeable amount of catalog bloat 4) Is about a magnitude or two more expensive So yes, TEMPORARY FUNCTION would be helpful. But it's simply a different feature. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Anonymous code block with parameters
On 2014-09-18 13:44:47 +0200, Pavel Stehule wrote: 2014-09-18 13:40 GMT+02:00 Andres Freund and...@2ndquadrant.com: On 2014-09-17 22:17:22 +0200, Pavel Stehule wrote: 2014-09-17 22:07 GMT+02:00 Vik Fearing vik.fear...@dalibo.com: I fail to see why that is so much preferrable for you to passing parameter to DO? 1) You need to think about unique names for functions 2) Doesn't work on HOT STANDBYs 3) Causes noticeable amount of catalog bloat 4) Is about a magnitude or two more expensive 1. I am not against simple DO, what doesn't substitute functions 2. When DO have to substitute functions, then I don't see a benefits Show me real use case please? Did you read what I wrote above? I'm sure you can rephrase them to be more 'use case' like yourself. Isn't being able to do this on a standby a fundamental enough advantage? Being significantly cheaper? Needing fewer roundtrips? Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Anonymous code block with parameters
On 9/18/14 1:35 PM, Martijn van Oosterhout wrote:
On Wed, Sep 17, 2014 at 10:17:22PM +0200, Pavel Stehule wrote:
Because you still have to do
SELECT pg_temp.my_temp_function(blah);
to execute it.
this problem should be solvable. I can to use a temporary tables without
using pg_temp schema.
Umm, IIRC it used to work that way but was changed to work like this.
IIRC the reason was that anyone can create functions in the temp
tablespace and thus hijack other functions that more priviledged
functions might call.
The same argument applies to temporary tables *already*. Consider:
=# create function oops() returns void as $$
$# begin insert into foo default values; end $$ language plpgsql
-# security definer;
CREATE FUNCTION
=# grant execute on function oops() to peasant;
GRANT
Then peasant does:
= create temporary table foo();
CREATE TABLE
= create function pg_temp.now_im_superuser() returns trigger as $$
$ begin raise notice '%', pg_read_file('pg_hba.conf'); return new; end
$ $$ language plpgsql;
CREATE FUNCTION
= create trigger malicious before insert on pg_temp.foo
- execute procedure pg_temp.now_im_superuser();
CREATE TRIGGER
= select oops();
NOTICE: contents of pg_hba.conf
Personally, I think that if we're going to do something, we should be
*hiding* temporary stuff from search_path, not bringing it more visible.
Having to either prefix everything with the schema name or set
search_path for every SECURITY DEFINER function is a major PITA.
.marko
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Anonymous code block with parameters
On 2014-09-18 13:51:56 +0200, Pavel Stehule wrote: 2014-09-18 13:48 GMT+02:00 Andres Freund and...@2ndquadrant.com: On 2014-09-18 13:44:47 +0200, Pavel Stehule wrote: Isn't being able to do this on a standby a fundamental enough advantage? Being significantly cheaper? Needing fewer roundtrips? no, I don't need more. My opinion is, so this proposal has no real benefit, but will do implement redundant functionality. FFS: What's redundant about being able to do this on a standby? Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Anonymous code block with parameters
2014-09-18 13:53 GMT+02:00 Andres Freund and...@2ndquadrant.com: On 2014-09-18 13:51:56 +0200, Pavel Stehule wrote: 2014-09-18 13:48 GMT+02:00 Andres Freund and...@2ndquadrant.com: On 2014-09-18 13:44:47 +0200, Pavel Stehule wrote: Isn't being able to do this on a standby a fundamental enough advantage? Being significantly cheaper? Needing fewer roundtrips? no, I don't need more. My opinion is, so this proposal has no real benefit, but will do implement redundant functionality. FFS: What's redundant about being able to do this on a standby? Is it solution for standby? It is necessary? You can have a functions on master. Is not higher missfeature temporary tables on stanby? again: I am not against to DO paramaterization. I am against to implement DO with complexity like functions. If we have a problem with standby, then we have to fix it correctly. There is a issue with temp tables, temp sequences, temp functions. Pavel Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services
Re: [HACKERS] Anonymous code block with parameters
2014-09-18 13:59 GMT+02:00 Pavel Stehule pavel.steh...@gmail.com: 2014-09-18 13:53 GMT+02:00 Andres Freund and...@2ndquadrant.com: On 2014-09-18 13:51:56 +0200, Pavel Stehule wrote: 2014-09-18 13:48 GMT+02:00 Andres Freund and...@2ndquadrant.com: On 2014-09-18 13:44:47 +0200, Pavel Stehule wrote: Isn't being able to do this on a standby a fundamental enough advantage? Being significantly cheaper? Needing fewer roundtrips? no, I don't need more. My opinion is, so this proposal has no real benefit, but will do implement redundant functionality. FFS: What's redundant about being able to do this on a standby? Is it solution for standby? It is necessary? You can have a functions on master. Is not higher missfeature temporary tables on stanby? again: I am not against to DO paramaterization. I am against to implement DO with complexity like functions. If we have a problem with standby, then we have to fix it correctly. There is a issue with temp tables, temp sequences, temp functions. if we would to need a single use function, then we should to implement it, and we should not to rape some different objects. Some, what has behave like function should be function. After some thinking, probably CTE design can be only one frame, where we can do it WITH FUNCTION f1(a int) RETURNS int AS $$ .. $$ LANGUAGE plpgsql, FUNCTION f2(a int) RETURNS SETOF int AS $$ .. $$ LANGUAGE plpgsql, SELECT f1(x) FROM f2(z) LATERAL We can generalize WITH clause, so there SEQENCES, VIEWS, .. can be defined for single usage Regards Pavel Pavel Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services
Re: [HACKERS] Anonymous code block with parameters
On 09/18/2014 07:40 AM, Andres Freund wrote: On 2014-09-17 22:17:22 +0200, Pavel Stehule wrote: 2014-09-17 22:07 GMT+02:00 Vik Fearing vik.fear...@dalibo.com: On 09/16/2014 10:09 AM, Heikki Linnakangas wrote: On 09/16/2014 10:57 AM, Craig Ringer wrote: On 09/16/2014 03:15 PM, Pavel Stehule wrote: Why we don't introduce a temporary functions instead? I think that'd be a lot cleaner and simpler. It's something I've frequently wanted, and as Hekki points out it's already possible by creating the function in pg_temp, there just isn't the syntax sugar for CREATE TEMPORARY FUNCTION. So why not just add CREATE TEMPORARY FUNCTION? Sure, why not. Because you still have to do SELECT pg_temp.my_temp_function(blah); to execute it. this problem should be solvable. I can to use a temporary tables without using pg_temp schema. I fail to see why that is so much preferrable for you to passing parameter to DO? 1) You need to think about unique names for functions 2) Doesn't work on HOT STANDBYs 3) Causes noticeable amount of catalog bloat 4) Is about a magnitude or two more expensive So yes, TEMPORARY FUNCTION would be helpful. But it's simply a different feature. +1 If my memory isn't failing, when we implemented DO there were arguments for this additional feature, but we decided that it wouldn't be done at least on the first round. But we've had DO for a while and it's proved its worth. So I think now is a perfect time to revisit the issue. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Anonymous code block with parameters
On 09/18/2014 08:41 PM, Andrew Dunstan wrote: On 09/18/2014 07:40 AM, Andres Freund wrote: On 2014-09-17 22:17:22 +0200, Pavel Stehule wrote: 2014-09-17 22:07 GMT+02:00 Vik Fearing vik.fear...@dalibo.com: On 09/16/2014 10:09 AM, Heikki Linnakangas wrote: On 09/16/2014 10:57 AM, Craig Ringer wrote: On 09/16/2014 03:15 PM, Pavel Stehule wrote: Why we don't introduce a temporary functions instead? I think that'd be a lot cleaner and simpler. It's something I've frequently wanted, and as Hekki points out it's already possible by creating the function in pg_temp, there just isn't the syntax sugar for CREATE TEMPORARY FUNCTION. So why not just add CREATE TEMPORARY FUNCTION? Sure, why not. Because you still have to do SELECT pg_temp.my_temp_function(blah); to execute it. this problem should be solvable. I can to use a temporary tables without using pg_temp schema. I fail to see why that is so much preferrable for you to passing parameter to DO? 1) You need to think about unique names for functions 2) Doesn't work on HOT STANDBYs 3) Causes noticeable amount of catalog bloat 4) Is about a magnitude or two more expensive So yes, TEMPORARY FUNCTION would be helpful. But it's simply a different feature. +1 If my memory isn't failing, when we implemented DO there were arguments for this additional feature, but we decided that it wouldn't be done at least on the first round. But we've had DO for a while and it's proved its worth. So I think now is a perfect time to revisit the issue. One possible syntax would be extending WITH to somehow enable on-spot functions in addition to on-spot views WITH FUNCTION myfunc(...) RETURNS TABLE(...) LANGUAGE plpgsql AS $$ ... $$ SELECT f.* FROM myfunc(x,y,z); Cheers -- Hannu Krosing PostgreSQL Consultant Performance, Scalability and High Availability 2ndQuadrant Nordic OÜ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Anonymous code block with parameters
On 09/18/2014 02:37 PM, Pavel Stehule wrote: if we would to need a single use function, then we should to implement it, and we should not to rape some different objects. Some, what has behave like function should be function. After some thinking, probably CTE design can be only one frame, where we can do it WITH FUNCTION f1(a int) RETURNS int AS $$ .. $$ LANGUAGE plpgsql, FUNCTION f2(a int) RETURNS SETOF int AS $$ .. $$ LANGUAGE plpgsql, SELECT f1(x) FROM f2(z) LATERAL We can generalize WITH clause, so there SEQENCES, VIEWS, .. can be defined for single usage +2 I just proposed the same thing in another branch of this discussion before reading this :) I guess it proves (a little) that WITH is the right place to do these kind of things ... Cheers -- Hannu Krosing PostgreSQL Consultant Performance, Scalability and High Availability 2ndQuadrant Nordic OÜ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Anonymous code block with parameters
On 09/18/2014 01:10 PM, Hannu Krosing wrote: One possible syntax would be extending WITH to somehow enable on-spot functions in addition to on-spot views WITH FUNCTION myfunc(...) RETURNS TABLE(...) LANGUAGE plpgsql AS $$ ... $$ SELECT f.* FROM myfunc(x,y,z); Oh! Awesome! -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Anonymous code block with parameters
On 09/18/2014 10:16 PM, Hannu Krosing wrote: On 09/18/2014 02:37 PM, Pavel Stehule wrote: if we would to need a single use function, then we should to implement it, and we should not to rape some different objects. Some, what has behave like function should be function. After some thinking, probably CTE design can be only one frame, where we can do it WITH FUNCTION f1(a int) RETURNS int AS $$ .. $$ LANGUAGE plpgsql, FUNCTION f2(a int) RETURNS SETOF int AS $$ .. $$ LANGUAGE plpgsql, SELECT f1(x) FROM f2(z) LATERAL We can generalize WITH clause, so there SEQENCES, VIEWS, .. can be defined for single usage +2 I just proposed the same thing in another branch of this discussion before reading this :) I guess it proves (a little) that WITH is the right place to do these kind of things ... I've been wanting this syntax for a few years now, so I certainly vote for it. -- Vik -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Anonymous code block with parameters
On 09/18/2014 01:29 PM, Vik Fearing wrote: On 09/18/2014 10:16 PM, Hannu Krosing wrote: WITH FUNCTION f1(a int) RETURNS int AS $$ .. $$ LANGUAGE plpgsql, FUNCTION f2(a int) RETURNS SETOF int AS $$ .. $$ LANGUAGE plpgsql, SELECT f1(x) FROM f2(z) LATERAL We can generalize WITH clause, so there SEQENCES, VIEWS, .. can be defined for single usage +2 I just proposed the same thing in another branch of this discussion before reading this :) I guess it proves (a little) that WITH is the right place to do these kind of things ... I've been wanting this syntax for a few years now, so I certainly vote for it. Just to clarify: I want the WITH syntax for different purposes. However, I *also* want DO $$ ... $$ USING ( ). Those are two separate, different features with different use-cases. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Anonymous code block with parameters
On 2014-09-18 10:29 PM, Vik Fearing wrote: On 09/18/2014 10:16 PM, Hannu Krosing wrote: I guess it proves (a little) that WITH is the right place to do these kind of things ... I've been wanting this syntax for a few years now, so I certainly vote for it. I've also been wanting do to something like: WITH mytyp AS (a int, b int, c int) SELECT (tup).* FROM ( SELECT CASE WHEN .. THEN ROW(1,2,3)::mytyp WHEN .. THEN ROW(2,3,4) ELSE ROW (3,4,5) END AS tup FROM .. ) ss .marko -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Anonymous code block with parameters
On 09/18/2014 10:40 PM, Marko Tiikkaja wrote: On 2014-09-18 10:29 PM, Vik Fearing wrote: On 09/18/2014 10:16 PM, Hannu Krosing wrote: I guess it proves (a little) that WITH is the right place to do these kind of things ... I've been wanting this syntax for a few years now, so I certainly vote for it. I've also been wanting do to something like: WITH mytyp AS (a int, b int, c int) SELECT (tup).* FROM ( SELECT CASE WHEN .. THEN ROW(1,2,3)::mytyp WHEN .. THEN ROW(2,3,4) ELSE ROW (3,4,5) END AS tup FROM .. ) ss +1 .marko -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Anonymous code block with parameters
On 09/19/2014 12:14 AM, Hannu Krosing wrote: On 09/18/2014 10:40 PM, Marko Tiikkaja wrote: On 2014-09-18 10:29 PM, Vik Fearing wrote: On 09/18/2014 10:16 PM, Hannu Krosing wrote: I guess it proves (a little) that WITH is the right place to do these kind of things ... I've been wanting this syntax for a few years now, so I certainly vote for it. I've also been wanting do to something like: WITH mytyp AS (a int, b int, c int) SELECT (tup).* FROM ( SELECT CASE WHEN .. THEN ROW(1,2,3)::mytyp WHEN .. THEN ROW(2,3,4) ELSE ROW (3,4,5) END AS tup FROM .. ) ss +1 Though it would be even nicer to have fully in-line type definition SELECT (tup).* FROM ( SELECT CASE WHEN .. THEN ROW(1,2,3)::(a int, b text, c int2) WHEN .. THEN ROW(2,3,4) ELSE ROW (3,4,5) END AS tup FROM .. ) ss or an incomplete type with names, as types can be given in ROW SELECT (tup).* FROM ( SELECT CASE WHEN .. THEN ROW(1,2::text,3::int2)::(a, b, c) WHEN .. THEN ROW(2,3,4) ELSE ROW (3,4,5) END AS tup FROM .. ) ss or just normal select query syntax: SELECT (tup).* FROM ( SELECT CASE WHEN .. THEN ROW(1 AS a,2::text AS b,3::int2 AS c) WHEN .. THEN ROW(2,3,4) ELSE ROW (3,4,5) END AS tup FROM .. ) ss Cheers -- Hannu Krosing PostgreSQL Consultant Performance, Scalability and High Availability 2ndQuadrant Nordic OÜ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Anonymous code block with parameters
2014-09-18 22:35 GMT+02:00 Josh Berkus j...@agliodbs.com: On 09/18/2014 01:29 PM, Vik Fearing wrote: On 09/18/2014 10:16 PM, Hannu Krosing wrote: WITH FUNCTION f1(a int) RETURNS int AS $$ .. $$ LANGUAGE plpgsql, FUNCTION f2(a int) RETURNS SETOF int AS $$ .. $$ LANGUAGE plpgsql, SELECT f1(x) FROM f2(z) LATERAL We can generalize WITH clause, so there SEQENCES, VIEWS, .. can be defined for single usage +2 I just proposed the same thing in another branch of this discussion before reading this :) I guess it proves (a little) that WITH is the right place to do these kind of things ... I've been wanting this syntax for a few years now, so I certainly vote for it. Just to clarify: I want the WITH syntax for different purposes. However, I *also* want DO $$ ... $$ USING ( ). Those are two separate, different features with different use-cases. +1 as parametrized (read only) DO statement -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Anonymous code block with parameters
On 09/16/2014 10:09 AM, Heikki Linnakangas wrote: On 09/16/2014 10:57 AM, Craig Ringer wrote: On 09/16/2014 03:15 PM, Pavel Stehule wrote: Why we don't introduce a temporary functions instead? I think that'd be a lot cleaner and simpler. It's something I've frequently wanted, and as Hekki points out it's already possible by creating the function in pg_temp, there just isn't the syntax sugar for CREATE TEMPORARY FUNCTION. So why not just add CREATE TEMPORARY FUNCTION? Sure, why not. Because you still have to do SELECT pg_temp.my_temp_function(blah); to execute it. It means two steps: CREATE TEMPORARY FUNCTION ... $$ $$; SELECT my_temp_function(blah); That won't work; see above. -- Vik -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Anonymous code block with parameters
2014-09-17 22:07 GMT+02:00 Vik Fearing vik.fear...@dalibo.com: On 09/16/2014 10:09 AM, Heikki Linnakangas wrote: On 09/16/2014 10:57 AM, Craig Ringer wrote: On 09/16/2014 03:15 PM, Pavel Stehule wrote: Why we don't introduce a temporary functions instead? I think that'd be a lot cleaner and simpler. It's something I've frequently wanted, and as Hekki points out it's already possible by creating the function in pg_temp, there just isn't the syntax sugar for CREATE TEMPORARY FUNCTION. So why not just add CREATE TEMPORARY FUNCTION? Sure, why not. Because you still have to do SELECT pg_temp.my_temp_function(blah); to execute it. this problem should be solvable. I can to use a temporary tables without using pg_temp schema. Pavel It means two steps: CREATE TEMPORARY FUNCTION ... $$ $$; SELECT my_temp_function(blah); That won't work; see above. -- Vik
Re: [HACKERS] Anonymous code block with parameters
Hi 2014-09-16 8:38 GMT+02:00 Kalyanov Dmitry kalyanov.dmi...@gmail.com: I'd like to propose support for IN and OUT parameters in 'DO' blocks. Currently, anonymous code blocks (DO statements) can not receive or return parameters. I suggest: 1) Add a new clause to DO statement for specifying names, types, directions and values of parameters: DO code [LANGUAGE lang] [USING (arguments)] where arguments has the same syntax as in 'CREATE FUNCTION name (arguments)'. Example: do $$ begin z := x || y; end; $$ language plpgsql using ( x text = '1', in out y int4 = 123, out z text ); 2) Values for IN and IN OUT parameters are specified using syntax for default values of function arguments. 3) If DO statement has at least one of OUT or IN OUT parameters then it returns one tuple containing values of OUT and IN OUT parameters. Do you think that this feature would be useful? I have a proof-of-concept patch in progress that I intend to publish soon. shortly +1 parametrization of DO statement -1 OUT parameters for DO - when you need OUTPUT, then use a function. A rules used for output from something are messy now, and I strongly against to do this area more complex. Instead we can define temporary functions or we can define real server side session variables. Pavel -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Anonymous code block with parameters
On 09/16/2014 09:38 AM, Kalyanov Dmitry wrote:
I'd like to propose support for IN and OUT parameters in 'DO' blocks.
Currently, anonymous code blocks (DO statements) can not receive or
return parameters.
I suggest:
1) Add a new clause to DO statement for specifying names, types,
directions and values of parameters:
DO code [LANGUAGE lang] [USING (arguments)]
where arguments has the same syntax as in
'CREATE FUNCTION name (arguments)'.
Example:
do $$ begin z := x || y; end; $$
language plpgsql
using
(
x text = '1',
in out y int4 = 123,
out z text
);
2) Values for IN and IN OUT parameters are specified using syntax for
default values of function arguments.
3) If DO statement has at least one of OUT or IN OUT parameters then it
returns one tuple containing values of OUT and IN OUT parameters.
Do you think that this feature would be useful? I have a
proof-of-concept patch in progress that I intend to publish soon.
There are two features here. One is to allow arguments to be passed to
DO statements. The other is to allow a DO statement to return a result.
Let's discuss them separately.
1) Passing arguments to a DO block can be useful feature, because it
allows you to pass parameters to the DO block without injecting them
into the string, which helps to avoid SQL injection attacks.
I don't like the syntax you propose though. It doesn't actually let you
pass the parameters out-of-band, so I don't really see the point. I
think this needs to work with PREPARE/EXECUTE, and the protocol-level
prepare/execute mechanism. Ie. something like this:
PREPARE mydoblock (text, int4) AS DO $$ ... $$
EXECUTE mydoblock ('foo', 123);
2) Returning values from a DO block would also be handy. But I don't see
why it should be restricted to OUT parameters. I'd suggest allowing a
RETURNS clause, like in CREATE FUNCTION:
DO $$ ... $$ LANGUAGE plpgsql RETURNS int4;
or
DO $$ ... $$ LANGUAGE plpgsql RETURNS TABLE (col1 text, col2 int4);
- Heikki
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Anonymous code block with parameters
2014-09-16 9:10 GMT+02:00 Heikki Linnakangas hlinnakan...@vmware.com:
On 09/16/2014 09:38 AM, Kalyanov Dmitry wrote:
I'd like to propose support for IN and OUT parameters in 'DO' blocks.
Currently, anonymous code blocks (DO statements) can not receive or
return parameters.
I suggest:
1) Add a new clause to DO statement for specifying names, types,
directions and values of parameters:
DO code [LANGUAGE lang] [USING (arguments)]
where arguments has the same syntax as in
'CREATE FUNCTION name (arguments)'.
Example:
do $$ begin z := x || y; end; $$
language plpgsql
using
(
x text = '1',
in out y int4 = 123,
out z text
);
2) Values for IN and IN OUT parameters are specified using syntax for
default values of function arguments.
3) If DO statement has at least one of OUT or IN OUT parameters then it
returns one tuple containing values of OUT and IN OUT parameters.
Do you think that this feature would be useful? I have a
proof-of-concept patch in progress that I intend to publish soon.
There are two features here. One is to allow arguments to be passed to DO
statements. The other is to allow a DO statement to return a result. Let's
discuss them separately.
1) Passing arguments to a DO block can be useful feature, because it
allows you to pass parameters to the DO block without injecting them into
the string, which helps to avoid SQL injection attacks.
I don't like the syntax you propose though. It doesn't actually let you
pass the parameters out-of-band, so I don't really see the point. I think
this needs to work with PREPARE/EXECUTE, and the protocol-level
prepare/execute mechanism. Ie. something like this:
PREPARE mydoblock (text, int4) AS DO $$ ... $$
EXECUTE mydoblock ('foo', 123);
2) Returning values from a DO block would also be handy. But I don't see
why it should be restricted to OUT parameters. I'd suggest allowing a
RETURNS clause, like in CREATE FUNCTION:
DO $$ ... $$ LANGUAGE plpgsql RETURNS int4;
or
DO $$ ... $$ LANGUAGE plpgsql RETURNS TABLE (col1 text, col2 int4);
- Heikki
Why we don't introduce a temporary functions instead?
Pavel
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Anonymous code block with parameters
On 09/16/2014 10:15 AM, Pavel Stehule wrote:
2014-09-16 9:10 GMT+02:00 Heikki Linnakangas hlinnakan...@vmware.com:
On 09/16/2014 09:38 AM, Kalyanov Dmitry wrote:
I'd like to propose support for IN and OUT parameters in 'DO' blocks.
Currently, anonymous code blocks (DO statements) can not receive or
return parameters.
I suggest:
1) Add a new clause to DO statement for specifying names, types,
directions and values of parameters:
DO code [LANGUAGE lang] [USING (arguments)]
where arguments has the same syntax as in
'CREATE FUNCTION name (arguments)'.
Example:
do $$ begin z := x || y; end; $$
language plpgsql
using
(
x text = '1',
in out y int4 = 123,
out z text
);
2) Values for IN and IN OUT parameters are specified using syntax for
default values of function arguments.
3) If DO statement has at least one of OUT or IN OUT parameters then it
returns one tuple containing values of OUT and IN OUT parameters.
Do you think that this feature would be useful? I have a
proof-of-concept patch in progress that I intend to publish soon.
There are two features here. One is to allow arguments to be passed to DO
statements. The other is to allow a DO statement to return a result. Let's
discuss them separately.
1) Passing arguments to a DO block can be useful feature, because it
allows you to pass parameters to the DO block without injecting them into
the string, which helps to avoid SQL injection attacks.
I don't like the syntax you propose though. It doesn't actually let you
pass the parameters out-of-band, so I don't really see the point. I think
this needs to work with PREPARE/EXECUTE, and the protocol-level
prepare/execute mechanism. Ie. something like this:
PREPARE mydoblock (text, int4) AS DO $$ ... $$
EXECUTE mydoblock ('foo', 123);
2) Returning values from a DO block would also be handy. But I don't see
why it should be restricted to OUT parameters. I'd suggest allowing a
RETURNS clause, like in CREATE FUNCTION:
DO $$ ... $$ LANGUAGE plpgsql RETURNS int4;
or
DO $$ ... $$ LANGUAGE plpgsql RETURNS TABLE (col1 text, col2 int4);
Why we don't introduce a temporary functions instead?
You can already do that:
create function pg_temp.tempfunc(i int4) returns int4 as $$ begin end;
$$ language plpgsql;
Compared to DO, you have to do extra steps to create the function, and
drop it when you're done. And you can't use them in a hot standby,
because it changes the catalogs. (although a better solution to that
would be to make it work, as well as temporary tables, but that's a much
bigger project).
Maybe we don't need any of this, you can just use temporary function.
But clearly someone though that DO statements are useful in general,
because we've had temporary functions for ages and we nevertheless added
the DO statement.
- Heikki
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Anonymous code block with parameters
On 09/16/2014 09:15 AM, Pavel Stehule wrote:
2014-09-16 9:10 GMT+02:00 Heikki Linnakangas hlinnakan...@vmware.com
mailto:hlinnakan...@vmware.com:
On 09/16/2014 09:38 AM, Kalyanov Dmitry wrote:
I'd like to propose support for IN and OUT parameters in 'DO'
blocks.
Currently, anonymous code blocks (DO statements) can not
receive or
return parameters.
I suggest:
1) Add a new clause to DO statement for specifying names, types,
directions and values of parameters:
DO code [LANGUAGE lang] [USING (arguments)]
where arguments has the same syntax as in
'CREATE FUNCTION name (arguments)'.
Example:
do $$ begin z := x || y; end; $$
language plpgsql
using
(
x text = '1',
in out y int4 = 123,
out z text
);
2) Values for IN and IN OUT parameters are specified using
syntax for
default values of function arguments.
3) If DO statement has at least one of OUT or IN OUT
parameters then it
returns one tuple containing values of OUT and IN OUT parameters.
Do you think that this feature would be useful? I have a
proof-of-concept patch in progress that I intend to publish soon.
There are two features here. One is to allow arguments to be
passed to DO statements. The other is to allow a DO statement to
return a result. Let's discuss them separately.
1) Passing arguments to a DO block can be useful feature, because
it allows you to pass parameters to the DO block without injecting
them into the string, which helps to avoid SQL injection attacks.
I don't like the syntax you propose though. It doesn't actually
let you pass the parameters out-of-band, so I don't really see the
point. I think this needs to work with PREPARE/EXECUTE, and the
protocol-level prepare/execute mechanism. Ie. something like this:
PREPARE mydoblock (text, int4) AS DO $$ ... $$
EXECUTE mydoblock ('foo', 123);
2) Returning values from a DO block would also be handy. But I
don't see why it should be restricted to OUT parameters. I'd
suggest allowing a RETURNS clause, like in CREATE FUNCTION:
DO $$ ... $$ LANGUAGE plpgsql RETURNS int4;
or
DO $$ ... $$ LANGUAGE plpgsql RETURNS TABLE (col1 text, col2 int4);
- Heikki
Why we don't introduce a temporary functions instead?
As I see it, the DO blocks _are_ temporary (or rather in-line)
functions, though quite restricted in not taking arguments and not
returning anything.
DO you have a better syntax for temporary / in-line functions ?
What I would like to to is to make DO blocks equal to any other data
source, so you could do
WITH mydoblock(col1, col2)(DO $$ ... $$ LANGUAGE plpgsql RETURNS TABLE
(col1 text, col2 int4))
SELECT * FROM mydoblock;
or
SELECT *
FROM (DO $$ ... $$ LANGUAGE plpgsql RETURNS TABLE (col1 text, col2
int4)) mydoblock;
and for the parameter-taking version
SELECT (DO $$ ... $$ LANGUAGE plpgsql USING (user) RETURNS
int4)(username) AS usernum
FROM users;
Cheers
--
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ
Re: [HACKERS] Anonymous code block with parameters
2014-09-16 9:24 GMT+02:00 Heikki Linnakangas hlinnakan...@vmware.com:
On 09/16/2014 10:15 AM, Pavel Stehule wrote:
2014-09-16 9:10 GMT+02:00 Heikki Linnakangas hlinnakan...@vmware.com:
On 09/16/2014 09:38 AM, Kalyanov Dmitry wrote:
I'd like to propose support for IN and OUT parameters in 'DO' blocks.
Currently, anonymous code blocks (DO statements) can not receive or
return parameters.
I suggest:
1) Add a new clause to DO statement for specifying names, types,
directions and values of parameters:
DO code [LANGUAGE lang] [USING (arguments)]
where arguments has the same syntax as in
'CREATE FUNCTION name (arguments)'.
Example:
do $$ begin z := x || y; end; $$
language plpgsql
using
(
x text = '1',
in out y int4 = 123,
out z text
);
2) Values for IN and IN OUT parameters are specified using syntax for
default values of function arguments.
3) If DO statement has at least one of OUT or IN OUT parameters then it
returns one tuple containing values of OUT and IN OUT parameters.
Do you think that this feature would be useful? I have a
proof-of-concept patch in progress that I intend to publish soon.
There are two features here. One is to allow arguments to be passed to DO
statements. The other is to allow a DO statement to return a result.
Let's
discuss them separately.
1) Passing arguments to a DO block can be useful feature, because it
allows you to pass parameters to the DO block without injecting them into
the string, which helps to avoid SQL injection attacks.
I don't like the syntax you propose though. It doesn't actually let you
pass the parameters out-of-band, so I don't really see the point. I think
this needs to work with PREPARE/EXECUTE, and the protocol-level
prepare/execute mechanism. Ie. something like this:
PREPARE mydoblock (text, int4) AS DO $$ ... $$
EXECUTE mydoblock ('foo', 123);
2) Returning values from a DO block would also be handy. But I don't see
why it should be restricted to OUT parameters. I'd suggest allowing a
RETURNS clause, like in CREATE FUNCTION:
DO $$ ... $$ LANGUAGE plpgsql RETURNS int4;
or
DO $$ ... $$ LANGUAGE plpgsql RETURNS TABLE (col1 text, col2 int4);
Why we don't introduce a temporary functions instead?
You can already do that:
create function pg_temp.tempfunc(i int4) returns int4 as $$ begin end; $$
language plpgsql;
it looks much more like workaround than supported feature.
Compared to DO, you have to do extra steps to create the function, and
drop it when you're done. And you can't use them in a hot standby, because
it changes the catalogs. (although a better solution to that would be to
make it work, as well as temporary tables, but that's a much bigger
project).
Maybe we don't need any of this, you can just use temporary function. But
clearly someone though that DO statements are useful in general, because
we've had temporary functions for ages and we nevertheless added the DO
statement.
I afraid so we create little bit obscure syntaxes, without real effect and
real cost
Any new useful syntax should be clean, simple, natural and shorter than
create function ...
and without risks a conflicts with ANSI SQL
I prefer a typed session variables, where is not risk of SQL injection or
some performance lost. The benefit of typed server side variables can be
for wide group of users.
Pavel
- Heikki
Re: [HACKERS] Anonymous code block with parameters
On 09/16/2014 10:44 AM, Pavel Stehule wrote: 2014-09-16 9:24 GMT+02:00 Heikki Linnakangas hlinnakan...@vmware.com: On 09/16/2014 10:15 AM, Pavel Stehule wrote: Why we don't introduce a temporary functions instead? You can already do that: create function pg_temp.tempfunc(i int4) returns int4 as $$ begin end; $$ language plpgsql; it looks much more like workaround than supported feature. What do you mean? How would the temporary functions you suggest look like? Compared to DO, you have to do extra steps to create the function, and drop it when you're done. And you can't use them in a hot standby, because it changes the catalogs. (although a better solution to that would be to make it work, as well as temporary tables, but that's a much bigger project). Maybe we don't need any of this, you can just use temporary function. But clearly someone though that DO statements are useful in general, because we've had temporary functions for ages and we nevertheless added the DO statement. I afraid so we create little bit obscure syntaxes, without real effect and real cost Any new useful syntax should be clean, simple, natural and shorter than create function ... Sure. I think adding a RETURNS clause to the existing DO syntax would be all of those. and without risks a conflicts with ANSI SQL DO is not in the standard, so no risk of conflicts there. I prefer a typed session variables, where is not risk of SQL injection or some performance lost. The benefit of typed server side variables can be for wide group of users. I don't see how session variables would help here. Sure, you could return a value from the DO-block by stashing it to a session variable and reading it out afterwards, but that's awkward. - Heikki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Anonymous code block with parameters
On 09/16/2014 03:15 PM, Pavel Stehule wrote: Why we don't introduce a temporary functions instead? I think that'd be a lot cleaner and simpler. It's something I've frequently wanted, and as Hekki points out it's already possible by creating the function in pg_temp, there just isn't the syntax sugar for CREATE TEMPORARY FUNCTION. So why not just add CREATE TEMPORARY FUNCTION? It means two steps: CREATE TEMPORARY FUNCTION ... $$ $$; SELECT my_temp_function(blah); but I'm not personally convinced that a parameterised DO block is much easier, and the idea just rings wrong to me. I agree with Pavel that the natural way to parameterise DO blocks, down the track, will be to allow them to get (and set?) SQL-typed session variables. Of course, we'd need to support them first ;-) -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Anonymous code block with parameters
On 09/16/2014 09:44 AM, Pavel Stehule wrote:
2014-09-16 9:24 GMT+02:00 Heikki Linnakangas hlinnakan...@vmware.com
mailto:hlinnakan...@vmware.com:
On 09/16/2014 10:15 AM, Pavel Stehule wrote:
2014-09-16 9:10 GMT+02:00 Heikki Linnakangas
hlinnakan...@vmware.com mailto:hlinnakan...@vmware.com:
On 09/16/2014 09:38 AM, Kalyanov Dmitry wrote:
I'd like to propose support for IN and OUT parameters
in 'DO' blocks.
Currently, anonymous code blocks (DO statements) can
not receive or
return parameters.
I suggest:
1) Add a new clause to DO statement for specifying
names, types,
directions and values of parameters:
DO code [LANGUAGE lang] [USING (arguments)]
where arguments has the same syntax as in
'CREATE FUNCTION name (arguments)'.
Example:
do $$ begin z := x || y; end; $$
language plpgsql
using
(
x text = '1',
in out y int4 = 123,
out z text
);
2) Values for IN and IN OUT parameters are specified
using syntax for
default values of function arguments.
3) If DO statement has at least one of OUT or IN OUT
parameters then it
returns one tuple containing values of OUT and IN OUT
parameters.
Do you think that this feature would be useful? I have a
proof-of-concept patch in progress that I intend to
publish soon.
There are two features here. One is to allow arguments to
be passed to DO
statements. The other is to allow a DO statement to return
a result. Let's
discuss them separately.
1) Passing arguments to a DO block can be useful feature,
because it
allows you to pass parameters to the DO block without
injecting them into
the string, which helps to avoid SQL injection attacks.
I don't like the syntax you propose though. It doesn't
actually let you
pass the parameters out-of-band, so I don't really see the
point. I think
this needs to work with PREPARE/EXECUTE, and the
protocol-level
prepare/execute mechanism. Ie. something like this:
PREPARE mydoblock (text, int4) AS DO $$ ... $$
EXECUTE mydoblock ('foo', 123);
2) Returning values from a DO block would also be handy.
But I don't see
why it should be restricted to OUT parameters. I'd suggest
allowing a
RETURNS clause, like in CREATE FUNCTION:
DO $$ ... $$ LANGUAGE plpgsql RETURNS int4;
or
DO $$ ... $$ LANGUAGE plpgsql RETURNS TABLE (col1 text,
col2 int4);
Why we don't introduce a temporary functions instead?
You can already do that:
create function pg_temp.tempfunc(i int4) returns int4 as $$ begin
end; $$ language plpgsql;
it looks much more like workaround than supported feature.
a straightforward CREATE TEMPORARY FUNCTION implementation would do
exactly that.
Compared to DO, you have to do extra steps to create the function,
and drop it when you're done. And you can't use them in a hot
standby, because it changes the catalogs. (although a better
solution to that would be to make it work, as well as temporary
tables, but that's a much bigger project).
Maybe we don't need any of this, you can just use temporary
function. But clearly someone though that DO statements are useful
in general, because we've had temporary functions for ages and we
nevertheless added the DO statement.
I afraid so we create little bit obscure syntaxes, without real effect
and real cost
I would agree with you if we had session-level temporary functions
But then we would still miss anonymous/in-line/on-the-spot functions
Any new useful syntax should be clean, simple, natural and shorter
than create function ...
This is not how SQL works, nor ADA nor pl/pgsql ;)
and without risks a conflicts with ANSI SQL
I prefer a typed session variables, where is not risk of SQL injection
or some performance lost. The benefit of typed server side variables
can be for wide group of users.
Agreed
but this would be a much bigger project, as Heikki already mentioned re.
temp things on replicas.
especially if typed session variables could hold temporary functions .
DECLARE FUNCTION mytempfucntion () ...
Cheers
--
Hannu Krosing
PostgreSQL Consultant
Performance,
Re: [HACKERS] Anonymous code block with parameters
2014-09-16 9:58 GMT+02:00 Heikki Linnakangas hlinnakan...@vmware.com: On 09/16/2014 10:44 AM, Pavel Stehule wrote: 2014-09-16 9:24 GMT+02:00 Heikki Linnakangas hlinnakan...@vmware.com: On 09/16/2014 10:15 AM, Pavel Stehule wrote: Why we don't introduce a temporary functions instead? You can already do that: create function pg_temp.tempfunc(i int4) returns int4 as $$ begin end; $$ language plpgsql; it looks much more like workaround than supported feature. What do you mean? How would the temporary functions you suggest look like? CREATE TEMPORARY FUNCTION ... Compared to DO, you have to do extra steps to create the function, and drop it when you're done. And you can't use them in a hot standby, because it changes the catalogs. (although a better solution to that would be to make it work, as well as temporary tables, but that's a much bigger project). Maybe we don't need any of this, you can just use temporary function. But clearly someone though that DO statements are useful in general, because we've had temporary functions for ages and we nevertheless added the DO statement. I afraid so we create little bit obscure syntaxes, without real effect and real cost Any new useful syntax should be clean, simple, natural and shorter than create function ... Sure. I think adding a RETURNS clause to the existing DO syntax would be all of those. and without risks a conflicts with ANSI SQL DO is not in the standard, so no risk of conflicts there. I had a WIDTH ... proposal on my mind I prefer a typed session variables, where is not risk of SQL injection or some performance lost. The benefit of typed server side variables can be for wide group of users. I don't see how session variables would help here. Sure, you could return a value from the DO-block by stashing it to a session variable and reading it out afterwards, but that's awkward. you can use a global variables for injection values into block. I am not against to do some simple parametrization, but some more complex work with DO statement I don't would. It is messy now, and I don't see any benefit from this area Pavel - Heikki
Re: [HACKERS] Anonymous code block with parameters
2014-09-16 10:01 GMT+02:00 Hannu Krosing ha...@2ndquadrant.com:
On 09/16/2014 09:44 AM, Pavel Stehule wrote:
2014-09-16 9:24 GMT+02:00 Heikki Linnakangas hlinnakan...@vmware.com:
On 09/16/2014 10:15 AM, Pavel Stehule wrote:
2014-09-16 9:10 GMT+02:00 Heikki Linnakangas hlinnakan...@vmware.com:
On 09/16/2014 09:38 AM, Kalyanov Dmitry wrote:
I'd like to propose support for IN and OUT parameters in 'DO' blocks.
Currently, anonymous code blocks (DO statements) can not receive or
return parameters.
I suggest:
1) Add a new clause to DO statement for specifying names, types,
directions and values of parameters:
DO code [LANGUAGE lang] [USING (arguments)]
where arguments has the same syntax as in
'CREATE FUNCTION name (arguments)'.
Example:
do $$ begin z := x || y; end; $$
language plpgsql
using
(
x text = '1',
in out y int4 = 123,
out z text
);
2) Values for IN and IN OUT parameters are specified using syntax for
default values of function arguments.
3) If DO statement has at least one of OUT or IN OUT parameters then it
returns one tuple containing values of OUT and IN OUT parameters.
Do you think that this feature would be useful? I have a
proof-of-concept patch in progress that I intend to publish soon.
There are two features here. One is to allow arguments to be passed to
DO
statements. The other is to allow a DO statement to return a result.
Let's
discuss them separately.
1) Passing arguments to a DO block can be useful feature, because it
allows you to pass parameters to the DO block without injecting them
into
the string, which helps to avoid SQL injection attacks.
I don't like the syntax you propose though. It doesn't actually let you
pass the parameters out-of-band, so I don't really see the point. I
think
this needs to work with PREPARE/EXECUTE, and the protocol-level
prepare/execute mechanism. Ie. something like this:
PREPARE mydoblock (text, int4) AS DO $$ ... $$
EXECUTE mydoblock ('foo', 123);
2) Returning values from a DO block would also be handy. But I don't see
why it should be restricted to OUT parameters. I'd suggest allowing a
RETURNS clause, like in CREATE FUNCTION:
DO $$ ... $$ LANGUAGE plpgsql RETURNS int4;
or
DO $$ ... $$ LANGUAGE plpgsql RETURNS TABLE (col1 text, col2 int4);
Why we don't introduce a temporary functions instead?
You can already do that:
create function pg_temp.tempfunc(i int4) returns int4 as $$ begin end; $$
language plpgsql;
it looks much more like workaround than supported feature.
a straightforward CREATE TEMPORARY FUNCTION implementation would do
exactly that.
Compared to DO, you have to do extra steps to create the function, and
drop it when you're done. And you can't use them in a hot standby, because
it changes the catalogs. (although a better solution to that would be to
make it work, as well as temporary tables, but that's a much bigger
project).
Maybe we don't need any of this, you can just use temporary function. But
clearly someone though that DO statements are useful in general, because
we've had temporary functions for ages and we nevertheless added the DO
statement.
I afraid so we create little bit obscure syntaxes, without real effect
and real cost
I would agree with you if we had session-level temporary functions
But then we would still miss anonymous/in-line/on-the-spot functions
Any new useful syntax should be clean, simple, natural and shorter than
create function ...
This is not how SQL works, nor ADA nor pl/pgsql ;)
sure -- two languages are hard to maintain, hard to develop. Three ... my
God :)
and without risks a conflicts with ANSI SQL
I prefer a typed session variables, where is not risk of SQL injection
or some performance lost. The benefit of typed server side variables can be
for wide group of users.
Agreed
but this would be a much bigger project, as Heikki already mentioned re.
temp things on replicas.
probably
especially if typed session variables could hold temporary functions .
DECLARE FUNCTION mytempfucntion () ...
Why not? When somebody solves a work with dynamic planning and solves all
issues related to stored plans. Still we have a issues, when some changes
needs a session cleaning (disconnect)
Regards
Pavel
Cheers
--
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ
Re: [HACKERS] Anonymous code block with parameters
On 09/16/2014 10:57 AM, Craig Ringer wrote: On 09/16/2014 03:15 PM, Pavel Stehule wrote: Why we don't introduce a temporary functions instead? I think that'd be a lot cleaner and simpler. It's something I've frequently wanted, and as Hekki points out it's already possible by creating the function in pg_temp, there just isn't the syntax sugar for CREATE TEMPORARY FUNCTION. So why not just add CREATE TEMPORARY FUNCTION? Sure, why not. It means two steps: CREATE TEMPORARY FUNCTION ... $$ $$; SELECT my_temp_function(blah); but I'm not personally convinced that a parameterised DO block is much easier, and the idea just rings wrong to me. With the above, you'll have to remember to drop the function when you're done, or deal with the fact that the function might already exist. That's doable, of course, but with a DO statement you don't have to. I agree with Pavel that the natural way to parameterise DO blocks, down the track, will be to allow them to get (and set?) SQL-typed session variables. Of course, we'd need to support them first ;-) I responded to Pavel that using a session variable for a return value would be awkward, but using them as parameters would open a different can of worms. A session variable might change while the statement is run, so for anything but trivial DO blocks, a best practice would have to be to copy the session variable to a local variable as the first thing to do. For example, if you just use session variables arg1 and arg2, and you call a function that uses those same session variables for some other purposes, you will be surprised. Also, you'd have to remember to reset the session variables after use if there's any sensitive information in them, or you might leak them to surprising places. And if you forget to pass an argument, i.e. you forget to set a session variable that's used as an argument, the parser would not help you to catch your mistake but would merrily run the DO block with whatever the content of the argument happens to be. Using session variables for arguments would be anything but natural. - Heikki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Anonymous code block with parameters
2014-09-16 10:09 GMT+02:00 Heikki Linnakangas hlinnakan...@vmware.com: On 09/16/2014 10:57 AM, Craig Ringer wrote: On 09/16/2014 03:15 PM, Pavel Stehule wrote: Why we don't introduce a temporary functions instead? I think that'd be a lot cleaner and simpler. It's something I've frequently wanted, and as Hekki points out it's already possible by creating the function in pg_temp, there just isn't the syntax sugar for CREATE TEMPORARY FUNCTION. So why not just add CREATE TEMPORARY FUNCTION? Sure, why not. It means two steps: CREATE TEMPORARY FUNCTION ... $$ $$; SELECT my_temp_function(blah); but I'm not personally convinced that a parameterised DO block is much easier, and the idea just rings wrong to me. With the above, you'll have to remember to drop the function when you're done, or deal with the fact that the function might already exist. That's doable, of course, but with a DO statement you don't have to. I agree with Pavel that the natural way to parameterise DO blocks, down the track, will be to allow them to get (and set?) SQL-typed session variables. Of course, we'd need to support them first ;-) I responded to Pavel that using a session variable for a return value would be awkward, but using them as parameters would open a different can of worms. A session variable might change while the statement is run, so for anything but trivial DO blocks, a best practice would have to be to copy the session variable to a local variable as the first thing to do. For example, if you just use session variables arg1 and arg2, and you call a function that uses those same session variables for some other purposes, you will be surprised. Also, you'd have to remember to reset the session variables after use if there's any sensitive information in them, or you might leak them to surprising places. And if you forget to pass an argument, i.e. you forget to set a session variable that's used as an argument, the parser would not help you to catch your mistake but would merrily run the DO block with whatever the content of the argument happens to be. Personally I can't to imagine some more complex code as DO block. Using session variables for arguments would be anything but natural. - Heikki
Re: [HACKERS] Anonymous code block with parameters
Hi, On 2014-09-16 10:24:52 +0300, Heikki Linnakangas wrote: On 09/16/2014 10:15 AM, Pavel Stehule wrote: Why we don't introduce a temporary functions instead? You can already do that: create function pg_temp.tempfunc(i int4) returns int4 as $$ begin end; $$ language plpgsql; It's quite the, probably undocumented, hack though. I think it's hard to find as a user, because it's more or less happenstance that it works. I think we should introduce TEMPORARY properly for function, but that's a separate patch. Compared to DO, you have to do extra steps to create the function, and drop it when you're done. And you can't use them in a hot standby, because it changes the catalogs. (although a better solution to that would be to make it work, as well as temporary tables, but that's a much bigger project). It'd be neat, but I really don't see it happening. Maybe we don't need any of this, you can just use temporary function. But clearly someone though that DO statements are useful in general, because we've had temporary functions for ages and we nevertheless added the DO statement. Doing a CREATE FUNCTION like that has a mighty amount of cost associated. If you're not using the DO interactively, but programmatically the amount of catalog and cache churn can be problematic. So I'm in favor of adding parameters to DO. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
