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

On 9/18/14 1:35 PM, Martijn van Oosterhout wrote:

On Wed, Sep 17, 2014 at 10:17:22PM +0200, Pavel Stehule wrote:

Because you still have to do

SELECT pg_temp.my_temp_function(blah);

to execute it.

this problem should be solvable. I can to use a temporary tables without
using pg_temp schema.

Umm, IIRC it used to work that way but was changed to work like this.
IIRC the reason was that anyone can create functions in the temp
tablespace and thus hijack other functions that more priviledged
functions might call.

The same argument applies to temporary tables *already*. Consider:

=# create function oops() returns void as $$
$# begin insert into foo default values; end $$ language plpgsql
-# security definer;
CREATE FUNCTION
=# grant execute on function oops() to peasant;
GRANT

Then peasant does:

=> create temporary table foo();
CREATE TABLE
=> create function pg_temp.now_im_superuser() returns trigger as $$
$> begin raise notice '%', pg_read_file('pg_hba.conf'); return new; end
$> $$ language plpgsql;
CREATE FUNCTION
=> create trigger malicious before insert on pg_temp.foo
-> execute procedure pg_temp.now_im_superuser();
CREATE TRIGGER
=> select oops();
NOTICE: <contents of pg_hba.conf>

Personally, I think that if we're going to do something, we should be
*hiding* temporary stuff from search_path, not bringing it more visible.
Having to either prefix everything with the schema name or set
search_path for every SECURITY DEFINER function is a major PITA.

.marko

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

On 2014-09-18 13:44:47 +0200, Pavel Stehule wrote:

2014-09-18 13:40 GMT+02:00 Andres Freund <andres@2ndquadrant.com>:

On 2014-09-17 22:17:22 +0200, Pavel Stehule wrote:

2014-09-17 22:07 GMT+02:00 Vik Fearing <vik.fearing@dalibo.com>:

I fail to see why that is so much preferrable for you to passing
parameter to DO?

1) You need to think about unique names for functions
2) Doesn't work on HOT STANDBYs
3) Causes noticeable amount of catalog bloat
4) Is about a magnitude or two more expensive

1. I am not against simple DO, what doesn't substitute functions

2. When DO have to substitute functions, then I don't see a benefits

Show me real use case please?

Did you read what I wrote above? I'm sure you can rephrase them to be
more 'use case' like yourself.

Isn't being able to do this on a standby a fundamental enough advantage?
Being significantly cheaper? Needing fewer roundtrips?

Greetings,

Andres Freund

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

--
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:48 GMT+02:00 Andres Freund <andres@2ndquadrant.com>:

On 2014-09-18 13:44:47 +0200, Pavel Stehule wrote:

2014-09-18 13:40 GMT+02:00 Andres Freund <andres@2ndquadrant.com>:

On 2014-09-17 22:17:22 +0200, Pavel Stehule wrote:

2014-09-17 22:07 GMT+02:00 Vik Fearing <vik.fearing@dalibo.com>:

I fail to see why that is so much preferrable for you to passing
parameter to DO?

1) You need to think about unique names for functions
2) Doesn't work on HOT STANDBYs
3) Causes noticeable amount of catalog bloat
4) Is about a magnitude or two more expensive

1. I am not against simple DO, what doesn't substitute functions

2. When DO have to substitute functions, then I don't see a benefits

Show me real use case please?

Did you read what I wrote above? I'm sure you can rephrase them to be
more 'use case' like yourself.

Isn't being able to do this on a standby a fundamental enough advantage?
Being significantly cheaper? Needing fewer roundtrips?

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

Regards

Pavel

On 2014-09-18 13:51:56 +0200, Pavel Stehule wrote:

2014-09-18 13:48 GMT+02:00 Andres Freund <andres@2ndquadrant.com>:

On 2014-09-18 13:44:47 +0200, Pavel Stehule wrote:
Isn't being able to do this on a standby a fundamental enough advantage?
Being significantly cheaper? Needing fewer roundtrips?

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

FFS: What's redundant about being able to do this on a standby?

Greetings,

Andres Freund

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

--
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:53 GMT+02:00 Andres Freund <andres@2ndquadrant.com>:

On 2014-09-18 13:51:56 +0200, Pavel Stehule wrote:

2014-09-18 13:48 GMT+02:00 Andres Freund <andres@2ndquadrant.com>:

On 2014-09-18 13:44:47 +0200, Pavel Stehule wrote:
Isn't being able to do this on a standby a fundamental enough

advantage?

Being significantly cheaper? Needing fewer roundtrips?

no, I don't need more. My opinion is, so this proposal has no real

benefit,

but will do implement redundant functionality.

FFS: What's redundant about being able to do this on a standby?

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

2014-09-18 13:59 GMT+02:00 Pavel Stehule <pavel.stehule@gmail.com>:

2014-09-18 13:53 GMT+02:00 Andres Freund <andres@2ndquadrant.com>:

On 2014-09-18 13:51:56 +0200, Pavel Stehule wrote:

2014-09-18 13:48 GMT+02:00 Andres Freund <andres@2ndquadrant.com>:

On 2014-09-18 13:44:47 +0200, Pavel Stehule wrote:
Isn't being able to do this on a standby a fundamental enough

advantage?

Being significantly cheaper? Needing fewer roundtrips?

no, I don't need more. My opinion is, so this proposal has no real

benefit,

but will do implement redundant functionality.

FFS: What's redundant about being able to do this on a standby?

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

On 09/18/2014 07:40 AM, Andres Freund wrote:

On 2014-09-17 22:17:22 +0200, Pavel Stehule wrote:

2014-09-17 22:07 GMT+02:00 Vik Fearing <vik.fearing@dalibo.com>:

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

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

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

Why we don't introduce a temporary functions instead?

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

So why not just add "CREATE TEMPORARY FUNCTION"?

Sure, why not.

Because you still have to do

SELECT pg_temp.my_temp_function(blah);

to execute it.

this problem should be solvable. I can to use a temporary tables without
using pg_temp schema.

I fail to see why that is so much preferrable for you to passing
parameter to DO?

1) You need to think about unique names for functions
2) Doesn't work on HOT STANDBYs
3) Causes noticeable amount of catalog bloat
4) Is about a magnitude or two more expensive

So yes, TEMPORARY FUNCTION would be helpful. But it's simply a different
feature.

+1

If my memory isn't failing, when we implemented DO there were arguments
for this additional feature, but we decided that it wouldn't be done at
least on the first round. But we've had DO for a while and it's proved
its worth. So I think now is a perfect time to revisit the issue.

cheers

andrew

--
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/18/2014 08:41 PM, Andrew Dunstan wrote:

On 09/18/2014 07:40 AM, Andres Freund wrote:

On 2014-09-17 22:17:22 +0200, Pavel Stehule wrote:

2014-09-17 22:07 GMT+02:00 Vik Fearing <vik.fearing@dalibo.com>:

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

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

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

Why we don't introduce a temporary functions instead?

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

So why not just add "CREATE TEMPORARY FUNCTION"?

Sure, why not.

Because you still have to do

SELECT pg_temp.my_temp_function(blah);

to execute it.

this problem should be solvable. I can to use a temporary tables
without
using pg_temp schema.

I fail to see why that is so much preferrable for you to passing
parameter to DO?

1) You need to think about unique names for functions
2) Doesn't work on HOT STANDBYs
3) Causes noticeable amount of catalog bloat
4) Is about a magnitude or two more expensive

So yes, TEMPORARY FUNCTION would be helpful. But it's simply a different
feature.

+1

If my memory isn't failing, when we implemented DO there were
arguments for this additional feature, but we decided that it wouldn't
be done at least on the first round. But we've had DO for a while and
it's proved its worth. So I think now is a perfect time to revisit the
issue.

One possible syntax would be extending WITH to somehow enable on-spot
functions in addition to on-spot views

WITH FUNCTION myfunc(...) RETURNS TABLE(...) LANGUAGE plpgsql AS $$
...
$$
SELECT f.*
FROM myfunc(x,y,z);

Cheers

--
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic O�

--
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/18/2014 02:37 PM, Pavel Stehule wrote:

if we would to need a "single use" function, then we should to
implement it, and we should not to rape some different objects. Some,
what has behave like function should be function.

After some thinking, probably CTE design can be only one frame, where
we can do it

WITH
FUNCTION f1(a int) RETURNS int AS $$ .. $$ LANGUAGE plpgsql,
FUNCTION f2(a int) RETURNS SETOF int AS $$ .. $$ LANGUAGE
plpgsql,
SELECT f1(x) FROM f2(z) LATERAL ....

We can generalize WITH clause, so there SEQENCES, VIEWS, .. can be
defined for "single usage"

+2

I just proposed the same thing in another branch of this discussion
before reading this :)

I guess it proves (a little) that WITH is the right place to do these
kind of things ...

Cheers

--
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ

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

On 09/18/2014 01:10 PM, Hannu Krosing wrote:

One possible syntax would be extending WITH to somehow enable on-spot
functions in addition to on-spot views

WITH FUNCTION myfunc(...) RETURNS TABLE(...) LANGUAGE plpgsql AS $$
...
$$
SELECT f.*
FROM myfunc(x,y,z);

Oh! Awesome!

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

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

On 09/18/2014 10:16 PM, Hannu Krosing wrote:

On 09/18/2014 02:37 PM, Pavel Stehule wrote:

if we would to need a "single use" function, then we should to
implement it, and we should not to rape some different objects. Some,
what has behave like function should be function.

After some thinking, probably CTE design can be only one frame, where
we can do it

WITH
FUNCTION f1(a int) RETURNS int AS $$ .. $$ LANGUAGE plpgsql,
FUNCTION f2(a int) RETURNS SETOF int AS $$ .. $$ LANGUAGE
plpgsql,
SELECT f1(x) FROM f2(z) LATERAL ....

We can generalize WITH clause, so there SEQENCES, VIEWS, .. can be
defined for "single usage"

+2

I just proposed the same thing in another branch of this discussion
before reading this :)

I guess it proves (a little) that WITH is the right place to do these
kind of things ...

I've been wanting this syntax for a few years now, so I certainly vote
for it.
--
Vik

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

On 09/18/2014 01:29 PM, Vik Fearing wrote:

On 09/18/2014 10:16 PM, Hannu Krosing wrote:

WITH
FUNCTION f1(a int) RETURNS int AS $$ .. $$ LANGUAGE plpgsql,
FUNCTION f2(a int) RETURNS SETOF int AS $$ .. $$ LANGUAGE
plpgsql,
SELECT f1(x) FROM f2(z) LATERAL ....

We can generalize WITH clause, so there SEQENCES, VIEWS, .. can be
defined for "single usage"

+2

I just proposed the same thing in another branch of this discussion
before reading this :)

I guess it proves (a little) that WITH is the right place to do these
kind of things ...

I've been wanting this syntax for a few years now, so I certainly vote
for it.

Just to clarify: I want the WITH syntax for different purposes.
However, I *also* want DO $$ ... $$ USING ( ). Those are two separate,
different features with different use-cases.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

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

On 2014-09-18 10:29 PM, Vik Fearing wrote:

On 09/18/2014 10:16 PM, Hannu Krosing wrote:

I guess it proves (a little) that WITH is the right place to do these
kind of things ...

I've been wanting this syntax for a few years now, so I certainly vote
for it.

I've also been wanting do to something like:

WITH mytyp AS (a int, b int, c int)
SELECT (tup).* FROM
(
SELECT CASE WHEN .. THEN ROW(1,2,3)::mytyp
WHEN .. THEN ROW(2,3,4)
ELSE ROW (3,4,5) END AS tup
FROM ..
) ss

.marko

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

On 09/18/2014 10:40 PM, Marko Tiikkaja wrote:

On 2014-09-18 10:29 PM, Vik Fearing wrote:

On 09/18/2014 10:16 PM, Hannu Krosing wrote:

I guess it proves (a little) that WITH is the right place to do these
kind of things ...

I've been wanting this syntax for a few years now, so I certainly vote
for it.

I've also been wanting do to something like:

WITH mytyp AS (a int, b int, c int)
SELECT (tup).* FROM
(
SELECT CASE WHEN .. THEN ROW(1,2,3)::mytyp
WHEN .. THEN ROW(2,3,4)
ELSE ROW (3,4,5) END AS tup
FROM ..
) ss

+1

.marko

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

On 09/19/2014 12:14 AM, Hannu Krosing wrote:

On 09/18/2014 10:40 PM, Marko Tiikkaja wrote:

On 2014-09-18 10:29 PM, Vik Fearing wrote:

On 09/18/2014 10:16 PM, Hannu Krosing wrote:

I guess it proves (a little) that WITH is the right place to do these
kind of things ...

I've been wanting this syntax for a few years now, so I certainly vote
for it.

I've also been wanting do to something like:

WITH mytyp AS (a int, b int, c int)
SELECT (tup).* FROM
(
SELECT CASE WHEN .. THEN ROW(1,2,3)::mytyp
WHEN .. THEN ROW(2,3,4)
ELSE ROW (3,4,5) END AS tup
FROM ..
) ss

+1

Though it would be even nicer to have fully in-line type definition

SELECT (tup).* FROM
(
SELECT CASE WHEN .. THEN ROW(1,2,3)::(a int, b text, c int2)
WHEN .. THEN ROW(2,3,4)
ELSE ROW (3,4,5) END AS tup
FROM ..
) ss

or an incomplete type with names, as types can be given in ROW

SELECT (tup).* FROM
(
SELECT CASE WHEN .. THEN ROW(1,2::text,3::int2)::(a, b, c)
WHEN .. THEN ROW(2,3,4)
ELSE ROW (3,4,5) END AS tup
FROM ..
) ss

or just normal select query syntax:

SELECT (tup).* FROM
(
SELECT CASE WHEN .. THEN ROW(1 AS a,2::text AS b,3::int2 AS c)
WHEN .. THEN ROW(2,3,4)
ELSE ROW (3,4,5) END AS tup
FROM ..
) ss

Cheers

--
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ

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

2014-09-18 22:35 GMT+02:00 Josh Berkus <josh@agliodbs.com>:

On 09/18/2014 01:29 PM, Vik Fearing wrote:

On 09/18/2014 10:16 PM, Hannu Krosing wrote:

WITH
FUNCTION f1(a int) RETURNS int AS $$ .. $$ LANGUAGE plpgsql,
FUNCTION f2(a int) RETURNS SETOF int AS $$ .. $$ LANGUAGE
plpgsql,
SELECT f1(x) FROM f2(z) LATERAL ....

We can generalize WITH clause, so there SEQENCES, VIEWS, .. can be
defined for "single usage"

+2

I just proposed the same thing in another branch of this discussion
before reading this :)

I guess it proves (a little) that WITH is the right place to do these
kind of things ...

I've been wanting this syntax for a few years now, so I certainly vote
for it.

Just to clarify: I want the WITH syntax for different purposes.
However, I *also* want DO $$ ... $$ USING ( ). Those are two separate,
different features with different use-cases.

+1 as parametrized (read only) DO statement

On Thu, Sep 18, 2014 at 5:22 PM, Hannu Krosing <hannu@2ndquadrant.com> wrote:

Though it would be even nicer to have fully in-line type definition

SELECT (tup).* FROM
(
SELECT CASE WHEN .. THEN ROW(1,2,3)::(a int, b text, c int2)
WHEN .. THEN ROW(2,3,4)
ELSE ROW (3,4,5) END AS tup
FROM ..
) ss

+1. Workaround at present (which I mostly use during json serialization) is:

SELECT (tup).* FROM
(
SELECT CASE WHEN .. THEN
(SELECT q FROM (SELECT 1, 2, 3) q)
WHEN .. THEN
(SELECT q FROM (SELECT 2, 3, 4) q)
ELSE (SELECT q FROM (SELECT 3, 4, 5) q)
END AS tup
FROM ..
) ss

If you're talking in line type definitions (which is kinda off topic)
though, it'd be nice to consider:

* nested type definition:
create type foo_t as
(
a text,
b int,
bars bar_t[] as
(
c int,
d text
),
baz baz_t as
(
e text,
f text
)
);

* ...and recursive type references (not being able to recursively
serialize json is a major headache)
create type foo_t as
(
path text,
children foo_t[]
);

merlin

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

On Fri, Sep 19, 2014 at 9:26 AM, Merlin Moncure <mmoncure@gmail.com> wrote:

On Thu, Sep 18, 2014 at 5:22 PM, Hannu Krosing <hannu@2ndquadrant.com> wrote:

Though it would be even nicer to have fully in-line type definition

SELECT (tup).* FROM
(
SELECT CASE WHEN .. THEN ROW(1,2,3)::(a int, b text, c int2)
WHEN .. THEN ROW(2,3,4)
ELSE ROW (3,4,5) END AS tup
FROM ..
) ss

+1. Workaround at present (which I mostly use during json serialization) is:

SELECT (tup).* FROM
(
SELECT CASE WHEN .. THEN
(SELECT q FROM (SELECT 1, 2, 3) q)
WHEN .. THEN
(SELECT q FROM (SELECT 2, 3, 4) q)
ELSE (SELECT q FROM (SELECT 3, 4, 5) q)
END AS tup
FROM ..
) ss

actually, this trick *only* works during json serialization -- it
allows control over the column names that row() masks over. trying to
expand (tup).* still gives the dreaded "ERROR: record type has not
been registered". That's because this works:

select (q).* from (select 1 as a, 2 as b) q;

but this doesn't:

select ((select q from (select a,b) q)).* from (select 1 as a, 2 as b) q;

merlin

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

On 2014-09-19 8:20 PM, Merlin Moncure wrote:

actually, this trick *only* works during json serialization -- it
allows control over the column names that row() masks over. trying to
expand (tup).* still gives the dreaded "ERROR: record type has not
been registered". That's because this works:

select (q).* from (select 1 as a, 2 as b) q;

but this doesn't:

select ((select q from (select a,b) q)).* from (select 1 as a, 2 as b) q;

Yeah. This is a seriously missing feature and a PITA. :-(

.marko

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

On 9/18/14 7:40 AM, Andres Freund wrote:

I fail to see why that is so much preferrable for you to passing
parameter to DO?

1) You need to think about unique names for functions
2) Doesn't work on HOT STANDBYs
3) Causes noticeable amount of catalog bloat
4) Is about a magnitude or two more expensive

Doesn't this apply to all temporary objects? It would also be great to
have temporary tables, temporary indexes, temporary triggers, temporary
extensions, etc. that don't have the above problems. I think inventing
a separate mechanism for working around each instance of this problem
would end up being very confusing.

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

On 2014-09-22 15:46:48 -0400, Peter Eisentraut wrote:

On 9/18/14 7:40 AM, Andres Freund wrote:

I fail to see why that is so much preferrable for you to passing
parameter to DO?

1) You need to think about unique names for functions
2) Doesn't work on HOT STANDBYs
3) Causes noticeable amount of catalog bloat
4) Is about a magnitude or two more expensive

Doesn't this apply to all temporary objects? It would also be great to
have temporary tables, temporary indexes, temporary triggers, temporary
extensions, etc. that don't have the above problems. I think inventing
a separate mechanism for working around each instance of this problem
would end up being very confusing.

Meh. Those aren't comparable. TEMPORARY TABLES/INDEXES/... all live
beyond a single statement. What's being discussed here doesn't.

Greetings,

Andres Freund

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

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

On Mon, Sep 22, 2014 at 2:49 PM, Andres Freund <andres@2ndquadrant.com> wrote:

On 2014-09-22 15:46:48 -0400, Peter Eisentraut wrote:

On 9/18/14 7:40 AM, Andres Freund wrote:

I fail to see why that is so much preferrable for you to passing
parameter to DO?

1) You need to think about unique names for functions
2) Doesn't work on HOT STANDBYs
3) Causes noticeable amount of catalog bloat
4) Is about a magnitude or two more expensive

Doesn't this apply to all temporary objects? It would also be great to
have temporary tables, temporary indexes, temporary triggers, temporary
extensions, etc. that don't have the above problems. I think inventing
a separate mechanism for working around each instance of this problem
would end up being very confusing.

Meh. Those aren't comparable. TEMPORARY TABLES/INDEXES/... all live
beyond a single statement. What's being discussed here doesn't.

Even if that wasn't true, 'DO' doesn't involve changes to system
catalogs whereas temporary functions would. With a little imagination
I could come up a with a scenario involving a script of a whole bunch
of repeated trivial DO statements which would involve a lot less
beating on the system catalogs.

When the data-modifying-with feature was considered, an implementation
that relied on temp tables was rejected at least in part because of
system catalog thrash and poorer performance for very trivial queries.

So, to me, DO vs CREATE FUNCTION has nothing to do with passing
arguments and/or returning data. It has to do with lifespan; single
call of the function body only, use DO, otherwise, create a function.

merlin

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

On 22/09/14 22:58, Merlin Moncure wrote:

Meh. Those aren't comparable. TEMPORARY TABLES/INDEXES/... all live
beyond a single statement. What's being discussed here doesn't.

Even if that wasn't true, 'DO' doesn't involve changes to system
catalogs whereas temporary functions would. With a little imagination
I could come up a with a scenario involving a script of a whole bunch
of repeated trivial DO statements which would involve a lot less
beating on the system catalogs.

When the data-modifying-with feature was considered, an implementation
that relied on temp tables was rejected at least in part because of
system catalog thrash and poorer performance for very trivial queries.

So, to me, DO vs CREATE FUNCTION has nothing to do with passing
arguments and/or returning data. It has to do with lifespan; single
call of the function body only, use DO, otherwise, create a function.

Actually same thing happened with the DO implementation itself -
creating anonymous/hidden temporary functions in the background was also
considered but was decided it's not acceptable (for similar reason temp
tables were rejected for WITH).

So we decided at least twice already that this kind of solution is bad,
I don't know of any change that would invalidate the reasons for
deciding that way so I don't see why they would suddenly become
acceptable...

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

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

On 09/23/2014 07:20 AM, Petr Jelinek wrote:

So, to me, DO vs CREATE FUNCTION has nothing to do with passing
arguments and/or returning data. It has to do with lifespan; single
call of the function body only, use DO, otherwise, create a function.

Actually same thing happened with the DO implementation itself -
creating anonymous/hidden temporary functions in the background was also
considered but was decided it's not acceptable (for similar reason temp
tables were rejected for WITH).

So we decided at least twice already that this kind of solution is bad,
I don't know of any change that would invalidate the reasons for
deciding that way so I don't see why they would suddenly become
acceptable...

All good points. I was wrong to suggest just going for TEMPORARY
FUNCTION before, there's clearly a useful place for DO with parameters.

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

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