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

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 litt

Re: [HACKERS] Anonymous code block with parameters

On Mon, Sep 22, 2014 at 2:49 PM, Andres Freund 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 fo

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

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

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:

Re: [HACKERS] Anonymous code block with parameters

On Fri, Sep 19, 2014 at 9:26 AM, Merlin Moncure wrote: > On Thu, Sep 18, 2014 at 5:22 PM, Hannu Krosing 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) >>

Re: [HACKERS] Anonymous code block with parameters

On Thu, Sep 18, 2014 at 5:22 PM, Hannu Krosing 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 t

Re: [HACKERS] Anonymous code block with parameters

2014-09-18 22:35 GMT+02:00 Josh Berkus : > 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 > >

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 bee

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 c

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 somethi

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

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

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

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 fr

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 : >>> On 09/16/2014 10:09 AM, Heikki Linnakangas wrote: > On 09/16/2014 10:57 AM, Craig Ringer wro

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

Re: [HACKERS] Anonymous code block with parameters

2014-09-18 13:59 GMT+02:00 Pavel Stehule : > > > 2014-09-18 13:53 GMT+02:00 Andres Freund : > >> On 2014-09-18 13:51:56 +0200, Pavel Stehule wrote: >> > 2014-09-18 13:48 GMT+02:00 Andres Freund : >> > >> > > On 2014-09-18 13:44:47 +0200, Pavel Stehule wrote: >> > > Isn't being able to do this on a

Re: [HACKERS] Anonymous code block with parameters

2014-09-18 13:53 GMT+02:00 Andres Freund : > On 2014-09-18 13:51:56 +0200, Pavel Stehule wrote: > > 2014-09-18 13:48 GMT+02:00 Andres Freund : > > > > > 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 s

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 : > > > 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? > > > >

Re: [HACKERS] Anonymous code block with parameters

2014-09-18 13:48 GMT+02:00 Andres Freund : > On 2014-09-18 13:44:47 +0200, Pavel Stehule wrote: > > 2014-09-18 13:40 GMT+02:00 Andres Freund : > > > > > On 2014-09-17 22:17:22 +0200, Pavel Stehule wrote: > > > > 2014-09-17 22:07 GMT+02:00 Vik Fearing : > > > I fail to see why that is so much prefe

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 sch

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 : > > > On 2014-09-17 22:17:22 +0200, Pavel Stehule wrote: > > > 2014-09-17 22:07 GMT+02:00 Vik Fearing : > > I fail to see why that is so much preferrable for you to passing > > parameter to DO? > > >

Re: [HACKERS] Anonymous code block with parameters

2014-09-18 13:40 GMT+02:00 Andres Freund : > On 2014-09-17 22:17:22 +0200, Pavel Stehule wrote: > > 2014-09-17 22:07 GMT+02:00 Vik Fearing : > > > > > 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 Stehul

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 : > > > 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 tempor

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 w

Re: [HACKERS] Anonymous code block with parameters

2014-09-17 22:07 GMT+02:00 Vik Fearing : > 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

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, a

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

Re: [HACKERS] Anonymous code block with parameters

2014-09-16 10:09 GMT+02:00 Heikki Linnakangas : > 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 want

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 func

Re: [HACKERS] Anonymous code block with parameters

2014-09-16 10:01 GMT+02:00 Hannu Krosing : > On 09/16/2014 09:44 AM, Pavel Stehule wrote: > > > > 2014-09-16 9:24 GMT+02:00 Heikki Linnakangas : > >> On 09/16/2014 10:15 AM, Pavel Stehule wrote: >> >>> 2014-09-16 9:10 GMT+02:00 Heikki Linnakangas : >>> >>> On 09/16/2014 09:38 AM, Kalyanov Dmit

Re: [HACKERS] Anonymous code block with parameters

2014-09-16 9:58 GMT+02:00 Heikki Linnakangas : > On 09/16/2014 10:44 AM, Pavel Stehule wrote: > >> 2014-09-16 9:24 GMT+02:00 Heikki Linnakangas : >> >> On 09/16/2014 10:15 AM, Pavel Stehule wrote: >>> >>> Why we don't introduce a temporary functions instead? >>> You can already do that

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 >: > > On 09/16/2014 10:15 AM, Pavel Stehule wrote: > > 2014-09-16 9:10 GMT+02:00 Heikki Linnakangas > mailto:hlinnakan...@vmware.com>>: > >

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 s

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 : 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; $$

Re: [HACKERS] Anonymous code block with parameters

2014-09-16 9:24 GMT+02:00 Heikki Linnakangas : > On 09/16/2014 10:15 AM, Pavel Stehule wrote: > >> 2014-09-16 9:10 GMT+02:00 Heikki Linnakangas : >> >> 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

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 >: > > On 09/16/2014 09:38 AM, Kalyanov Dmitry wrote: > > I'd like to propose support for IN and OUT parameters in 'DO' > blocks. > > Currentl

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 : 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 paramete

Re: [HACKERS] Anonymous code block with parameters

2014-09-16 9:10 GMT+02:00 Heikki Linnakangas : > 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

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

Re: [HACKERS] Anonymous code block with parameters

Hi 2014-09-16 8:38 GMT+02:00 Kalyanov Dmitry : > 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, >

[HACKERS] Anonymous code block with parameters

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 [LANGUAGE ] [USING ()