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 probin. 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.
Also, per SQL standard, LANGUAGE SQL is the default, so it does not
need to be specified anymore.
Note: Some parts of the patch look better under git diff -w (ignoring
whitespace changes) because if/else blocks were introduced around
existing code.
TODOs and discussion points:
- pg_dump is not yet supported. As a consequence, the pg_upgrade
tests don't pass yet. I'm thinking about changing pg_dump to use
pg_get_functiondef here instead of coding everything by hand. Some
initial experimenting showed that this would be possible with minimal
tweaking and it would surely be beneficial in the long run.
- The compiled function body is stored in the probin field of pg_proc.
This matches the historical split similar to adsrc/adbin, consrc/conbin,
but this has now been abandoned. Also, this field should ideally be of
type pg_node_tree, so reusing probin for that is probably not good.
Seems like a new field might be best.
- More test coverage is needed. Surprisingly, there wasn't actually any
test AFAICT that just creates and SQL function and runs it. Most of
that code is tested incidentally, but there is very little or no
targeted testing of this functionality.
- Some of the changes in pg_proc.c, functioncmds.c, and functions.c in
particular were jammed in and could use some reorganization after the
basic ideas are solidified.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From 9611034103216bf57a76546cc212786fa8fe5b73 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Tue, 30 Jun 2020 19:42:08 +0200
Subject: [PATCH v1] 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 probin. 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.
Also, per SQL standard, LANGUAGE SQL is the default, so it does not
need to be specified anymore.
TODO: pg_dump is not yet supported. As a consequence, the pg_upgrade
tests don't pass yet.
---
doc/src/sgml/ref/create_function.sgml | 126 +++++++++++--
doc/src/sgml/ref/create_procedure.sgml | 62 ++++++-
src/backend/catalog/pg_proc.c | 148 ++++++++-------
src/backend/commands/aggregatecmds.c | 2 +
src/backend/commands/functioncmds.c | 96 +++++++---
src/backend/executor/functions.c | 79 ++++----
src/backend/nodes/copyfuncs.c | 15 ++
src/backend/nodes/equalfuncs.c | 13 ++
src/backend/nodes/outfuncs.c | 12 ++
src/backend/nodes/readfuncs.c | 1 +
src/backend/optimizer/util/clauses.c | 25 ++-
src/backend/parser/analyze.c | 35 ++++
src/backend/parser/gram.y | 126 ++++++++++---
src/backend/tcop/postgres.c | 3 +-
src/backend/utils/adt/ruleutils.c | 76 +++++++-
src/fe_utils/psqlscan.l | 23 ++-
src/include/commands/defrem.h | 2 +
src/include/executor/functions.h | 15 ++
src/include/fe_utils/psqlscan_int.h | 2 +
src/include/nodes/nodes.h | 1 +
src/include/nodes/parsenodes.h | 13 ++
src/include/parser/kwlist.h | 2 +
src/include/tcop/tcopprot.h | 1 +
src/interfaces/ecpg/preproc/ecpg.addons | 6 +
src/interfaces/ecpg/preproc/ecpg.trailer | 4 +-
.../regress/expected/create_function_3.out | 170 +++++++++++++++++-
.../regress/expected/create_procedure.out | 58 ++++++
src/test/regress/sql/create_function_3.sql | 77 +++++++-
src/test/regress/sql/create_procedure.sql | 26 +++
29 files changed, 1041 insertions(+), 178 deletions(-)
diff --git a/doc/src/sgml/ref/create_function.sgml
b/doc/src/sgml/ref/create_function.sgml
index f81cedc823..f7cc428773 100644
--- a/doc/src/sgml/ref/create_function.sgml
+++ b/doc/src/sgml/ref/create_function.sgml
@@ -38,6 +38,7 @@
| SET <replaceable class="parameter">configuration_parameter</replaceable>
{ TO <replaceable class="parameter">value</replaceable> | = <replaceable
class="parameter">value</replaceable> | FROM CURRENT }
| AS '<replaceable class="parameter">definition</replaceable>'
| AS '<replaceable class="parameter">obj_file</replaceable>',
'<replaceable class="parameter">link_symbol</replaceable>'
+ | <replaceable class="parameter">sql_body</replaceable>
} ...
</synopsis>
</refsynopsisdiv>
@@ -257,8 +258,9 @@ <title>Parameters</title>
The name of the language that the function is implemented in.
It can be <literal>sql</literal>, <literal>c</literal>,
<literal>internal</literal>, or the name of a user-defined
- procedural language, e.g. <literal>plpgsql</literal>. Enclosing the
- name in single quotes is deprecated and requires matching case.
+ procedural language, e.g. <literal>plpgsql</literal>. The default is
+ <literal>sql</literal>. Enclosing the name in single quotes is
+ deprecated and requires matching case.
</para>
</listitem>
</varlistentry>
@@ -577,6 +579,44 @@ <title>Parameters</title>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><replaceable class="parameter">sql_body</replaceable></term>
+
+ <listitem>
+ <para>
+ The body of a <literal>LANGUAGE SQL</literal> function. This can
+ either be a single statement
+<programlisting>
+RETURN <replaceable>expression</replaceable>
+</programlisting>
+ or a block
+<programlisting>
+BEGIN ATOMIC
+ <replaceable>statement</replaceable>;
+ <replaceable>statement</replaceable>;
+ ...
+ <replaceable>statement</replaceable>;
+END
+</programlisting>
+ </para>
+
+ <para>
+ This is similar to writing the text of the function body as a string
+ constant (see <replaceable>definition</replaceable> above), but there
+ are some differences: This form only works for <literal>LANGUAGE
+ SQL</literal>, 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 <literal>DROP
+ ... CASCADE</literal> 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.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
<para>
@@ -669,6 +709,15 @@ <title>Examples</title>
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;
+</programlisting>
+ The same function written in a more SQL-conforming style, using argument
+ names and an unquoted body:
+<programlisting>
+CREATE FUNCTION add(a integer, b integer) RETURNS integer
+ LANGUAGE SQL
+ IMMUTABLE
+ RETURNS NULL ON NULL INPUT
+ RETURN a + b;
</programlisting>
</para>
@@ -799,23 +848,74 @@ <title>Writing <literal>SECURITY DEFINER</literal>
Functions Safely</title>
<title>Compatibility</title>
<para>
- A <command>CREATE FUNCTION</command> command is defined in the SQL standard.
- The <productname>PostgreSQL</productname> version is similar but
- not fully compatible. The attributes are not portable, neither are the
- different available languages.
+ A <command>CREATE FUNCTION</command> command is defined in the SQL
+ standard. The <productname>PostgreSQL</productname> 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 <productname>PostgreSQL</productname>.
</para>
<para>
- For compatibility with some other database systems,
- <replaceable class="parameter">argmode</replaceable> can be written
- either before or after <replaceable class="parameter">argname</replaceable>.
- But only the first way is standard-compliant.
+ The following are important compatibility issues:
+
+ <itemizedlist>
+ <listitem>
+ <para>
+ <literal>OR REPLACE</literal> is a PostgreSQL extension.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ For compatibility with some other database systems, <replaceable
+ class="parameter">argmode</replaceable> can be written either before or
+ after <replaceable class="parameter">argname</replaceable>. But only
+ the first way is standard-compliant.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ For parameter defaults, the SQL standard specifies only the syntax with
+ the <literal>DEFAULT</literal> key word. The syntax with
+ <literal>=</literal> is used in T-SQL and Firebird.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The <literal>SETOF</literal> modifier is a PostgreSQL extension.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Only <literal>SQL</literal> is standardized as a language.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ All other attributes except <literal>CALLED ON NULL INPUT</literal> and
+ <literal>RETURNS NULL ON NULL INPUT</literal> are not standardized.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ For the body of <literal>LANGUAGE SQL</literal> functions, the SQL
+ standard only specifies the <replaceable>sql_body</replaceable> form.
+ </para>
+ </listitem>
+ </itemizedlist>
</para>
<para>
- For parameter defaults, the SQL standard specifies only the syntax with
- the <literal>DEFAULT</literal> key word. The syntax
- with <literal>=</literal> is used in T-SQL and Firebird.
+ Simple <literal>LANGUAGE SQL</literal> 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.
</para>
</refsect1>
diff --git a/doc/src/sgml/ref/create_procedure.sgml
b/doc/src/sgml/ref/create_procedure.sgml
index 0ea6513cb5..9a84132a2b 100644
--- a/doc/src/sgml/ref/create_procedure.sgml
+++ b/doc/src/sgml/ref/create_procedure.sgml
@@ -29,6 +29,7 @@
| SET <replaceable class="parameter">configuration_parameter</replaceable>
{ TO <replaceable class="parameter">value</replaceable> | = <replaceable
class="parameter">value</replaceable> | FROM CURRENT }
| AS '<replaceable class="parameter">definition</replaceable>'
| AS '<replaceable class="parameter">obj_file</replaceable>',
'<replaceable class="parameter">link_symbol</replaceable>'
+ | <replaceable class="parameter">sql_body</replaceable>
} ...
</synopsis>
</refsynopsisdiv>
@@ -164,8 +165,9 @@ <title>Parameters</title>
The name of the language that the procedure is implemented in.
It can be <literal>sql</literal>, <literal>c</literal>,
<literal>internal</literal>, or the name of a user-defined
- procedural language, e.g. <literal>plpgsql</literal>. Enclosing the
- name in single quotes is deprecated and requires matching case.
+ procedural language, e.g. <literal>plpgsql</literal>. The default is
+ <literal>sql</literal>. Enclosing the name in single quotes is
+ deprecated and requires matching case.
</para>
</listitem>
</varlistentry>
@@ -301,6 +303,41 @@ <title>Parameters</title>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">sql_body</replaceable></term>
+
+ <listitem>
+ <para>
+ The body of a <literal>LANGUAGE SQL</literal> procedure. This should
+ be a block
+<programlisting>
+BEGIN ATOMIC
+ <replaceable>statement</replaceable>;
+ <replaceable>statement</replaceable>;
+ ...
+ <replaceable>statement</replaceable>;
+END
+</programlisting>
+ </para>
+
+ <para>
+ This is similar to writing the text of the procedure body as a string
+ constant (see <replaceable>definition</replaceable> above), but there
+ are some differences: This form only works for <literal>LANGUAGE
+ SQL</literal>, 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 <literal>DROP
+ ... CASCADE</literal> 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.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</refsect1>
@@ -320,6 +357,7 @@ <title>Notes</title>
<refsect1 id="sql-createprocedure-examples">
<title>Examples</title>
+ <para>
<programlisting>
CREATE PROCEDURE insert_data(a integer, b integer)
LANGUAGE SQL
@@ -327,9 +365,21 @@ <title>Examples</title>
INSERT INTO tbl VALUES (a);
INSERT INTO tbl VALUES (b);
$$;
-
+</programlisting>
+ or
+<programlisting>
+CREATE PROCEDURE insert_data(a integer, b integer)
+LANGUAGE SQL
+BEGIN ATOMIC
+ INSERT INTO tbl VALUES (a);
+ INSERT INTO tbl VALUES (b);
+END;
+</programlisting>
+ and call like this:
+<programlisting>
CALL insert_data(1, 2);
</programlisting>
+ </para>
</refsect1>
<refsect1 id="sql-createprocedure-compat">
@@ -337,9 +387,9 @@ <title>Compatibility</title>
<para>
A <command>CREATE PROCEDURE</command> command is defined in the SQL
- standard. The <productname>PostgreSQL</productname> version is similar but
- not fully compatible. For details see
- also <xref linkend="sql-createfunction"/>.
+ standard. The <productname>PostgreSQL</productname> implementation can be
+ used in a compatible way but has many extensions. For details see also
+ <xref linkend="sql-createfunction"/>.
</para>
</refsect1>
diff --git a/src/backend/catalog/pg_proc.c b/src/backend/catalog/pg_proc.c
index 6cdda35d1c..4474a73d25 100644
--- a/src/backend/catalog/pg_proc.c
+++ b/src/backend/catalog/pg_proc.c
@@ -32,6 +32,7 @@
#include "mb/pg_wchar.h"
#include "miscadmin.h"
#include "nodes/nodeFuncs.h"
+#include "parser/analyze.h"
#include "parser/parse_coerce.h"
#include "parser/parse_type.h"
#include "tcop/pquery.h"
@@ -118,8 +119,6 @@ ProcedureCreate(const char *procedureName,
/*
* sanity checks
*/
- Assert(PointerIsValid(prosrc));
-
parameterCount = parameterTypes->dim1;
if (parameterCount < 0 || parameterCount > FUNC_MAX_ARGS)
ereport(ERROR,
@@ -330,7 +329,10 @@ ProcedureCreate(const char *procedureName,
values[Anum_pg_proc_protrftypes - 1] = trftypes;
else
nulls[Anum_pg_proc_protrftypes - 1] = true;
- values[Anum_pg_proc_prosrc - 1] = CStringGetTextDatum(prosrc);
+ if (prosrc)
+ values[Anum_pg_proc_prosrc - 1] = CStringGetTextDatum(prosrc);
+ else
+ nulls[Anum_pg_proc_prosrc - 1] = true;
if (probin)
values[Anum_pg_proc_probin - 1] = CStringGetTextDatum(probin);
else
@@ -648,6 +650,10 @@ ProcedureCreate(const char *procedureName,
recordDependencyOn(&myself, &referenced, DEPENDENCY_NORMAL);
}
+ /* dependency on SQL routine body */
+ if (languageObjectId == SQLlanguageId && probin)
+ recordDependencyOnExpr(&myself, stringToNode(probin), NIL,
DEPENDENCY_NORMAL);
+
/* dependency on owner */
if (!is_update)
recordDependencyOnOwner(ProcedureRelationId, retval, proowner);
@@ -816,16 +822,7 @@ fmgr_sql_validator(PG_FUNCTION_ARGS)
Oid funcoid = PG_GETARG_OID(0);
HeapTuple tuple;
Form_pg_proc proc;
- List *raw_parsetree_list;
- List *querytree_list;
- ListCell *lc;
- bool isnull;
- Datum tmp;
- char *prosrc;
- parse_error_callback_arg callback_arg;
- ErrorContextCallback sqlerrcontext;
bool haspolyarg;
- int i;
if (!CheckFunctionValidatorAccess(fcinfo->flinfo->fn_oid, funcoid))
PG_RETURN_VOID();
@@ -849,7 +846,7 @@ fmgr_sql_validator(PG_FUNCTION_ARGS)
/* Disallow pseudotypes in arguments */
/* except for polymorphic */
haspolyarg = false;
- for (i = 0; i < proc->pronargs; i++)
+ for (int i = 0; i < proc->pronargs; i++)
{
if (get_typtype(proc->proargtypes.values[i]) == TYPTYPE_PSEUDO)
{
@@ -866,72 +863,93 @@ fmgr_sql_validator(PG_FUNCTION_ARGS)
/* Postpone body checks if !check_function_bodies */
if (check_function_bodies)
{
+ Datum tmp;
+ bool isnull;
+ List *querytree_list = NIL;
+
tmp = SysCacheGetAttr(PROCOID, tuple, Anum_pg_proc_prosrc,
&isnull);
if (isnull)
- elog(ERROR, "null prosrc");
-
- prosrc = TextDatumGetCString(tmp);
+ {
+ char *probin;
- /*
- * Setup error traceback support for ereport().
- */
- callback_arg.proname = NameStr(proc->proname);
- callback_arg.prosrc = prosrc;
+ tmp = SysCacheGetAttr(PROCOID, tuple,
Anum_pg_proc_probin, &isnull);
+ if (isnull)
+ elog(ERROR, "null probin and prosrc");
- sqlerrcontext.callback = sql_function_parse_error_callback;
- sqlerrcontext.arg = (void *) &callback_arg;
- sqlerrcontext.previous = error_context_stack;
- error_context_stack = &sqlerrcontext;
+ probin = TextDatumGetCString(tmp);
+ querytree_list = castNode(List, stringToNode(probin));
+ }
+ else
+ {
+ char *prosrc;
+ List *raw_parsetree_list;
+ parse_error_callback_arg callback_arg;
+ ErrorContextCallback sqlerrcontext;
- /*
- * We can't do full prechecking of the function definition if
there
- * are any polymorphic input types, because actual datatypes of
- * expression results will be unresolvable. The check will be
done at
- * runtime instead.
- *
- * We can run the text through the raw parser though; this will
at
- * least catch silly syntactic errors.
- */
- raw_parsetree_list = pg_parse_query(prosrc);
+ prosrc = TextDatumGetCString(tmp);
- if (!haspolyarg)
- {
/*
- * OK to do full precheck: analyze and rewrite the
queries, then
- * verify the result type.
+ * Setup error traceback support for ereport().
*/
- SQLFunctionParseInfoPtr pinfo;
- Oid rettype;
- TupleDesc rettupdesc;
+ callback_arg.proname = NameStr(proc->proname);
+ callback_arg.prosrc = prosrc;
- /* But first, set up parameter information */
- pinfo = prepare_sql_fn_parse_info(tuple, NULL,
InvalidOid);
+ sqlerrcontext.callback =
sql_function_parse_error_callback;
+ sqlerrcontext.arg = (void *) &callback_arg;
+ sqlerrcontext.previous = error_context_stack;
+ error_context_stack = &sqlerrcontext;
- querytree_list = NIL;
- foreach(lc, raw_parsetree_list)
+ /*
+ * We can't do full prechecking of the function
definition if there
+ * are any polymorphic input types, because actual
datatypes of
+ * expression results will be unresolvable. The check
will be done at
+ * runtime instead.
+ *
+ * We can run the text through the raw parser though;
this will at
+ * least catch silly syntactic errors.
+ */
+ raw_parsetree_list = pg_parse_query(prosrc);
+
+ if (!haspolyarg)
{
- RawStmt *parsetree = lfirst_node(RawStmt,
lc);
- List *querytree_sublist;
-
- querytree_sublist =
pg_analyze_and_rewrite_params(parsetree,
-
prosrc,
-
(ParserSetupHook)
sql_fn_parser_setup,
-
pinfo,
-
NULL);
- querytree_list = list_concat(querytree_list,
-
querytree_sublist);
+ /*
+ * OK to do full precheck: analyze and rewrite
the queries, then
+ * verify the result type.
+ */
+ ListCell *lc;
+ SQLFunctionParseInfoPtr pinfo;
+ Oid rettype;
+ TupleDesc rettupdesc;
+
+ /* But first, set up parameter information */
+ pinfo = prepare_sql_fn_parse_info(tuple, NULL,
InvalidOid);
+
+ querytree_list = NIL;
+ foreach(lc, raw_parsetree_list)
+ {
+ RawStmt *parsetree =
lfirst_node(RawStmt, lc);
+ List *querytree_sublist;
+
+ querytree_sublist =
pg_analyze_and_rewrite_params(parsetree,
+
prosrc,
+
(ParserSetupHook)
sql_fn_parser_setup,
+
pinfo,
+
NULL);
+ querytree_list =
list_concat(querytree_list,
+
querytree_sublist);
+ }
+
+ check_sql_fn_statements(querytree_list);
+
+ (void) get_func_result_type(funcoid, &rettype,
&rettupdesc);
+
+ (void) check_sql_fn_retval(querytree_list,
+
rettype, rettupdesc,
+
false, NULL);
}
- check_sql_fn_statements(querytree_list);
-
- (void) get_func_result_type(funcoid, &rettype,
&rettupdesc);
-
- (void) check_sql_fn_retval(querytree_list,
-
rettype, rettupdesc,
-
false, NULL);
+ error_context_stack = sqlerrcontext.previous;
}
-
- error_context_stack = sqlerrcontext.previous;
}
ReleaseSysCache(tuple);
diff --git a/src/backend/commands/aggregatecmds.c
b/src/backend/commands/aggregatecmds.c
index 6bf54e64f8..26b3fa27de 100644
--- a/src/backend/commands/aggregatecmds.c
+++ b/src/backend/commands/aggregatecmds.c
@@ -313,9 +313,11 @@ DefineAggregate(ParseState *pstate,
InvalidOid,
OBJECT_AGGREGATE,
¶meterTypes,
+
NULL,
&allParameterTypes,
¶meterModes,
¶meterNames,
+
NULL,
¶meterDefaults,
&variadicArgType,
&requiredResultType);
diff --git a/src/backend/commands/functioncmds.c
b/src/backend/commands/functioncmds.c
index 1b5bdcec8b..859bc72974 100644
--- a/src/backend/commands/functioncmds.c
+++ b/src/backend/commands/functioncmds.c
@@ -53,9 +53,11 @@
#include "commands/proclang.h"
#include "executor/execdesc.h"
#include "executor/executor.h"
+#include "executor/functions.h"
#include "funcapi.h"
#include "miscadmin.h"
#include "optimizer/optimizer.h"
+#include "parser/analyze.h"
#include "parser/parse_coerce.h"
#include "parser/parse_collate.h"
#include "parser/parse_expr.h"
@@ -186,9 +188,11 @@ interpret_function_parameter_list(ParseState *pstate,
Oid
languageOid,
ObjectType
objtype,
oidvector
**parameterTypes,
+ List
**parameterTypes_list,
ArrayType
**allParameterTypes,
ArrayType
**parameterModes,
ArrayType
**parameterNames,
+ List
**inParameterNames_list,
List
**parameterDefaults,
Oid
*variadicArgType,
Oid
*requiredResultType)
@@ -300,6 +304,8 @@ interpret_function_parameter_list(ParseState *pstate,
errmsg("VARIADIC parameter
must be the last input parameter")));
inTypes[inCount++] = toid;
isinput = true;
+ if (parameterTypes_list)
+ *parameterTypes_list =
lappend_oid(*parameterTypes_list, toid);
}
/* handle output parameters */
@@ -376,6 +382,9 @@ interpret_function_parameter_list(ParseState *pstate,
have_names = true;
}
+ if (inParameterNames_list)
+ *inParameterNames_list =
lappend(*inParameterNames_list, makeString(fp->name ? fp->name : pstrdup("")));
+
if (fp->defexpr)
{
Node *def;
@@ -790,28 +799,10 @@ compute_function_attributes(ParseState *pstate,
defel->defname);
}
- /* process required items */
if (as_item)
*as = (List *) as_item->arg;
- else
- {
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_FUNCTION_DEFINITION),
- errmsg("no function body specified")));
- *as = NIL; /* keep compiler quiet
*/
- }
-
if (language_item)
*language = strVal(language_item->arg);
- else
- {
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_FUNCTION_DEFINITION),
- errmsg("no language specified")));
- *language = NULL; /* keep compiler quiet */
- }
-
- /* process optional items */
if (transform_item)
*transform = transform_item->arg;
if (windowfunc_item)
@@ -860,10 +851,19 @@ compute_function_attributes(ParseState *pstate,
*/
static void
interpret_AS_clause(Oid languageOid, const char *languageName,
- char *funcname, List *as,
+ char *funcname, List *as, List
*sql_body,
+ List *parameterTypes, List
*inParameterNames,
char **prosrc_str_p, char
**probin_str_p)
{
- Assert(as != NIL);
+ if (sql_body && as)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_FUNCTION_DEFINITION),
+ errmsg("duplicate function body specified")));
+
+ if (sql_body && languageOid != SQLlanguageId)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_FUNCTION_DEFINITION),
+ errmsg("inline SQL function body only valid
for language SQL")));
if (languageOid == ClanguageId)
{
@@ -885,6 +885,53 @@ interpret_AS_clause(Oid languageOid, const char
*languageName,
*prosrc_str_p = funcname;
}
}
+ else if (sql_body)
+ {
+ List *transformed_stmts = NIL;
+ ListCell *lc;
+ SQLFunctionParseInfoPtr pinfo;
+
+ pinfo = (SQLFunctionParseInfoPtr)
palloc0(sizeof(SQLFunctionParseInfo));
+
+ pinfo->fname = funcname;
+ pinfo->nargs = list_length(parameterTypes);
+ pinfo->argtypes = (Oid *) palloc(pinfo->nargs * sizeof(Oid));
+ pinfo->argnames = (char **) palloc(pinfo->nargs * sizeof(char
*));
+ for (int i = 0; i < list_length(parameterTypes); i++)
+ {
+ char *s = strVal(list_nth(inParameterNames, i));
+
+ pinfo->argtypes[i] = list_nth_oid(parameterTypes, i);
+ if (IsPolymorphicType(pinfo->argtypes[i]))
+ ereport(ERROR,
+
(errcode(ERRCODE_INVALID_FUNCTION_DEFINITION),
+ errmsg("SQL function with
unquoted function body cannot have polymorphic arguments")));
+
+ if (s[0] != '\0')
+ pinfo->argnames[i] = s;
+ else
+ pinfo->argnames[i] = NULL;
+ }
+
+ foreach(lc, sql_body)
+ {
+ Node *stmt = lfirst(lc);
+ Query *q;
+ ParseState *pstate = make_parsestate(NULL);
+
+ /* ignore NULL statement; see gram.y */
+ if (!stmt)
+ continue;
+
+ sql_fn_parser_setup(pstate, pinfo);
+ q = transformStmt(pstate, stmt);
+ transformed_stmts = lappend(transformed_stmts, q);
+ free_parsestate(pstate);
+ }
+
+ *probin_str_p = nodeToString(transformed_stmts);
+ *prosrc_str_p = NULL;
+ }
else
{
/* Everything else wants the given string in prosrc. */
@@ -933,9 +980,11 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt
*stmt)
Oid namespaceId;
AclResult aclresult;
oidvector *parameterTypes;
+ List *parameterTypes_list = NIL;
ArrayType *allParameterTypes;
ArrayType *parameterModes;
ArrayType *parameterNames;
+ List *inParameterNames_list = NIL;
List *parameterDefaults;
Oid variadicArgType;
List *trftypes_list = NIL;
@@ -966,6 +1015,8 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt
*stmt)
get_namespace_name(namespaceId));
/* Set default attributes */
+ as_clause = NULL;
+ language = "sql";
isWindowFunc = false;
isStrict = false;
security = false;
@@ -1057,9 +1108,11 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt
*stmt)
languageOid,
stmt->is_procedure ? OBJECT_PROCEDURE : OBJECT_FUNCTION,
¶meterTypes,
+
¶meterTypes_list,
&allParameterTypes,
¶meterModes,
¶meterNames,
+
&inParameterNames_list,
¶meterDefaults,
&variadicArgType,
&requiredResultType);
@@ -1116,7 +1169,8 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt
*stmt)
trftypes = NULL;
}
- interpret_AS_clause(languageOid, language, funcname, as_clause,
+ interpret_AS_clause(languageOid, language, funcname, as_clause,
stmt->sql_body,
+ parameterTypes_list,
inParameterNames_list,
&prosrc_str, &probin_str);
/*
diff --git a/src/backend/executor/functions.c b/src/backend/executor/functions.c
index f940f48c6d..911149aa27 100644
--- a/src/backend/executor/functions.c
+++ b/src/backend/executor/functions.c
@@ -26,6 +26,7 @@
#include "parser/parse_coerce.h"
#include "parser/parse_collate.h"
#include "parser/parse_func.h"
+#include "rewrite/rewriteHandler.h"
#include "storage/proc.h"
#include "tcop/utility.h"
#include "utils/builtins.h"
@@ -128,21 +129,6 @@ typedef struct
typedef SQLFunctionCache *SQLFunctionCachePtr;
-/*
- * Data structure needed by the parser callback hooks to resolve parameter
- * references during parsing of a SQL function's body. This is separate from
- * SQLFunctionCache since we sometimes do parsing separately from execution.
- */
-typedef struct SQLFunctionParseInfo
-{
- char *fname; /* function's name */
- int nargs; /* number of input
arguments */
- Oid *argtypes; /* resolved types of input
arguments */
- char **argnames; /* names of input arguments; NULL if
none */
- /* Note that argnames[i] can be NULL, if some args are unnamed */
- Oid collation; /* function's input
collation, if known */
-} SQLFunctionParseInfo;
-
/* non-export function prototypes */
static Node *sql_fn_param_ref(ParseState *pstate, ParamRef *pref);
@@ -606,7 +592,6 @@ init_sql_fcache(FunctionCallInfo fcinfo, Oid collation,
bool lazyEvalOK)
HeapTuple procedureTuple;
Form_pg_proc procedureStruct;
SQLFunctionCachePtr fcache;
- List *raw_parsetree_list;
List *queryTree_list;
List *flat_query_list;
List *resulttlist;
@@ -682,9 +667,6 @@ init_sql_fcache(FunctionCallInfo fcinfo, Oid collation,
bool lazyEvalOK)
procedureTuple,
Anum_pg_proc_prosrc,
&isNull);
- if (isNull)
- elog(ERROR, "null prosrc for function %u", foid);
- fcache->src = TextDatumGetCString(tmp);
/*
* Parse and rewrite the queries in the function text. Use sublists to
@@ -701,22 +683,55 @@ init_sql_fcache(FunctionCallInfo fcinfo, Oid collation,
bool lazyEvalOK)
* but we'll not worry about it until the module is rewritten to use
* plancache.c.
*/
- raw_parsetree_list = pg_parse_query(fcache->src);
-
queryTree_list = NIL;
flat_query_list = NIL;
- foreach(lc, raw_parsetree_list)
+ if (isNull)
+ {
+ char *binstr;
+ List *stored_query_list;
+
+ tmp = SysCacheGetAttr(PROCOID,
+ procedureTuple,
+ Anum_pg_proc_probin,
+ &isNull);
+ if (isNull)
+ elog(ERROR, "null prosrc and probin for function %u",
foid);
+
+ binstr = TextDatumGetCString(tmp);
+ stored_query_list = stringToNode(binstr);
+
+ foreach(lc, stored_query_list)
+ {
+ Query *parsetree = lfirst_node(Query, lc);
+ List *queryTree_sublist;
+
+ AcquireRewriteLocks(parsetree, true, false);
+ queryTree_sublist = pg_rewrite_query(parsetree);
+ queryTree_list = lappend(queryTree_list,
queryTree_sublist);
+ flat_query_list = list_concat(flat_query_list,
queryTree_sublist);
+ }
+ }
+ else
{
- RawStmt *parsetree = lfirst_node(RawStmt, lc);
- List *queryTree_sublist;
-
- queryTree_sublist = pg_analyze_and_rewrite_params(parsetree,
-
fcache->src,
-
(ParserSetupHook) sql_fn_parser_setup,
-
fcache->pinfo,
-
NULL);
- queryTree_list = lappend(queryTree_list, queryTree_sublist);
- flat_query_list = list_concat(flat_query_list,
queryTree_sublist);
+ List *raw_parsetree_list;
+
+ fcache->src = TextDatumGetCString(tmp);
+
+ raw_parsetree_list = pg_parse_query(fcache->src);
+
+ foreach(lc, raw_parsetree_list)
+ {
+ RawStmt *parsetree = lfirst_node(RawStmt, lc);
+ List *queryTree_sublist;
+
+ queryTree_sublist =
pg_analyze_and_rewrite_params(parsetree,
+
fcache->src,
+
(ParserSetupHook) sql_fn_parser_setup,
+
fcache->pinfo,
+
NULL);
+ queryTree_list = lappend(queryTree_list,
queryTree_sublist);
+ flat_query_list = list_concat(flat_query_list,
queryTree_sublist);
+ }
}
check_sql_fn_statements(flat_query_list);
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index d8cf87e6d0..1f8b6b2d9c 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -3069,6 +3069,7 @@ _copyQuery(const Query *from)
COPY_SCALAR_FIELD(hasModifyingCTE);
COPY_SCALAR_FIELD(hasForUpdate);
COPY_SCALAR_FIELD(hasRowSecurity);
+ COPY_SCALAR_FIELD(isReturn);
COPY_NODE_FIELD(cteList);
COPY_NODE_FIELD(rtable);
COPY_NODE_FIELD(jointree);
@@ -3197,6 +3198,16 @@ _copySetOperationStmt(const SetOperationStmt *from)
return newnode;
}
+static ReturnStmt *
+_copyReturnStmt(const ReturnStmt *from)
+{
+ ReturnStmt *newnode = makeNode(ReturnStmt);
+
+ COPY_NODE_FIELD(returnval);
+
+ return newnode;
+}
+
static AlterTableStmt *
_copyAlterTableStmt(const AlterTableStmt *from)
{
@@ -3573,6 +3584,7 @@ _copyCreateFunctionStmt(const CreateFunctionStmt *from)
COPY_NODE_FIELD(parameters);
COPY_NODE_FIELD(returnType);
COPY_NODE_FIELD(options);
+ COPY_NODE_FIELD(sql_body);
return newnode;
}
@@ -5226,6 +5238,9 @@ copyObjectImpl(const void *from)
case T_SetOperationStmt:
retval = _copySetOperationStmt(from);
break;
+ case T_ReturnStmt:
+ retval = _copyReturnStmt(from);
+ break;
case T_AlterTableStmt:
retval = _copyAlterTableStmt(from);
break;
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 627b026b19..44d2f31bd3 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -966,6 +966,7 @@ _equalQuery(const Query *a, const Query *b)
COMPARE_SCALAR_FIELD(hasModifyingCTE);
COMPARE_SCALAR_FIELD(hasForUpdate);
COMPARE_SCALAR_FIELD(hasRowSecurity);
+ COMPARE_SCALAR_FIELD(isReturn);
COMPARE_NODE_FIELD(cteList);
COMPARE_NODE_FIELD(rtable);
COMPARE_NODE_FIELD(jointree);
@@ -1082,6 +1083,14 @@ _equalSetOperationStmt(const SetOperationStmt *a, const
SetOperationStmt *b)
return true;
}
+static bool
+_equalReturnStmt(const ReturnStmt *a, const ReturnStmt *b)
+{
+ COMPARE_NODE_FIELD(returnval);
+
+ return true;
+}
+
static bool
_equalAlterTableStmt(const AlterTableStmt *a, const AlterTableStmt *b)
{
@@ -1394,6 +1403,7 @@ _equalCreateFunctionStmt(const CreateFunctionStmt *a,
const CreateFunctionStmt *
COMPARE_NODE_FIELD(parameters);
COMPARE_NODE_FIELD(returnType);
COMPARE_NODE_FIELD(options);
+ COMPARE_NODE_FIELD(sql_body);
return true;
}
@@ -3278,6 +3288,9 @@ equal(const void *a, const void *b)
case T_SetOperationStmt:
retval = _equalSetOperationStmt(a, b);
break;
+ case T_ReturnStmt:
+ retval = _equalReturnStmt(a, b);
+ break;
case T_AlterTableStmt:
retval = _equalAlterTableStmt(a, b);
break;
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index e2f177515d..2153fcf59b 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -2758,6 +2758,14 @@ _outSelectStmt(StringInfo str, const SelectStmt *node)
WRITE_NODE_FIELD(rarg);
}
+static void
+_outReturnStmt(StringInfo str, const ReturnStmt *node)
+{
+ WRITE_NODE_TYPE("RETURN");
+
+ WRITE_NODE_FIELD(returnval);
+}
+
static void
_outFuncCall(StringInfo str, const FuncCall *node)
{
@@ -2946,6 +2954,7 @@ _outQuery(StringInfo str, const Query *node)
WRITE_BOOL_FIELD(hasModifyingCTE);
WRITE_BOOL_FIELD(hasForUpdate);
WRITE_BOOL_FIELD(hasRowSecurity);
+ WRITE_BOOL_FIELD(isReturn);
WRITE_NODE_FIELD(cteList);
WRITE_NODE_FIELD(rtable);
WRITE_NODE_FIELD(jointree);
@@ -4196,6 +4205,9 @@ outNode(StringInfo str, const void *obj)
case T_SelectStmt:
_outSelectStmt(str, obj);
break;
+ case T_ReturnStmt:
+ _outReturnStmt(str, obj);
+ break;
case T_ColumnDef:
_outColumnDef(str, obj);
break;
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
index 42050ab719..c6d0009820 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -263,6 +263,7 @@ _readQuery(void)
READ_BOOL_FIELD(hasModifyingCTE);
READ_BOOL_FIELD(hasForUpdate);
READ_BOOL_FIELD(hasRowSecurity);
+ READ_BOOL_FIELD(isReturn);
READ_NODE_FIELD(cteList);
READ_NODE_FIELD(rtable);
READ_NODE_FIELD(jointree);
diff --git a/src/backend/optimizer/util/clauses.c
b/src/backend/optimizer/util/clauses.c
index 0c6fe0115a..531adad3d4 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -4439,7 +4439,22 @@ inline_function(Oid funcid, Oid result_type, Oid
result_collid,
Anum_pg_proc_prosrc,
&isNull);
if (isNull)
- elog(ERROR, "null prosrc for function %u", funcid);
+ {
+ char *probin;
+ List *querytree_list;
+
+ tmp = SysCacheGetAttr(PROCOID, func_tuple, Anum_pg_proc_probin,
&isNull);
+ if (isNull)
+ elog(ERROR, "null probin and prosrc");
+
+ probin = TextDatumGetCString(tmp);
+ querytree_list = castNode(List, stringToNode(probin));
+ if (list_length(querytree_list) != 1)
+ goto fail;
+ querytree = linitial(querytree_list);
+ }
+ else
+ {
src = TextDatumGetCString(tmp);
/*
@@ -4497,6 +4512,7 @@ inline_function(Oid funcid, Oid result_type, Oid
result_collid,
querytree = transformTopLevelStmt(pstate, linitial(raw_parsetree_list));
free_parsestate(pstate);
+ }
/*
* The single command must be a simple "SELECT expression".
@@ -4984,7 +5000,11 @@ inline_set_returning_function(PlannerInfo *root,
RangeTblEntry *rte)
Anum_pg_proc_prosrc,
&isNull);
if (isNull)
- elog(ERROR, "null prosrc for function %u", func_oid);
+ {
+ goto fail; // TODO
+ }
+ else
+ {
src = TextDatumGetCString(tmp);
/*
@@ -5036,6 +5056,7 @@ inline_set_returning_function(PlannerInfo *root,
RangeTblEntry *rte)
if (list_length(querytree_list) != 1)
goto fail;
querytree = linitial(querytree_list);
+ }
/*
* The single command must be a plain SELECT.
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 401da5dedf..29b120b2ea 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -66,6 +66,7 @@ static Node *transformSetOperationTree(ParseState *pstate,
SelectStmt *stmt,
bool
isTopLevel, List **targetlist);
static void determineRecursiveColTypes(ParseState *pstate,
Node
*larg, List *nrtargetlist);
+static Query *transformReturnStmt(ParseState *pstate, ReturnStmt *stmt);
static Query *transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt);
static List *transformReturningList(ParseState *pstate, List *returningList);
static List *transformUpdateTargetList(ParseState *pstate,
@@ -304,6 +305,10 @@ transformStmt(ParseState *pstate, Node *parseTree)
}
break;
+ case T_ReturnStmt:
+ result = transformReturnStmt(pstate, (ReturnStmt *)
parseTree);
+ break;
+
/*
* Special cases
*/
@@ -2221,6 +2226,36 @@ determineRecursiveColTypes(ParseState *pstate, Node
*larg, List *nrtargetlist)
}
+/*
+ * transformReturnStmt -
+ * transforms a return statement
+ */
+static Query *
+transformReturnStmt(ParseState *pstate, ReturnStmt *stmt)
+{
+ Query *qry = makeNode(Query);
+
+ qry->commandType = CMD_SELECT;
+ qry->isReturn = true;
+
+ qry->targetList = list_make1(makeTargetEntry((Expr *)
transformExpr(pstate, stmt->returnval, EXPR_KIND_SELECT_TARGET),
+
1, NULL, false));
+
+ if (pstate->p_resolve_unknowns)
+ resolveTargetListUnknowns(pstate, qry->targetList);
+ qry->rtable = pstate->p_rtable;
+ qry->jointree = makeFromExpr(pstate->p_joinlist, NULL);
+ qry->hasSubLinks = pstate->p_hasSubLinks;
+ qry->hasWindowFuncs = pstate->p_hasWindowFuncs;
+ qry->hasTargetSRFs = pstate->p_hasTargetSRFs;
+ qry->hasAggs = pstate->p_hasAggs;
+
+ assign_query_collations(pstate, qry);
+
+ return qry;
+}
+
+
/*
* transformUpdateStmt -
* transforms an update statement
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index e669d75a5a..4f5ed7f229 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -253,7 +253,7 @@ static Node *makeRecursiveViewSelect(char *relname, List
*aliases, Node *query);
struct SelectLimit *selectlimit;
}
-%type <node> stmt schema_stmt
+%type <node> stmt toplevel_stmt schema_stmt routine_body_stmt
AlterEventTrigStmt AlterCollationStmt
AlterDatabaseStmt AlterDatabaseSetStmt AlterDomainStmt
AlterEnumStmt
AlterFdwStmt AlterForeignServerStmt AlterGroupStmt
@@ -280,9 +280,9 @@ static Node *makeRecursiveViewSelect(char *relname, List
*aliases, Node *query);
GrantStmt GrantRoleStmt ImportForeignSchemaStmt IndexStmt
InsertStmt
ListenStmt LoadStmt LockStmt NotifyStmt ExplainableStmt
PreparableStmt
CreateFunctionStmt AlterFunctionStmt ReindexStmt RemoveAggrStmt
- RemoveFuncStmt RemoveOperStmt RenameStmt RevokeStmt
RevokeRoleStmt
+ RemoveFuncStmt RemoveOperStmt RenameStmt ReturnStmt RevokeStmt
RevokeRoleStmt
RuleActionStmt RuleActionStmtOrEmpty RuleStmt
- SecLabelStmt SelectStmt TransactionStmt TruncateStmt
+ SecLabelStmt SelectStmt TransactionStmt TransactionStmtLegacy
TruncateStmt
UnlistenStmt UpdateStmt VacuumStmt
VariableResetStmt VariableSetStmt VariableShowStmt
ViewStmt CheckPointStmt CreateConversionStmt
@@ -385,14 +385,14 @@ static Node *makeRecursiveViewSelect(char *relname, List
*aliases, Node *query);
%type <node> vacuum_relation
%type <selectlimit> opt_select_limit select_limit limit_clause
-%type <list> stmtblock stmtmulti
+%type <list> stmtblock stmtmulti routine_body_stmt_list
OptTableElementList TableElementList OptInherit
definition
OptTypedTableElementList TypedTableElementList
reloptions opt_reloptions
OptWith distinct_clause opt_all_clause
opt_definition func_args func_args_list
func_args_with_defaults
func_args_with_defaults_list
aggr_args aggr_args_list
- func_as createfunc_opt_list alterfunc_opt_list
+ func_as createfunc_opt_list
opt_createfunc_opt_list alterfunc_opt_list
old_aggr_definition old_aggr_list
oper_argtypes RuleActionList RuleActionMulti
opt_column_list columnList opt_name_list
@@ -418,6 +418,7 @@ static Node *makeRecursiveViewSelect(char *relname, List
*aliases, Node *query);
vacuum_relation_list opt_vacuum_relation_list
drop_option_list
+%type <list> opt_routine_body
%type <list> group_by_list
%type <node> group_by_item empty_grouping_set rollup_clause cube_clause
%type <node> grouping_sets_clause
@@ -627,7 +628,7 @@ static Node *makeRecursiveViewSelect(char *relname, List
*aliases, Node *query);
/* ordinary key words in alphabetical order */
%token <keyword> ABORT_P ABSOLUTE_P ACCESS ACTION ADD_P ADMIN AFTER
AGGREGATE ALL ALSO ALTER ALWAYS ANALYSE ANALYZE AND ANY ARRAY AS ASC
- ASSERTION ASSIGNMENT ASYMMETRIC AT ATTACH ATTRIBUTE AUTHORIZATION
+ ASSERTION ASSIGNMENT ASYMMETRIC AT ATOMIC ATTACH ATTRIBUTE AUTHORIZATION
BACKWARD BEFORE BEGIN_P BETWEEN BIGINT BINARY BIT
BOOLEAN_P BOTH BY
@@ -689,7 +690,7 @@ static Node *makeRecursiveViewSelect(char *relname, List
*aliases, Node *query);
RANGE READ REAL REASSIGN RECHECK RECURSIVE REF REFERENCES REFERENCING
REFRESH REINDEX RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA
- RESET RESTART RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK
ROLLUP
+ RESET RESTART RESTRICT RETURN RETURNING RETURNS REVOKE RIGHT ROLE
ROLLBACK ROLLUP
ROUTINE ROUTINES ROW ROWS RULE
SAVEPOINT SCHEMA SCHEMAS SCROLL SEARCH SECOND_P SECURITY SELECT
SEQUENCE SEQUENCES
@@ -816,7 +817,7 @@ stmtblock: stmtmulti
* we'd get -1 for the location in such cases.
* We also take care to discard empty statements entirely.
*/
-stmtmulti: stmtmulti ';' stmt
+stmtmulti: stmtmulti ';' toplevel_stmt
{
if ($1 != NIL)
{
@@ -828,7 +829,7 @@ stmtmulti: stmtmulti ';' stmt
else
$$ = $1;
}
- | stmt
+ | toplevel_stmt
{
if ($1 != NULL)
$$ = list_make1(makeRawStmt($1,
0));
@@ -837,7 +838,16 @@ stmtmulti: stmtmulti ';' stmt
}
;
-stmt :
+/*
+ * toplevel_stmt includes BEGIN and END. stmt does not include them, because
+ * those words have different meanings in function bodys.
+ */
+toplevel_stmt:
+ stmt
+ | TransactionStmtLegacy
+ ;
+
+stmt:
AlterEventTrigStmt
| AlterCollationStmt
| AlterDatabaseStmt
@@ -7343,7 +7353,7 @@ opt_nulls_order: NULLS_LA FIRST_P { $$ =
SORTBY_NULLS_FIRST; }
CreateFunctionStmt:
CREATE opt_or_replace FUNCTION func_name
func_args_with_defaults
- RETURNS func_return createfunc_opt_list
+ RETURNS func_return opt_createfunc_opt_list
opt_routine_body
{
CreateFunctionStmt *n =
makeNode(CreateFunctionStmt);
n->is_procedure = false;
@@ -7352,10 +7362,11 @@ CreateFunctionStmt:
n->parameters = $5;
n->returnType = $7;
n->options = $8;
+ n->sql_body = $9;
$$ = (Node *)n;
}
| CREATE opt_or_replace FUNCTION func_name
func_args_with_defaults
- RETURNS TABLE '(' table_func_column_list ')'
createfunc_opt_list
+ RETURNS TABLE '(' table_func_column_list ')'
opt_createfunc_opt_list opt_routine_body
{
CreateFunctionStmt *n =
makeNode(CreateFunctionStmt);
n->is_procedure = false;
@@ -7365,10 +7376,11 @@ CreateFunctionStmt:
n->returnType = TableFuncTypeName($9);
n->returnType->location = @7;
n->options = $11;
+ n->sql_body = $12;
$$ = (Node *)n;
}
| CREATE opt_or_replace FUNCTION func_name
func_args_with_defaults
- createfunc_opt_list
+ opt_createfunc_opt_list opt_routine_body
{
CreateFunctionStmt *n =
makeNode(CreateFunctionStmt);
n->is_procedure = false;
@@ -7377,10 +7389,11 @@ CreateFunctionStmt:
n->parameters = $5;
n->returnType = NULL;
n->options = $6;
+ n->sql_body = $7;
$$ = (Node *)n;
}
| CREATE opt_or_replace PROCEDURE func_name
func_args_with_defaults
- createfunc_opt_list
+ opt_createfunc_opt_list opt_routine_body
{
CreateFunctionStmt *n =
makeNode(CreateFunctionStmt);
n->is_procedure = true;
@@ -7389,6 +7402,7 @@ CreateFunctionStmt:
n->parameters = $5;
n->returnType = NULL;
n->options = $6;
+ n->sql_body = $7;
$$ = (Node *)n;
}
;
@@ -7668,6 +7682,11 @@ aggregate_with_argtypes_list:
{ $$ = lappend($1, $3); }
;
+opt_createfunc_opt_list:
+ createfunc_opt_list
+ | /*EMPTY*/ { $$ = NIL; }
+ ;
+
createfunc_opt_list:
/* Must be at least one to prevent conflict */
createfunc_opt_item
{ $$ = list_make1($1); }
@@ -7779,6 +7798,50 @@ func_as: Sconst
{ $$ = list_make1(makeString($1)); }
}
;
+ReturnStmt: RETURN a_expr
+ {
+ ReturnStmt *r = makeNode(ReturnStmt);
+ r->returnval = (Node *) $2;
+ $$ = (Node *) r;
+ }
+ ;
+
+opt_routine_body:
+ ReturnStmt
+ {
+ $$ = list_make1($1);
+ }
+ | BEGIN_P ATOMIC routine_body_stmt_list END_P
+ {
+ $$ = $3;
+ }
+ | /*EMPTY*/
+ {
+ $$ = NIL;
+ }
+ ;
+
+routine_body_stmt_list:
+ routine_body_stmt_list routine_body_stmt ';'
+ {
+ $$ = lappend($1, $2);
+ }
+ | /*EMPTY*/
+ {
+ /*
+ * For an empty body we insert a single
fake element, so
+ * that the parse analysis code can
tell apart an empty
+ * body from no body at all.
+ */
+ $$ = list_make1(NULL);
+ }
+ ;
+
+routine_body_stmt:
+ stmt
+ | ReturnStmt
+ ;
+
transform_type_list:
FOR TYPE_P Typename { $$ = list_make1($3); }
| transform_type_list ',' FOR TYPE_P Typename { $$ =
lappend($1, $5); }
@@ -9725,13 +9788,6 @@ TransactionStmt:
n->chain = $3;
$$ = (Node *)n;
}
- | BEGIN_P opt_transaction transaction_mode_list_or_empty
- {
- TransactionStmt *n =
makeNode(TransactionStmt);
- n->kind = TRANS_STMT_BEGIN;
- n->options = $3;
- $$ = (Node *)n;
- }
| START TRANSACTION transaction_mode_list_or_empty
{
TransactionStmt *n =
makeNode(TransactionStmt);
@@ -9747,14 +9803,6 @@ TransactionStmt:
n->chain = $3;
$$ = (Node *)n;
}
- | END_P opt_transaction opt_transaction_chain
- {
- TransactionStmt *n =
makeNode(TransactionStmt);
- n->kind = TRANS_STMT_COMMIT;
- n->options = NIL;
- n->chain = $3;
- $$ = (Node *)n;
- }
| ROLLBACK opt_transaction opt_transaction_chain
{
TransactionStmt *n =
makeNode(TransactionStmt);
@@ -9821,6 +9869,24 @@ TransactionStmt:
}
;
+TransactionStmtLegacy:
+ BEGIN_P opt_transaction transaction_mode_list_or_empty
+ {
+ TransactionStmt *n =
makeNode(TransactionStmt);
+ n->kind = TRANS_STMT_BEGIN;
+ n->options = $3;
+ $$ = (Node *)n;
+ }
+ | END_P opt_transaction opt_transaction_chain
+ {
+ TransactionStmt *n =
makeNode(TransactionStmt);
+ n->kind = TRANS_STMT_COMMIT;
+ n->options = NIL;
+ n->chain = $3;
+ $$ = (Node *)n;
+ }
+ ;
+
opt_transaction: WORK
{}
| TRANSACTION
{}
| /*EMPTY*/
{}
@@ -15039,6 +15105,7 @@ unreserved_keyword:
| ASSERTION
| ASSIGNMENT
| AT
+ | ATOMIC
| ATTACH
| ATTRIBUTE
| BACKWARD
@@ -15237,6 +15304,7 @@ unreserved_keyword:
| RESET
| RESTART
| RESTRICT
+ | RETURN
| RETURNS
| REVOKE
| ROLE
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index c9424f167c..b63c330653 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -176,7 +176,6 @@ static int interactive_getc(void);
static int SocketBackend(StringInfo inBuf);
static int ReadCommand(StringInfo inBuf);
static void forbidden_in_wal_sender(char firstchar);
-static List *pg_rewrite_query(Query *query);
static bool check_log_statement(List *stmt_list);
static int errdetail_execute(List *raw_parsetree_list);
static int errdetail_params(ParamListInfo params);
@@ -761,7 +760,7 @@ pg_analyze_and_rewrite_params(RawStmt *parsetree,
* Note: query must just have come from the parser, because we do not do
* AcquireRewriteLocks() on it.
*/
-static List *
+List *
pg_rewrite_query(Query *query)
{
List *querytree_list;
diff --git a/src/backend/utils/adt/ruleutils.c
b/src/backend/utils/adt/ruleutils.c
index 076c3c019f..0ecda56e54 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -174,6 +174,9 @@ typedef struct
List *outer_tlist; /* referent for OUTER_VAR Vars */
List *inner_tlist; /* referent for INNER_VAR Vars */
List *index_tlist; /* referent for INDEX_VAR Vars */
+ /* Special namespace representing a function signature: */
+ int numargs;
+ char **argnames;
} deparse_namespace;
/*
@@ -2800,9 +2803,54 @@ pg_get_functiondef(PG_FUNCTION_ARGS)
}
/* And finally the function definition ... */
+ tmp = SysCacheGetAttr(PROCOID, proctup, Anum_pg_proc_probin, &isnull);
+ if (proc->prolang == SQLlanguageId && !isnull)
+ {
+ List *stmts = castNode(List,
stringToNode(TextDatumGetCString(tmp)));
+ ListCell *lc;
+ int numargs;
+ Oid *argtypes;
+ char **argnames;
+ char *argmodes;
+ deparse_namespace dpns = {0};
+ bool need_block;
+
+ numargs = get_func_arg_info(proctup,
+
&argtypes, &argnames, &argmodes);
+ dpns.numargs = numargs;
+ dpns.argnames = argnames;
+
+ /*
+ * We need a BEGIN ATOMIC/END block unless the body is a single
RETURN
+ * statement.
+ */
+ need_block = true;
+ if (list_length(stmts) == 1)
+ {
+ Query *query = linitial_node(Query, stmts);
+
+ if (query->isReturn)
+ need_block = false;
+ }
+
+ if (need_block)
+ appendStringInfoString(&buf, "BEGIN ATOMIC\n");
+ foreach(lc, stmts)
+ {
+ Query *query = lfirst_node(Query, lc);
+
+ get_query_def(query, &buf, list_make1(&dpns), NULL,
PRETTYFLAG_INDENT, WRAP_COLUMN_DEFAULT, 1);
+ if (lc != list_tail(stmts))
+ appendStringInfoChar(&buf, ';');
+ appendStringInfoChar(&buf, '\n');
+ }
+ if (need_block)
+ appendStringInfoString(&buf, "END");
+ }
+ else
+ {
appendStringInfoString(&buf, "AS ");
- tmp = SysCacheGetAttr(PROCOID, proctup, Anum_pg_proc_probin, &isnull);
if (!isnull)
{
simple_quote_literal(&buf, TextDatumGetCString(tmp));
@@ -2831,6 +2879,7 @@ pg_get_functiondef(PG_FUNCTION_ARGS)
appendBinaryStringInfo(&buf, dq.data, dq.len);
appendStringInfoString(&buf, prosrc);
appendBinaryStringInfo(&buf, dq.data, dq.len);
+ }
appendStringInfoChar(&buf, '\n');
@@ -5428,7 +5477,10 @@ get_basic_select_query(Query *query, deparse_context
*context,
/*
* Build up the query string - first we say SELECT
*/
- appendStringInfoString(buf, "SELECT");
+ if (query->isReturn)
+ appendStringInfoString(buf, "RETURN");
+ else
+ appendStringInfoString(buf, "SELECT");
/* Add the DISTINCT clause if given */
if (query->distinctClause != NIL)
@@ -7560,6 +7612,26 @@ get_parameter(Param *param, deparse_context *context)
return;
}
+ /*
+ * If it's an external parameter, see if the outermost namespace
provides
+ * function argument names.
+ */
+ if (param->paramkind == PARAM_EXTERN)
+ {
+ dpns = lfirst(list_tail(context->namespaces));
+ if (dpns->argnames)
+ {
+ char *argname = dpns->argnames[param->paramid -
1];
+
+ if (argname)
+ {
+ // TODO: qualify with function name if necessary
+ appendStringInfo(context->buf, "%s",
quote_identifier(argname));
+ return;
+ }
+ }
+ }
+
/*
* Not PARAM_EXEC, or couldn't find referent: just print $N.
*/
diff --git a/src/fe_utils/psqlscan.l b/src/fe_utils/psqlscan.l
index 08dffde1ba..ee34463e67 100644
--- a/src/fe_utils/psqlscan.l
+++ b/src/fe_utils/psqlscan.l
@@ -645,10 +645,11 @@ other .
";" {
ECHO;
- if (cur_state->paren_depth == 0)
+ if (cur_state->paren_depth == 0 &&
cur_state->begin_depth == 0)
{
/* Terminate lexing temporarily
*/
cur_state->start_state =
YY_START;
+ cur_state->identifier_count = 0;
return LEXRES_SEMI;
}
}
@@ -661,6 +662,8 @@ other .
"\\"[;:] {
/* Force a semi-colon or colon into the
query buffer */
psqlscan_emit(cur_state, yytext + 1, 1);
+ if (yytext[1] == ';')
+ cur_state->identifier_count = 0;
}
"\\" {
@@ -867,6 +870,17 @@ other .
{identifier} {
+ cur_state->identifier_count++;
+ if (pg_strcasecmp(yytext, "begin") == 0)
+ {
+ if (cur_state->identifier_count
> 1)
+
cur_state->begin_depth++;
+ }
+ else if (pg_strcasecmp(yytext, "end")
== 0)
+ {
+ if (cur_state->begin_depth > 0)
+
cur_state->begin_depth--;
+ }
ECHO;
}
@@ -1054,6 +1068,11 @@ psql_scan(PsqlScanState state,
result = PSCAN_INCOMPLETE;
*prompt = PROMPT_PAREN;
}
+ if (state->begin_depth > 0)
+ {
+ result = PSCAN_INCOMPLETE;
+ *prompt = PROMPT_CONTINUE;
+ }
else if (query_buf->len > 0)
{
result = PSCAN_EOL;
@@ -1170,6 +1189,8 @@ psql_scan_reset(PsqlScanState state)
if (state->dolqstart)
free(state->dolqstart);
state->dolqstart = NULL;
+ state->identifier_count = 0;
+ state->begin_depth = 0;
}
/*
diff --git a/src/include/commands/defrem.h b/src/include/commands/defrem.h
index c26a102b17..4d93afbd4b 100644
--- a/src/include/commands/defrem.h
+++ b/src/include/commands/defrem.h
@@ -68,9 +68,11 @@ extern void interpret_function_parameter_list(ParseState
*pstate,
Oid languageOid,
ObjectType objtype,
oidvector **parameterTypes,
+
List **parameterTypes_list,
ArrayType **allParameterTypes,
ArrayType **parameterModes,
ArrayType **parameterNames,
+
List **inParameterNames_list,
List **parameterDefaults,
Oid *variadicArgType,
Oid *requiredResultType);
diff --git a/src/include/executor/functions.h b/src/include/executor/functions.h
index cb13428a5a..5d547c66ed 100644
--- a/src/include/executor/functions.h
+++ b/src/include/executor/functions.h
@@ -20,6 +20,21 @@
/* This struct is known only within executor/functions.c */
typedef struct SQLFunctionParseInfo *SQLFunctionParseInfoPtr;
+/*
+ * Data structure needed by the parser callback hooks to resolve parameter
+ * references during parsing of a SQL function's body. This is separate from
+ * SQLFunctionCache since we sometimes do parsing separately from execution.
+ */
+typedef struct SQLFunctionParseInfo
+{
+ char *fname; /* function's name */
+ int nargs; /* number of input
arguments */
+ Oid *argtypes; /* resolved types of input
arguments */
+ char **argnames; /* names of input arguments; NULL if
none */
+ /* Note that argnames[i] can be NULL, if some args are unnamed */
+ Oid collation; /* function's input
collation, if known */
+} SQLFunctionParseInfo;
+
extern Datum fmgr_sql(PG_FUNCTION_ARGS);
extern SQLFunctionParseInfoPtr prepare_sql_fn_parse_info(HeapTuple
procedureTuple,
diff --git a/src/include/fe_utils/psqlscan_int.h
b/src/include/fe_utils/psqlscan_int.h
index 311f80394a..fb8f58aa29 100644
--- a/src/include/fe_utils/psqlscan_int.h
+++ b/src/include/fe_utils/psqlscan_int.h
@@ -114,6 +114,8 @@ typedef struct PsqlScanStateData
int paren_depth; /* depth of nesting in
parentheses */
int xcdepth; /* depth of nesting in
slash-star comments */
char *dolqstart; /* current $foo$ quote start string */
+ int identifier_count; /* identifiers since
start of statement */
+ int begin_depth; /* depth of begin/end routine
body blocks */
/*
* Callback functions provided by the program making use of the lexer,
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index 381d84b4e4..ab8b745665 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -316,6 +316,7 @@ typedef enum NodeTag
T_DeleteStmt,
T_UpdateStmt,
T_SelectStmt,
+ T_ReturnStmt,
T_AlterTableStmt,
T_AlterTableCmd,
T_AlterDomainStmt,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 5e1ffafb91..5c436516a3 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -132,6 +132,8 @@ typedef struct Query
bool hasForUpdate; /* FOR [KEY] UPDATE/SHARE was specified
*/
bool hasRowSecurity; /* rewriter has applied some RLS policy
*/
+ bool isReturn; /* is a RETURN statement */
+
List *cteList; /* WITH list (of CommonTableExpr's) */
List *rtable; /* list of range table entries
*/
@@ -1670,6 +1672,16 @@ typedef struct SetOperationStmt
} SetOperationStmt;
+/*
+ * RETURN statement (inside SQL function body)
+ */
+typedef struct ReturnStmt
+{
+ NodeTag type;
+ Node *returnval;
+} ReturnStmt;
+
+
/*****************************************************************************
* Other Statements (no optimizations required)
*
@@ -2840,6 +2852,7 @@ typedef struct CreateFunctionStmt
List *parameters; /* a list of FunctionParameter */
TypeName *returnType; /* the return type */
List *options; /* a list of DefElem */
+ List *sql_body;
} CreateFunctionStmt;
typedef enum FunctionParameterMode
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 08f22ce211..c972c6f2ba 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -48,6 +48,7 @@ PG_KEYWORD("assertion", ASSERTION, UNRESERVED_KEYWORD)
PG_KEYWORD("assignment", ASSIGNMENT, UNRESERVED_KEYWORD)
PG_KEYWORD("asymmetric", ASYMMETRIC, RESERVED_KEYWORD)
PG_KEYWORD("at", AT, UNRESERVED_KEYWORD)
+PG_KEYWORD("atomic", ATOMIC, UNRESERVED_KEYWORD)
PG_KEYWORD("attach", ATTACH, UNRESERVED_KEYWORD)
PG_KEYWORD("attribute", ATTRIBUTE, UNRESERVED_KEYWORD)
PG_KEYWORD("authorization", AUTHORIZATION, TYPE_FUNC_NAME_KEYWORD)
@@ -344,6 +345,7 @@ PG_KEYWORD("replica", REPLICA, UNRESERVED_KEYWORD)
PG_KEYWORD("reset", RESET, UNRESERVED_KEYWORD)
PG_KEYWORD("restart", RESTART, UNRESERVED_KEYWORD)
PG_KEYWORD("restrict", RESTRICT, UNRESERVED_KEYWORD)
+PG_KEYWORD("return", RETURN, UNRESERVED_KEYWORD)
PG_KEYWORD("returning", RETURNING, RESERVED_KEYWORD)
PG_KEYWORD("returns", RETURNS, UNRESERVED_KEYWORD)
PG_KEYWORD("revoke", REVOKE, UNRESERVED_KEYWORD)
diff --git a/src/include/tcop/tcopprot.h b/src/include/tcop/tcopprot.h
index bd30607b07..e626c8eafd 100644
--- a/src/include/tcop/tcopprot.h
+++ b/src/include/tcop/tcopprot.h
@@ -43,6 +43,7 @@ typedef enum
extern PGDLLIMPORT int log_statement;
extern List *pg_parse_query(const char *query_string);
+extern List *pg_rewrite_query(Query *query);
extern List *pg_analyze_and_rewrite(RawStmt *parsetree,
const
char *query_string,
Oid
*paramTypes, int numParams,
diff --git a/src/interfaces/ecpg/preproc/ecpg.addons
b/src/interfaces/ecpg/preproc/ecpg.addons
index 300381eaad..0441561c52 100644
--- a/src/interfaces/ecpg/preproc/ecpg.addons
+++ b/src/interfaces/ecpg/preproc/ecpg.addons
@@ -87,6 +87,12 @@ ECPG: stmtTransactionStmt block
whenever_action(2);
free($1);
}
+ECPG: toplevel_stmtTransactionStmtLegacy block
+ {
+ fprintf(base_yyout, "{ ECPGtrans(__LINE__, %s, \"%s\");",
connection ? connection : "NULL", $1);
+ whenever_action(2);
+ free($1);
+ }
ECPG: stmtViewStmt rule
| ECPGAllocateDescr
{
diff --git a/src/interfaces/ecpg/preproc/ecpg.trailer
b/src/interfaces/ecpg/preproc/ecpg.trailer
index 6ccc8ab916..7f4be655f9 100644
--- a/src/interfaces/ecpg/preproc/ecpg.trailer
+++ b/src/interfaces/ecpg/preproc/ecpg.trailer
@@ -4,8 +4,8 @@ statements: /*EMPTY*/
| statements statement
;
-statement: ecpgstart at stmt ';' { connection = NULL; }
- | ecpgstart stmt ';'
+statement: ecpgstart at toplevel_stmt ';' { connection = NULL; }
+ | ecpgstart toplevel_stmt ';'
| ecpgstart ECPGVarDeclaration
{
fprintf(base_yyout, "%s", $2);
diff --git a/src/test/regress/expected/create_function_3.out
b/src/test/regress/expected/create_function_3.out
index ba260df996..38436b3ccc 100644
--- a/src/test/regress/expected/create_function_3.out
+++ b/src/test/regress/expected/create_function_3.out
@@ -17,7 +17,7 @@ SET search_path TO temp_func_test, public;
CREATE FUNCTION functest_A_1(text, date) RETURNS bool LANGUAGE 'sql'
AS 'SELECT $1 = ''abcd'' AND $2 > ''2001-01-01''';
CREATE FUNCTION functest_A_2(text[]) RETURNS int LANGUAGE 'sql'
- AS 'SELECT $1[0]::int';
+ AS 'SELECT $1[1]::int';
CREATE FUNCTION functest_A_3() RETURNS bool LANGUAGE 'sql'
AS 'SELECT false';
SELECT proname, prorettype::regtype, proargtypes::regtype[] FROM pg_proc
@@ -31,6 +31,24 @@ SELECT proname, prorettype::regtype, proargtypes::regtype[]
FROM pg_proc
functest_a_3 | boolean | {}
(3 rows)
+SELECT functest_A_1('abcd', '2020-01-01');
+ functest_a_1
+--------------
+ t
+(1 row)
+
+SELECT functest_A_2(ARRAY['1', '2', '3']);
+ functest_a_2
+--------------
+ 1
+(1 row)
+
+SELECT functest_A_3();
+ functest_a_3
+--------------
+ f
+(1 row)
+
--
-- IMMUTABLE | STABLE | VOLATILE
--
@@ -237,6 +255,135 @@ SELECT pg_get_functiondef('functest_F_2'::regproc);
(1 row)
+--
+-- SQL-standard body
+--
+CREATE FUNCTION functest_S_1(a text, b date) RETURNS boolean
+ LANGUAGE SQL
+ RETURN a = 'abcd' AND b > '2001-01-01';
+CREATE FUNCTION functest_S_2(a text[]) RETURNS int
+ RETURN a[1]::int;
+CREATE FUNCTION functest_S_3() RETURNS boolean
+ RETURN false;
+CREATE FUNCTION functest_S_10(a text, b date) RETURNS boolean
+ LANGUAGE SQL
+ BEGIN ATOMIC
+ SELECT a = 'abcd' AND b > '2001-01-01';
+ END;
+CREATE FUNCTION functest_S_13() RETURNS boolean
+ BEGIN ATOMIC
+ SELECT 1;
+ SELECT false;
+ END;
+-- polymorphic arguments not allowed in this form
+CREATE FUNCTION functest_S_xx(x anyarray) RETURNS anyelement
+ LANGUAGE SQL
+ RETURN x[1];
+ERROR: SQL function with unquoted function body cannot have polymorphic
arguments
+SELECT functest_S_1('abcd', '2020-01-01');
+ functest_s_1
+--------------
+ t
+(1 row)
+
+SELECT functest_S_2(ARRAY['1', '2', '3']);
+ functest_s_2
+--------------
+ 1
+(1 row)
+
+SELECT functest_S_3();
+ functest_s_3
+--------------
+ f
+(1 row)
+
+SELECT functest_S_10('abcd', '2020-01-01');
+ functest_s_10
+---------------
+ t
+(1 row)
+
+SELECT functest_S_13();
+ functest_s_13
+---------------
+ f
+(1 row)
+
+SELECT pg_get_functiondef('functest_S_1'::regproc);
+ pg_get_functiondef
+------------------------------------------------------------------------
+ CREATE OR REPLACE FUNCTION temp_func_test.functest_s_1(a text, b date)+
+ RETURNS boolean +
+ LANGUAGE sql +
+ RETURN ((a = 'abcd'::text) AND (b > '01-01-2001'::date)) +
+ +
+
+(1 row)
+
+SELECT pg_get_functiondef('functest_S_2'::regproc);
+ pg_get_functiondef
+------------------------------------------------------------------
+ CREATE OR REPLACE FUNCTION temp_func_test.functest_s_2(a text[])+
+ RETURNS integer +
+ LANGUAGE sql +
+ RETURN ((a)[1])::integer +
+ +
+
+(1 row)
+
+SELECT pg_get_functiondef('functest_S_3'::regproc);
+ pg_get_functiondef
+----------------------------------------------------------
+ CREATE OR REPLACE FUNCTION temp_func_test.functest_s_3()+
+ RETURNS boolean +
+ LANGUAGE sql +
+ RETURN false +
+ +
+
+(1 row)
+
+SELECT pg_get_functiondef('functest_S_10'::regproc);
+ pg_get_functiondef
+-------------------------------------------------------------------------
+ CREATE OR REPLACE FUNCTION temp_func_test.functest_s_10(a text, b date)+
+ RETURNS boolean +
+ LANGUAGE sql +
+ BEGIN ATOMIC +
+ SELECT ((a = 'abcd'::text) AND (b > '01-01-2001'::date)) +
+ END +
+
+(1 row)
+
+SELECT pg_get_functiondef('functest_S_13'::regproc);
+ pg_get_functiondef
+-----------------------------------------------------------
+ CREATE OR REPLACE FUNCTION temp_func_test.functest_s_13()+
+ RETURNS boolean +
+ LANGUAGE sql +
+ BEGIN ATOMIC +
+ SELECT 1; +
+ SELECT false AS bool +
+ END +
+
+(1 row)
+
+-- test with views
+CREATE TABLE functest3 (a int);
+INSERT INTO functest3 VALUES (1), (2);
+CREATE VIEW functestv3 AS SELECT * FROM functest3;
+CREATE FUNCTION functest_S_14() RETURNS bigint
+ RETURN (SELECT count(*) FROM functestv3);
+SELECT functest_S_14();
+ functest_s_14
+---------------
+ 2
+(1 row)
+
+DROP TABLE functest3 CASCADE;
+NOTICE: drop cascades to 2 other objects
+DETAIL: drop cascades to view functestv3
+drop cascades to function functest_s_14()
-- information_schema tests
CREATE FUNCTION functest_IS_1(a int, b int default 1, c text default 'foo')
RETURNS int
@@ -266,6 +413,20 @@ SELECT routine_name, ordinal_position, parameter_name,
parameter_default
(7 rows)
DROP FUNCTION functest_IS_1(int, int, text), functest_IS_2(int),
functest_IS_3(int);
+CREATE TABLE functest1 (a int, b int);
+CREATE SEQUENCE functest2;
+CREATE FUNCTION functest_IS_4()
+ RETURNS int
+ LANGUAGE SQL
+ RETURN (SELECT count(a) FROM functest1);
+CREATE FUNCTION functest_IS_5()
+ RETURNS int
+ LANGUAGE SQL
+ RETURN nextval('functest2');
+DROP TABLE functest1 CASCADE;
+NOTICE: drop cascades to function functest_is_4()
+DROP SEQUENCE functest2 CASCADE;
+NOTICE: drop cascades to function functest_is_5()
-- overload
CREATE FUNCTION functest_B_2(bigint) RETURNS bool LANGUAGE 'sql'
IMMUTABLE AS 'SELECT $1 > 0';
@@ -342,7 +503,7 @@ SELECT * FROM voidtest5(3);
-- Cleanup
DROP SCHEMA temp_func_test CASCADE;
-NOTICE: drop cascades to 21 other objects
+NOTICE: drop cascades to 26 other objects
DETAIL: drop cascades to function functest_a_1(text,date)
drop cascades to function functest_a_2(text[])
drop cascades to function functest_a_3()
@@ -358,6 +519,11 @@ drop cascades to function functest_f_1(integer)
drop cascades to function functest_f_2(integer)
drop cascades to function functest_f_3(integer)
drop cascades to function functest_f_4(integer)
+drop cascades to function functest_s_1(text,date)
+drop cascades to function functest_s_2(text[])
+drop cascades to function functest_s_3()
+drop cascades to function functest_s_10(text,date)
+drop cascades to function functest_s_13()
drop cascades to function functest_b_2(bigint)
drop cascades to function voidtest1(integer)
drop cascades to function voidtest2(integer,integer)
diff --git a/src/test/regress/expected/create_procedure.out
b/src/test/regress/expected/create_procedure.out
index 211a42cefa..c927a2894a 100644
--- a/src/test/regress/expected/create_procedure.out
+++ b/src/test/regress/expected/create_procedure.out
@@ -65,6 +65,41 @@ SELECT * FROM cp_test ORDER BY b COLLATE "C";
1 | xyzzy
(3 rows)
+-- SQL-standard body
+CREATE PROCEDURE ptest1s(x text)
+LANGUAGE SQL
+BEGIN ATOMIC
+ INSERT INTO cp_test VALUES (1, x);
+END;
+\df ptest1s
+ List of functions
+ Schema | Name | Result data type | Argument data types | Type
+--------+---------+------------------+---------------------+------
+ public | ptest1s | | x text | proc
+(1 row)
+
+SELECT pg_get_functiondef('ptest1s'::regproc);
+ pg_get_functiondef
+----------------------------------------------------
+ CREATE OR REPLACE PROCEDURE public.ptest1s(x text)+
+ LANGUAGE sql +
+ BEGIN ATOMIC +
+ INSERT INTO cp_test (a, b) +
+ VALUES (1, x) +
+ END +
+
+(1 row)
+
+CALL ptest1s('b');
+SELECT * FROM cp_test ORDER BY b COLLATE "C";
+ a | b
+---+-------
+ 1 | 0
+ 1 | a
+ 1 | b
+ 1 | xyzzy
+(4 rows)
+
CREATE PROCEDURE ptest2()
LANGUAGE SQL
AS $$
@@ -146,6 +181,28 @@ AS $$
SELECT a = b;
$$;
CALL ptest7(least('a', 'b'), 'a');
+-- empty body
+CREATE PROCEDURE ptest8(x text)
+BEGIN ATOMIC
+END;
+\df ptest8
+ List of functions
+ Schema | Name | Result data type | Argument data types | Type
+--------+--------+------------------+---------------------+------
+ public | ptest8 | | x text | proc
+(1 row)
+
+SELECT pg_get_functiondef('ptest8'::regproc);
+ pg_get_functiondef
+---------------------------------------------------
+ CREATE OR REPLACE PROCEDURE public.ptest8(x text)+
+ LANGUAGE sql +
+ BEGIN ATOMIC +
+ END +
+
+(1 row)
+
+CALL ptest8('');
-- various error cases
CALL version(); -- error: not a procedure
ERROR: version() is not a procedure
@@ -204,6 +261,7 @@ ALTER ROUTINE ptest1a RENAME TO ptest1;
DROP ROUTINE cp_testfunc1(int);
-- cleanup
DROP PROCEDURE ptest1;
+DROP PROCEDURE ptest1s;
DROP PROCEDURE ptest2;
DROP TABLE cp_test;
DROP USER regress_cp_user1;
diff --git a/src/test/regress/sql/create_function_3.sql
b/src/test/regress/sql/create_function_3.sql
index 7a2df0ea8a..91071bfb9f 100644
--- a/src/test/regress/sql/create_function_3.sql
+++ b/src/test/regress/sql/create_function_3.sql
@@ -23,7 +23,7 @@ CREATE SCHEMA temp_func_test;
CREATE FUNCTION functest_A_1(text, date) RETURNS bool LANGUAGE 'sql'
AS 'SELECT $1 = ''abcd'' AND $2 > ''2001-01-01''';
CREATE FUNCTION functest_A_2(text[]) RETURNS int LANGUAGE 'sql'
- AS 'SELECT $1[0]::int';
+ AS 'SELECT $1[1]::int';
CREATE FUNCTION functest_A_3() RETURNS bool LANGUAGE 'sql'
AS 'SELECT false';
SELECT proname, prorettype::regtype, proargtypes::regtype[] FROM pg_proc
@@ -31,6 +31,10 @@ CREATE FUNCTION functest_A_3() RETURNS bool LANGUAGE 'sql'
'functest_A_2'::regproc,
'functest_A_3'::regproc) ORDER BY proname;
+SELECT functest_A_1('abcd', '2020-01-01');
+SELECT functest_A_2(ARRAY['1', '2', '3']);
+SELECT functest_A_3();
+
--
-- IMMUTABLE | STABLE | VOLATILE
--
@@ -149,6 +153,60 @@ CREATE FUNCTION functest_F_4(int) RETURNS bool LANGUAGE
'sql'
SELECT pg_get_functiondef('functest_F_2'::regproc);
+--
+-- SQL-standard body
+--
+CREATE FUNCTION functest_S_1(a text, b date) RETURNS boolean
+ LANGUAGE SQL
+ RETURN a = 'abcd' AND b > '2001-01-01';
+CREATE FUNCTION functest_S_2(a text[]) RETURNS int
+ RETURN a[1]::int;
+CREATE FUNCTION functest_S_3() RETURNS boolean
+ RETURN false;
+
+CREATE FUNCTION functest_S_10(a text, b date) RETURNS boolean
+ LANGUAGE SQL
+ BEGIN ATOMIC
+ SELECT a = 'abcd' AND b > '2001-01-01';
+ END;
+
+CREATE FUNCTION functest_S_13() RETURNS boolean
+ BEGIN ATOMIC
+ SELECT 1;
+ SELECT false;
+ END;
+
+-- polymorphic arguments not allowed in this form
+CREATE FUNCTION functest_S_xx(x anyarray) RETURNS anyelement
+ LANGUAGE SQL
+ RETURN x[1];
+
+SELECT functest_S_1('abcd', '2020-01-01');
+SELECT functest_S_2(ARRAY['1', '2', '3']);
+SELECT functest_S_3();
+
+SELECT functest_S_10('abcd', '2020-01-01');
+SELECT functest_S_13();
+
+SELECT pg_get_functiondef('functest_S_1'::regproc);
+SELECT pg_get_functiondef('functest_S_2'::regproc);
+SELECT pg_get_functiondef('functest_S_3'::regproc);
+SELECT pg_get_functiondef('functest_S_10'::regproc);
+SELECT pg_get_functiondef('functest_S_13'::regproc);
+
+-- test with views
+CREATE TABLE functest3 (a int);
+INSERT INTO functest3 VALUES (1), (2);
+CREATE VIEW functestv3 AS SELECT * FROM functest3;
+
+CREATE FUNCTION functest_S_14() RETURNS bigint
+ RETURN (SELECT count(*) FROM functestv3);
+
+SELECT functest_S_14();
+
+DROP TABLE functest3 CASCADE;
+
+
-- information_schema tests
CREATE FUNCTION functest_IS_1(a int, b int default 1, c text default 'foo')
@@ -173,6 +231,23 @@ CREATE FUNCTION functest_IS_3(a int default 1, out b int)
DROP FUNCTION functest_IS_1(int, int, text), functest_IS_2(int),
functest_IS_3(int);
+CREATE TABLE functest1 (a int, b int);
+CREATE SEQUENCE functest2;
+
+CREATE FUNCTION functest_IS_4()
+ RETURNS int
+ LANGUAGE SQL
+ RETURN (SELECT count(a) FROM functest1);
+
+CREATE FUNCTION functest_IS_5()
+ RETURNS int
+ LANGUAGE SQL
+ RETURN nextval('functest2');
+
+DROP TABLE functest1 CASCADE;
+DROP SEQUENCE functest2 CASCADE;
+
+
-- overload
CREATE FUNCTION functest_B_2(bigint) RETURNS bool LANGUAGE 'sql'
IMMUTABLE AS 'SELECT $1 > 0';
diff --git a/src/test/regress/sql/create_procedure.sql
b/src/test/regress/sql/create_procedure.sql
index 89b96d580f..acbe92fb9a 100644
--- a/src/test/regress/sql/create_procedure.sql
+++ b/src/test/regress/sql/create_procedure.sql
@@ -28,6 +28,21 @@ CREATE PROCEDURE ptest1(x text)
SELECT * FROM cp_test ORDER BY b COLLATE "C";
+-- SQL-standard body
+CREATE PROCEDURE ptest1s(x text)
+LANGUAGE SQL
+BEGIN ATOMIC
+ INSERT INTO cp_test VALUES (1, x);
+END;
+
+\df ptest1s
+SELECT pg_get_functiondef('ptest1s'::regproc);
+
+CALL ptest1s('b');
+
+SELECT * FROM cp_test ORDER BY b COLLATE "C";
+
+
CREATE PROCEDURE ptest2()
LANGUAGE SQL
AS $$
@@ -112,6 +127,16 @@ CREATE PROCEDURE ptest7(a text, b text)
CALL ptest7(least('a', 'b'), 'a');
+-- empty body
+CREATE PROCEDURE ptest8(x text)
+BEGIN ATOMIC
+END;
+
+\df ptest8
+SELECT pg_get_functiondef('ptest8'::regproc);
+CALL ptest8('');
+
+
-- various error cases
CALL version(); -- error: not a procedure
@@ -159,6 +184,7 @@ CREATE USER regress_cp_user1;
-- cleanup
DROP PROCEDURE ptest1;
+DROP PROCEDURE ptest1s;
DROP PROCEDURE ptest2;
DROP TABLE cp_test;
base-commit: 68de1440c79d75e529ff8c7395d698252370f992
--
2.27.0
