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

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

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

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

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

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

2014-09-16 9:24 GMT+02:00 Heikki Linnakangas <hlinnakangas@vmware.com>:

On 09/16/2014 10:15 AM, Pavel Stehule wrote:

2014-09-16 9:10 GMT+02:00 Heikki Linnakangas <hlinnakangas@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, Scalability and High Availability
2ndQuadrant Nordic OÜ

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

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

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

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   <kleptog@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

On 2014-09-17 22:17:22 +0200, Pavel Stehule wrote:
> 2014-09-17 22:07 GMT+02:00 Vik Fearing <vik.fearing@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

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
schemaname or set 
 
search_path for every SECURITY DEFINER function is a major PITA.


.marko

On 2014-09-18 13:44:47 +0200, Pavel Stehule wrote:
> 2014-09-18 13:40 GMT+02:00 Andres Freund <andres@2ndquadrant.com>:
> 
> > On 2014-09-17 22:17:22 +0200, Pavel Stehule wrote:
> > > 2014-09-17 22:07 GMT+02:00 Vik Fearing <vik.fearing@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

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

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

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.fearing@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Ü

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Ü

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

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

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

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

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

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Ü

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

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

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

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.

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

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

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

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