Re: expression evaluation with expected datatypes

Hello

When I worked on parametrised DO statement, I had to solve following issue:

Syntax is:

DO (param list) $$ ... $$ LANGUAGE ... USING expr_list

What is correct way for evaluation of expr_list with specified target types?

I used two techniques:

1) evaluation expressions -
http://archives.postgresql.org/pgsql-hackers/2012-07/msg00340.php

this code is elegant and works well - with one significant issue -
doesn't support subqueries

2) SPI with parse_tree execution - SPI expect so SQL will be entered
in plain text form. But sometimes we have as input parsed tree - as
result of some parser. We can serialize tree to string, but then we
get different queryString and we will have problem with possible error
identification in queryString, I patched SPI and append a two
functions for evaluation parsed tree. Probably it should be better
done without SPI, but I missing some like DestSPI for general usage.

http://archives.postgresql.org/pgsql-hackers/2012-07/msg00361.php

What is correct way for solution of this task? I am thinking so there
some interface is missing

Regards

Pavel

Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> When I worked on parametrised DO statement, I had to solve following issue:

> Syntax is:

> DO (param list) $$ ... $$ LANGUAGE ... USING expr_list

> What is correct way for evaluation of expr_list with specified target types?

I'd argue that that's a pointlessly unwieldy syntax that's creating an
unnecessary problem. Just use a SELECT list, that is

DO ... USING value AS name, value2 AS name2, ...

The value expressions can define their own types just fine.

regards, tom lane

2012/7/8 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>> When I worked on parametrised DO statement, I had to solve following issue:
>
>> Syntax is:
>
>> DO (param list) $$ ... $$ LANGUAGE ... USING expr_list
>
>> What is correct way for evaluation of expr_list with specified target types?
>
> I'd argue that that's a pointlessly unwieldy syntax that's creating an
> unnecessary problem. Just use a SELECT list, that is
>
> DO ... USING value AS name, value2 AS name2, ...
>
> The value expressions can define their own types just fine.

it is solution, but it is consistent with nothing what we support

* it is different than USING clause in other statements or in dynamic
SQL in plpgsql
* it is different than named parameter syntax

and I don't think so plpgsql style in main core is ok - when parser
returns some tree, but we use substring of queryString instead.

Regards

Pavel

>
> regards, tom lane

Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> When I worked on parametrised DO statement, I had to solve following issue:

DO currently is a utility command, not a query. Do you mean to change
that?

Also, did you think about a lambda construct, which is basically
allowing functions to be defined inline in a query?

We could imagine several syntax to show up the idea, common keywords
here include LAMBDA, FLET or LABELS, but I think that expanding WITH
would be preferable for us.

WITH FUNCTION foo(param list) returns rettype language foo AS (
definition here
)
<query using foo() here>;

Other WITH extensions we can think about include support for DCL as
asked by David Fetter in the past already, and support for variables too
(a kind of per-query SET LOCAL).

I don't see how adding parameters and return values to utility commands
is going to be easier than adding a "lambda" facility.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

2012/7/10 Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr>:
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>> When I worked on parametrised DO statement, I had to solve following issue:
>
> DO currently is a utility command, not a query. Do you mean to change
> that?
>
> Also, did you think about a lambda construct, which is basically
> allowing functions to be defined inline in a query?
>
> We could imagine several syntax to show up the idea, common keywords
> here include LAMBDA, FLET or LABELS, but I think that expanding WITH
> would be preferable for us.
>
> WITH FUNCTION foo(param list) returns rettype language foo AS (
> definition here
> )
> <query using foo() here>;
>
> Other WITH extensions we can think about include support for DCL as
> asked by David Fetter in the past already, and support for variables too
> (a kind of per-query SET LOCAL).
>
> I don't see how adding parameters and return values to utility commands
> is going to be easier than adding a "lambda" facility.

I don't think so we need true LAMBDA - we don't need support for
recursion and we don't need to modify system tables.

I don't see any advantage and usage of this complex syntax

Regards

Pavel

>
> Regards,
> --
> Dimitri Fontaine
> http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr> writes:
> We could imagine several syntax to show up the idea, common keywords
> here include LAMBDA, FLET or LABELS, but I think that expanding WITH
> would be preferable for us.

> WITH FUNCTION foo(param list) returns rettype language foo AS (
> definition here
> )
> <query using foo() here>;

I like this idea. This gets rid of both the "how to pass parameters"
and the "how to return results" issues that exist with DO, as well as
assorted implementation problems that you hinted at by asking whether
DO would still be a utility command.

In the syntax-bikeshedding department, we'd still need the function body
to be a string literal, and I think we'd want the ability to add
options such as IMMUTABLE/VOLATILE. So I'd be inclined to move all
these options inside the parentheses that the WITH syntax dictates.
Perhaps

WITH FUNCTION foo(paramlist) AS (
returns int
as $$ ... $$
language plpgsql
... other CREATE FUNCTION options as needed ...
)
query here ...

regards, tom lane

2012/7/10 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr> writes:
>> We could imagine several syntax to show up the idea, common keywords
>> here include LAMBDA, FLET or LABELS, but I think that expanding WITH
>> would be preferable for us.
>
>> WITH FUNCTION foo(param list) returns rettype language foo AS (
>> definition here
>> )
>> <query using foo() here>;
>
> I like this idea. This gets rid of both the "how to pass parameters"
> and the "how to return results" issues that exist with DO, as well as
> assorted implementation problems that you hinted at by asking whether
> DO would still be a utility command.

what is use case for this statement?

Regards

Pavel

>
> In the syntax-bikeshedding department, we'd still need the function body
> to be a string literal, and I think we'd want the ability to add
> options such as IMMUTABLE/VOLATILE. So I'd be inclined to move all
> these options inside the parentheses that the WITH syntax dictates.
> Perhaps
>
> WITH FUNCTION foo(paramlist) AS (
> returns int
> as $$ ... $$
> language plpgsql
> ... other CREATE FUNCTION options as needed ...
> )
> query here ...
>
> regards, tom lane

Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>>> WITH FUNCTION foo(param list) returns rettype language foo AS (
>>> definition here
>>> )
>>> <query using foo() here>;
>>
>> I like this idea. This gets rid of both the "how to pass parameters"
>> and the "how to return results" issues that exist with DO, as well as
>> assorted implementation problems that you hinted at by asking whether
>> DO would still be a utility command.
>
> what is use case for this statement?

It's the DO block idea turned into a query rather than a utility
command: you can now run a function that does not exists in the catalogs
*and* feed it parameters (either from the client, as literals in the
main query, or from the query itself) *and* you get a query result our
of it.

I'm not sure I can understand the difference between that and the use
case for which you want to implement DO blocks with parameters.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

2012/7/10 Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr>:
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>>>> WITH FUNCTION foo(param list) returns rettype language foo AS (
>>>> definition here
>>>> )
>>>> <query using foo() here>;
>>>
>>> I like this idea. This gets rid of both the "how to pass parameters"
>>> and the "how to return results" issues that exist with DO, as well as
>>> assorted implementation problems that you hinted at by asking whether
>>> DO would still be a utility command.
>>
>> what is use case for this statement?
>
> It's the DO block idea turned into a query rather than a utility
> command: you can now run a function that does not exists in the catalogs
> *and* feed it parameters (either from the client, as literals in the
> main query, or from the query itself) *and* you get a query result our
> of it.
>
> I'm not sure I can understand the difference between that and the use
> case for which you want to implement DO blocks with parameters.

this is similar to temporary functions - you need some temporary name
- it is insert to pg_proc, and you have to solve possible conflicts.

>
> Regards,
> --
> Dimitri Fontaine
> http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> 2012/7/10 Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr>:
>> I'm not sure I can understand the difference between that and the use
>> case for which you want to implement DO blocks with parameters.

> this is similar to temporary functions - you need some temporary name
> - it is insert to pg_proc, and you have to solve possible conflicts.

What's to solve? Presumably the WITH function name would take
precedence over anything in the catalogs, the same as WITH query names
take precedence over actual tables.

regards, tom lane

Excerpts from Tom Lane's message of mar jul 10 10:56:50 -0400 2012:
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> > 2012/7/10 Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr>:
> >> I'm not sure I can understand the difference between that and the use
> >> case for which you want to implement DO blocks with parameters.
>
> > this is similar to temporary functions - you need some temporary name
> > - it is insert to pg_proc, and you have to solve possible conflicts.
>
> What's to solve? Presumably the WITH function name would take
> precedence over anything in the catalogs, the same as WITH query names
> take precedence over actual tables.

Hm, would the newly defined function mask all regular functions with
that name? If not, a seemingly innocuous change in a query could mean
calling not the function defined in the WITH FUNCTION clause but another
one with the same name but different parameter count/types.

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> Excerpts from Tom Lane's message of mar jul 10 10:56:50 -0400 2012:
>> What's to solve? Presumably the WITH function name would take
>> precedence over anything in the catalogs, the same as WITH query names
>> take precedence over actual tables.

> Hm, would the newly defined function mask all regular functions with
> that name?

Only the ones with the same parameter types ...

> If not, a seemingly innocuous change in a query could mean
> calling not the function defined in the WITH FUNCTION clause but another
> one with the same name but different parameter count/types.

I would see this working as if the WITH function appeared in a schema
earlier in the search path than any regular functions. So the risk is
not greater, nor indeed different, than from any other overloaded
function name.

regards, tom lane