From e717a9a18b2e34c9c40e5259ad4d31cd7e420750 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Wed, 7 Apr 2021 21:30:08 +0200 Subject: SQL-standard function body This adds support for writing CREATE FUNCTION and CREATE PROCEDURE statements for language SQL with a function body that conforms to the SQL standard and is portable to other implementations. Instead of the PostgreSQL-specific AS $$ string literal $$ syntax, this allows writing out the SQL statements making up the body unquoted, either as a single statement: CREATE FUNCTION add(a integer, b integer) RETURNS integer LANGUAGE SQL RETURN a + b; or as a block CREATE PROCEDURE insert_data(a integer, b integer) LANGUAGE SQL BEGIN ATOMIC INSERT INTO tbl VALUES (a); INSERT INTO tbl VALUES (b); END; The function body is parsed at function definition time and stored as expression nodes in a new pg_proc column prosqlbody. So at run time, no further parsing is required. However, this form does not support polymorphic arguments, because there is no more parse analysis done at call time. Dependencies between the function and the objects it uses are fully tracked. A new RETURN statement is introduced. This can only be used inside function bodies. Internally, it is treated much like a SELECT statement. psql needs some new intelligence to keep track of function body boundaries so that it doesn't send off statements when it sees semicolons that are inside a function body. Tested-by: Jaime Casanova Reviewed-by: Julien Rouhaud Discussion: https://www.postgresql.org/message-id/flat/1c11f1eb-f00c-43b7-799d-2d44132c02d7@2ndquadrant.com --- doc/src/sgml/catalogs.sgml | 10 +++ doc/src/sgml/ref/create_function.sgml | 125 +++++++++++++++++++++++++++++---- doc/src/sgml/ref/create_procedure.sgml | 61 ++++++++++++++-- 3 files changed, 179 insertions(+), 17 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index f103d914a62..2656786d1e6 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -6002,6 +6002,16 @@ SCRAM-SHA-256$<iteration count>:&l + + + prosqlbody pg_node_tree + + + Pre-parsed SQL function body. This will be used for language SQL + functions if the body is not specified as a string constant. + + + proconfig text[] diff --git a/doc/src/sgml/ref/create_function.sgml b/doc/src/sgml/ref/create_function.sgml index f1001615f4a..e43705d069c 100644 --- a/doc/src/sgml/ref/create_function.sgml +++ b/doc/src/sgml/ref/create_function.sgml @@ -38,6 +38,7 @@ CREATE [ OR REPLACE ] FUNCTION | SET configuration_parameter { TO value | = value | FROM CURRENT } | AS 'definition' | AS 'obj_file', 'link_symbol' + | sql_body } ... @@ -262,7 +263,9 @@ CREATE [ OR REPLACE ] FUNCTION The name of the language that the function is implemented in. It can be sql, c, internal, or the name of a user-defined - procedural language, e.g., plpgsql. Enclosing the + procedural language, e.g., plpgsql. The default is + sql if sql_body is specified. Enclosing the name in single quotes is deprecated and requires matching case. @@ -582,6 +585,44 @@ CREATE [ OR REPLACE ] FUNCTION + + sql_body + + + + The body of a LANGUAGE SQL function. This can + either be a single statement + +RETURN expression + + or a block + +BEGIN ATOMIC + statement; + statement; + ... + statement; +END + + + + + This is similar to writing the text of the function body as a string + constant (see definition above), but there + are some differences: This form only works for LANGUAGE + SQL, the string constant form works for all languages. This + form is parsed at function definition time, the string constant form is + parsed at execution time; therefore this form cannot support + polymorphic argument types and other constructs that are not resolvable + at function definition time. This form tracks dependencies between the + function and objects used in the function body, so DROP + ... CASCADE will work correctly, whereas the form using + string literals may leave dangling functions. Finally, this form is + more compatible with the SQL standard and other SQL implementations. + + + + @@ -667,6 +708,15 @@ CREATE FUNCTION add(integer, integer) RETURNS integer LANGUAGE SQL IMMUTABLE RETURNS NULL ON NULL INPUT; + + The same function written in a more SQL-conforming style, using argument + names and an unquoted body: + +CREATE FUNCTION add(a integer, b integer) RETURNS integer + LANGUAGE SQL + IMMUTABLE + RETURNS NULL ON NULL INPUT + RETURN a + b; @@ -797,23 +847,74 @@ COMMIT; Compatibility - A CREATE FUNCTION command is defined in the SQL standard. - The PostgreSQL version is similar but - not fully compatible. The attributes are not portable, neither are the - different available languages. + A CREATE FUNCTION command is defined in the SQL + standard. The PostgreSQL implementation can be + used in a compatible way but has many extensions. Conversely, the SQL + standard specifies a number of optional features that are not implemented + in PostgreSQL. - For compatibility with some other database systems, - argmode can be written - either before or after argname. - But only the first way is standard-compliant. + The following are important compatibility issues: + + + + + OR REPLACE is a PostgreSQL extension. + + + + + + For compatibility with some other database systems, argmode can be written either before or + after argname. But only + the first way is standard-compliant. + + + + + + For parameter defaults, the SQL standard specifies only the syntax with + the DEFAULT key word. The syntax with + = is used in T-SQL and Firebird. + + + + + + The SETOF modifier is a PostgreSQL extension. + + + + + + Only SQL is standardized as a language. + + + + + + All other attributes except CALLED ON NULL INPUT and + RETURNS NULL ON NULL INPUT are not standardized. + + + + + + For the body of LANGUAGE SQL functions, the SQL + standard only specifies the sql_body form. + + + - For parameter defaults, the SQL standard specifies only the syntax with - the DEFAULT key word. The syntax - with = is used in T-SQL and Firebird. + Simple LANGUAGE SQL functions can be written in a way + that is both standard-conforming and portable to other implementations. + More complex functions using advanced features, optimization attributes, or + other languages will necessarily be specific to PostgreSQL in a significant + way. diff --git a/doc/src/sgml/ref/create_procedure.sgml b/doc/src/sgml/ref/create_procedure.sgml index 6dbc0127194..2cd47d097f3 100644 --- a/doc/src/sgml/ref/create_procedure.sgml +++ b/doc/src/sgml/ref/create_procedure.sgml @@ -29,6 +29,7 @@ CREATE [ OR REPLACE ] PROCEDURE | SET configuration_parameter { TO value | = value | FROM CURRENT } | AS 'definition' | AS 'obj_file', 'link_symbol' + | sql_body } ... @@ -167,7 +168,9 @@ CREATE [ OR REPLACE ] PROCEDURE The name of the language that the procedure is implemented in. It can be sql, c, internal, or the name of a user-defined - procedural language, e.g., plpgsql. Enclosing the + procedural language, e.g., plpgsql. The default is + sql if sql_body is specified. Enclosing the name in single quotes is deprecated and requires matching case. @@ -304,6 +307,41 @@ CREATE [ OR REPLACE ] PROCEDURE + + + sql_body + + + + The body of a LANGUAGE SQL procedure. This should + be a block + +BEGIN ATOMIC + statement; + statement; + ... + statement; +END + + + + + This is similar to writing the text of the procedure body as a string + constant (see definition above), but there + are some differences: This form only works for LANGUAGE + SQL, the string constant form works for all languages. This + form is parsed at procedure definition time, the string constant form is + parsed at execution time; therefore this form cannot support + polymorphic argument types and other constructs that are not resolvable + at procedure definition time. This form tracks dependencies between the + procedure and objects used in the procedure body, so DROP + ... CASCADE will work correctly, whereas the form using + string literals may leave dangling procedures. Finally, this form is + more compatible with the SQL standard and other SQL implementations. + + + + @@ -323,6 +361,7 @@ CREATE [ OR REPLACE ] PROCEDURE Examples + CREATE PROCEDURE insert_data(a integer, b integer) LANGUAGE SQL @@ -330,9 +369,21 @@ AS $$ INSERT INTO tbl VALUES (a); INSERT INTO tbl VALUES (b); $$; - + + or + +CREATE PROCEDURE insert_data(a integer, b integer) +LANGUAGE SQL +BEGIN ATOMIC + INSERT INTO tbl VALUES (a); + INSERT INTO tbl VALUES (b); +END; + + and call like this: + CALL insert_data(1, 2); + @@ -340,9 +391,9 @@ CALL insert_data(1, 2); A CREATE PROCEDURE command is defined in the SQL - standard. The PostgreSQL version is similar but - not fully compatible. For details see - also . + standard. The PostgreSQL implementation can be + used in a compatible way but has many extensions. For details see also + . -- cgit v1.2.3