Anonymous code block with parameters

Hi

2014-09-16 8:38 GMT+02:00 Kalyanov Dmitry <kalyanov.dmitry@gmail.com>:

I'd like to propose support for IN and OUT parameters in 'DO' blocks.

Currently, anonymous code blocks (DO statements) can not receive or
return parameters.

I suggest:

1) Add a new clause to DO statement for specifying names, types,
directions and values of parameters:

DO <code> [LANGUAGE <lang>] [USING (<arguments>)]

where <arguments> has the same syntax as in
'CREATE FUNCTION <name> (<arguments>)'.

Example:

do $$ begin z := x || y; end; $$
language plpgsql
using
(
x text = '1',
in out y int4 = 123,
out z text
);

2) Values for IN and IN OUT parameters are specified using syntax for
default values of function arguments.

3) If DO statement has at least one of OUT or IN OUT parameters then it
returns one tuple containing values of OUT and IN OUT parameters.

Do you think that this feature would be useful? I have a
proof-of-concept patch in progress that I intend to publish soon.

shortly

+1 parametrization of DO statement

-1 OUT parameters for DO - when you need OUTPUT, then use a function. A
rules used for output from something are messy now, and I strongly against
to do this area more complex. Instead we can define temporary functions or
we can define real server side session variables.

Pavel

On 09/16/2014 09:38 AM, Kalyanov Dmitry wrote:

I'd like to propose support for IN and OUT parameters in 'DO' blocks.

Currently, anonymous code blocks (DO statements) can not receive or
return parameters.

I suggest:

1) Add a new clause to DO statement for specifying names, types,
directions and values of parameters:

DO <code> [LANGUAGE <lang>] [USING (<arguments>)]

where <arguments> has the same syntax as in
'CREATE FUNCTION <name> (<arguments>)'.

Example:

do $$ begin z := x || y; end; $$
language plpgsql
using
(
x text = '1',
in out y int4 = 123,
out z text
);

2) Values for IN and IN OUT parameters are specified using syntax for
default values of function arguments.

3) If DO statement has at least one of OUT or IN OUT parameters then it
returns one tuple containing values of OUT and IN OUT parameters.

Do you think that this feature would be useful? I have a
proof-of-concept patch in progress that I intend to publish soon.

There are two features here. One is to allow arguments to be passed to
DO statements. The other is to allow a DO statement to return a result.
Let's discuss them separately.

1) Passing arguments to a DO block can be useful feature, because it
allows you to pass parameters to the DO block without injecting them
into the string, which helps to avoid SQL injection attacks.

I don't like the syntax you propose though. It doesn't actually let you
pass the parameters out-of-band, so I don't really see the point. I
think this needs to work with PREPARE/EXECUTE, and the protocol-level
prepare/execute mechanism. Ie. something like this:

PREPARE mydoblock (text, int4) AS DO $$ ... $$
EXECUTE mydoblock ('foo', 123);

2) Returning values from a DO block would also be handy. But I don't see
why it should be restricted to OUT parameters. I'd suggest allowing a
RETURNS clause, like in CREATE FUNCTION:

DO $$ ... $$ LANGUAGE plpgsql RETURNS int4;

or

DO $$ ... $$ LANGUAGE plpgsql RETURNS TABLE (col1 text, col2 int4);

- Heikki

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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?

Pavel

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

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

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.

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

On 09/16/2014 03:15 PM, Pavel Stehule wrote:

Why we don't introduce a temporary functions instead?

I think that'd be a lot cleaner and simpler. It's something I've
frequently wanted, and as Hekki points out it's already possible by
creating the function in pg_temp, there just isn't the syntax sugar for
"CREATE TEMPORARY FUNCTION".

So why not just add "CREATE TEMPORARY FUNCTION"?

It means two steps:

CREATE TEMPORARY FUNCTION ... $$ $$;

SELECT my_temp_function(blah);

but I'm not personally convinced that a parameterised DO block is much
easier, and the idea just rings wrong to me.

I agree with Pavel that the natural way to parameterise DO blocks, down
the track, will be to allow them to get (and set?) SQL-typed session
variables. Of course, we'd need to support them first ;-)

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 09/16/2014 09:44 AM, Pavel Stehule wrote:

2014-09-16 9:24 GMT+02:00 Heikki Linnakangas <hlinnakangas@vmware.com
<mailto: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 <mailto: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Ü

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

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?

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

2014-09-16 10:01 GMT+02:00 Hannu Krosing <hannu@2ndquadrant.com>:

On 09/16/2014 09: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:

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

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

On 09/16/2014 10:57 AM, Craig Ringer wrote:

On 09/16/2014 03:15 PM, Pavel Stehule wrote:

Why we don't introduce a temporary functions instead?

I think that'd be a lot cleaner and simpler. It's something I've
frequently wanted, and as Hekki points out it's already possible by
creating the function in pg_temp, there just isn't the syntax sugar for
"CREATE TEMPORARY FUNCTION".

So why not just add "CREATE TEMPORARY FUNCTION"?

Sure, why not.

It means two steps:

CREATE TEMPORARY FUNCTION ... $$ $$;

SELECT my_temp_function(blah);

but I'm not personally convinced that a parameterised DO block is much
easier, and the idea just rings wrong to me.

With the above, you'll have to remember to drop the function when you're
done, or deal with the fact that the function might already exist.
That's doable, of course, but with a DO statement you don't have to.

I agree with Pavel that the natural way to parameterise DO blocks, down
the track, will be to allow them to get (and set?) SQL-typed session
variables. Of course, we'd need to support them first ;-)

I responded to Pavel that using a session variable for a return value
would be awkward, but using them as parameters would open a different
can of worms. A session variable might change while the statement is
run, so for anything but trivial DO blocks, a best practice would have
to be to copy the session variable to a local variable as the first
thing to do. For example, if you just use session variables arg1 and
arg2, and you call a function that uses those same session variables for
some other purposes, you will be surprised. Also, you'd have to remember
to reset the session variables after use if there's any sensitive
information in them, or you might leak them to surprising places. And if
you forget to pass an argument, i.e. you forget to set a session
variable that's used as an argument, the parser would not help you to
catch your mistake but would merrily run the DO block with whatever the
content of the argument happens to be.

Using session variables for arguments would be anything but natural.

- Heikki

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

On 09/16/2014 10:57 AM, Craig Ringer wrote:

On 09/16/2014 03:15 PM, Pavel Stehule wrote:

Why we don't introduce a temporary functions instead?

I think that'd be a lot cleaner and simpler. It's something I've
frequently wanted, and as Hekki points out it's already possible by
creating the function in pg_temp, there just isn't the syntax sugar for
"CREATE TEMPORARY FUNCTION".

So why not just add "CREATE TEMPORARY FUNCTION"?

Sure, why not.

It means two steps:

CREATE TEMPORARY FUNCTION ... $$ $$;

SELECT my_temp_function(blah);

but I'm not personally convinced that a parameterised DO block is much
easier, and the idea just rings wrong to me.

With the above, you'll have to remember to drop the function when you're
done, or deal with the fact that the function might already exist. That's
doable, of course, but with a DO statement you don't have to.

I agree with Pavel that the natural way to parameterise DO blocks, down

the track, will be to allow them to get (and set?) SQL-typed session
variables. Of course, we'd need to support them first ;-)

I responded to Pavel that using a session variable for a return value
would be awkward, but using them as parameters would open a different can
of worms. A session variable might change while the statement is run, so
for anything but trivial DO blocks, a best practice would have to be to
copy the session variable to a local variable as the first thing to do. For
example, if you just use session variables arg1 and arg2, and you call a
function that uses those same session variables for some other purposes,
you will be surprised. Also, you'd have to remember to reset the session
variables after use if there's any sensitive information in them, or you
might leak them to surprising places. And if you forget to pass an
argument, i.e. you forget to set a session variable that's used as an
argument, the parser would not help you to catch your mistake but would
merrily run the DO block with whatever the content of the argument happens
to be.

Personally I can't to imagine some more complex code as DO block.

Hi,

On 2014-09-16 10:24:52 +0300, Heikki Linnakangas wrote:

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

Why we don't introduce a temporary functions instead?

You can already do that:

create function pg_temp.tempfunc(i int4) returns int4 as $$ begin end; $$
language plpgsql;

It's quite the, probably undocumented, hack though. I think it's hard to
find as a user, because it's more or less happenstance that it works. I
think we should introduce TEMPORARY properly for function, but that's a
separate patch.

Compared to DO, you have to do extra steps to create the function, and drop
it when you're done. And you can't use them in a hot standby, because it
changes the catalogs. (although a better solution to that would be to make
it work, as well as temporary tables, but that's a much bigger project).

It'd be neat, but I really don't see it happening.

Maybe we don't need any of this, you can just use temporary function. But
clearly someone though that DO statements are useful in general, because
we've had temporary functions for ages and we nevertheless added the DO
statement.

Doing a CREATE FUNCTION like that has a mighty amount of cost
associated. If you're not using the DO interactively, but
programmatically the amount of catalog and cache churn can be
problematic. So I'm in favor of adding parameters to DO.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 09/16/2014 10:09 AM, Heikki Linnakangas wrote:

On 09/16/2014 10:57 AM, Craig Ringer wrote:

On 09/16/2014 03:15 PM, Pavel Stehule wrote:

Why we don't introduce a temporary functions instead?

I think that'd be a lot cleaner and simpler. It's something I've
frequently wanted, and as Hekki points out it's already possible by
creating the function in pg_temp, there just isn't the syntax sugar for
"CREATE TEMPORARY FUNCTION".

So why not just add "CREATE TEMPORARY FUNCTION"?

Sure, why not.

Because you still have to do

SELECT pg_temp.my_temp_function(blah);

to execute it.

It means two steps:

CREATE TEMPORARY FUNCTION ... $$ $$;

SELECT my_temp_function(blah);

That won't work; see above.
--
Vik

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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.

Pavel

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

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