Thread: 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.stehule@gmail.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@sss.pgh.pa.us>: > Pavel Stehule <pavel.stehule@gmail.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.stehule@gmail.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@2ndquadrant.fr>: > Pavel Stehule <pavel.stehule@gmail.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@2ndQuadrant.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 intas $$ ... $$language plpgsql... other CREATE FUNCTION options as needed
...
)
query here ...
regards, tom lane
2012/7/10 Tom Lane <tgl@sss.pgh.pa.us>: > Dimitri Fontaine <dimitri@2ndQuadrant.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.stehule@gmail.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@2ndquadrant.fr>: > Pavel Stehule <pavel.stehule@gmail.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.stehule@gmail.com> writes:
> 2012/7/10 Dimitri Fontaine <dimitri@2ndquadrant.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.stehule@gmail.com> writes: > > 2012/7/10 Dimitri Fontaine <dimitri@2ndquadrant.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@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera <alvherre@commandprompt.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