checkfunction20120128.diff
text/x-patch
Filename: checkfunction20120128.diff
Type: text/x-patch
Part: 0
Message:
Re: review: CHECK FUNCTION statement
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: context
| File | + | − |
|---|---|---|
| doc/src/sgml/catalogs.sgml | 18 | 0 |
| doc/src/sgml/plhandler.sgml | 41 | 0 |
| doc/src/sgml/ref/allfiles.sgml | 2 | 0 |
| doc/src/sgml/ref/create_language.sgml | 40 | 0 |
| doc/src/sgml/reference.sgml | 2 | 0 |
| src/backend/catalog/pg_proc.c | 1 | 0 |
| src/backend/commands/functioncmds.c | 497 | 0 |
| src/backend/commands/proclang.c | 114 | 0 |
| src/backend/nodes/copyfuncs.c | 19 | 0 |
| src/backend/nodes/equalfuncs.c | 17 | 0 |
| src/backend/parser/gram.y | 137 | 0 |
| src/backend/tcop/utility.c | 21 | 0 |
| src/bin/pg_dump/pg_dump.c | 38 | 0 |
| src/bin/pg_dump/pg_dump.h | 1 | 0 |
| src/bin/psql/tab-complete.c | 24 | 0 |
| src/include/catalog/pg_language.h | 7 | 0 |
| src/include/catalog/pg_pltemplate.h | 13 | 0 |
| src/include/commands/defrem.h | 3 | 0 |
| src/include/nodes/nodes.h | 1 | 0 |
| src/include/nodes/parsenodes.h | 17 | 0 |
| src/pl/plpgsql/src/pl_comp.c | 4 | 0 |
| src/pl/plpgsql/src/pl_exec.c | 1566 | 0 |
| src/pl/plpgsql/src/pl_handler.c | 268 | 0 |
| src/pl/plpgsql/src/plpgsql.h | 21 | 0 |
| src/pl/plpgsql/src/plpgsql--unpackaged--1.0.sql | 1 | 0 |
| src/test/regress/expected/plpgsql.out | 535 | 0 |
| src/test/regress/sql/plpgsql.sql | 303 | 0 |
*** a/doc/src/sgml/catalogs.sgml
--- b/doc/src/sgml/catalogs.sgml
***************
*** 3663,3668 ****
--- 3663,3680 ----
</row>
<row>
+ <entry><structfield>lanchecker</structfield></entry>
+ <entry><type>oid</type></entry>
+ <entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
+ <entry>
+ This references a correctness check function that is used by
+ <command>CHECK FUNCTION</> and <command>CHECK TRIGGER</> for in-depth
+ checks of function bodies.
+ Zero if no such function is provided.
+ </entry>
+ </row>
+
+ <row>
<entry><structfield>lanacl</structfield></entry>
<entry><type>aclitem[]</type></entry>
<entry></entry>
***************
*** 4273,4278 ****
--- 4285,4296 ----
</row>
<row>
+ <entry><structfield>tmplchecker</structfield></entry>
+ <entry><type>text</type></entry>
+ <entry>Name of correctness check function, or null if none</entry>
+ </row>
+
+ <row>
<entry><structfield>tmpllibrary</structfield></entry>
<entry><type>text</type></entry>
<entry>Path of shared library that implements language</entry>
*** a/doc/src/sgml/plhandler.sgml
--- b/doc/src/sgml/plhandler.sgml
***************
*** 159,170 **** CREATE LANGUAGE plsample
<para>
Although providing a call handler is sufficient to create a minimal
! procedural language, there are two other functions that can optionally
be provided to make the language more convenient to use. These
! are a <firstterm>validator</firstterm> and an
! <firstterm>inline handler</firstterm>. A validator can be provided
! to allow language-specific checking to be done during
! <xref linkend="sql-createfunction">.
An inline handler can be provided to allow the language to support
anonymous code blocks executed via the <xref linkend="sql-do"> command.
</para>
--- 159,173 ----
<para>
Although providing a call handler is sufficient to create a minimal
! procedural language, there are three other functions that can optionally
be provided to make the language more convenient to use. These
! are a <firstterm>validator</firstterm>, a <firstterm>checker</firstterm>
! and an <firstterm>inline handler</firstterm>.
! A validator can be provided to allow language-specific checking to be
! done during <xref linkend="sql-createfunction">.
! A checker performs in-depth checks of function bodies via the
! commands <xref linkend="sql-checkfunction"> and
! <xref linkend="sql-checktrigger">.
An inline handler can be provided to allow the language to support
anonymous code blocks executed via the <xref linkend="sql-do"> command.
</para>
***************
*** 203,208 **** CREATE LANGUAGE plsample
--- 206,244 ----
</para>
<para>
+ If a checker is provided by a procedural language, it must be declared
+ as a function taking four arguments, one of type <type>oid</> second of
+ type <type>regclass</> third is text array (used for options) and fourth
+ of type <type>boolean</>.
+ The checker's result is a table which consists of
+ functionid of type <type>oid</> (oid of a checked function), lineno
+ of type <type>integer</> (representing line number of the function
+ body source), statement of type <type>text</> (statement type), sqlstate
+ of type <type>text</>, message of type <type>text</> (error message),
+ detail of type <type>text</> (detail of the error mesaage if any),
+ hint of type <type>text</> (hint for the error message if any),
+ level of type <type>text</> (error level), position of type
+ <type>integer</> (possition of the error in a query if there is a query)
+ and query of type <type>text</> (showing SQL statement in which
+ the error occured).
+ The checker will be called whenever a function is checked with
+ <command>CHECK FUNCTION</> or <command>CHECK TRIGGER</>.
+ The passed-in OID is the OID of the function's <classname>pg_proc</>
+ row. The passed-in <type>regclass</> is the OID of the
+ <classname>pg_class</> row of the table on which the trigger is defined,
+ or <symbol>InvalidOid</symbol> in the case of <command>CHECK FUNCTION</>.
+ The last parameter passed as <type>boolean</> defines if checker should
+ stop checking on first error.
+ The checker must fetch these rows in the usual way, and do
+ whatever checking is appropriate.
+ Typical checks include verifying that all referenced database objects
+ actually exist or style checks like verifying that all declared
+ variables are actually used. If the checker finds the function to be okay,
+ it should just return. If it finds a problem, it should return a row
+ describing error.
+ </para>
+
+ <para>
If an inline handler is provided by a procedural language, it
must be declared as a function taking a single parameter of type
<type>internal</>. The inline handler's result is ignored, so it is
*** a/doc/src/sgml/ref/allfiles.sgml
--- b/doc/src/sgml/ref/allfiles.sgml
***************
*** 40,45 **** Complete list of usable sgml source files in this directory.
--- 40,47 ----
<!ENTITY alterView SYSTEM "alter_view.sgml">
<!ENTITY analyze SYSTEM "analyze.sgml">
<!ENTITY begin SYSTEM "begin.sgml">
+ <!ENTITY checkFunction SYSTEM "check_function.sgml">
+ <!ENTITY checkTrigger SYSTEM "check_trigger.sgml">
<!ENTITY checkpoint SYSTEM "checkpoint.sgml">
<!ENTITY close SYSTEM "close.sgml">
<!ENTITY cluster SYSTEM "cluster.sgml">
*** a/doc/src/sgml/ref/create_language.sgml
--- b/doc/src/sgml/ref/create_language.sgml
***************
*** 23,29 **** PostgreSQL documentation
<synopsis>
CREATE [ OR REPLACE ] [ PROCEDURAL ] LANGUAGE <replaceable class="parameter">name</replaceable>
CREATE [ OR REPLACE ] [ TRUSTED ] [ PROCEDURAL ] LANGUAGE <replaceable class="parameter">name</replaceable>
! HANDLER <replaceable class="parameter">call_handler</replaceable> [ INLINE <replaceable class="parameter">inline_handler</replaceable> ] [ VALIDATOR <replaceable>valfunction</replaceable> ]
</synopsis>
</refsynopsisdiv>
--- 23,29 ----
<synopsis>
CREATE [ OR REPLACE ] [ PROCEDURAL ] LANGUAGE <replaceable class="parameter">name</replaceable>
CREATE [ OR REPLACE ] [ TRUSTED ] [ PROCEDURAL ] LANGUAGE <replaceable class="parameter">name</replaceable>
! HANDLER <replaceable class="parameter">call_handler</replaceable> [ INLINE <replaceable class="parameter">inline_handler</replaceable> ] [ VALIDATOR <replaceable>valfunction</replaceable> ] [ CHECK <replaceable>checkfunction</replaceable> ]
</synopsis>
</refsynopsisdiv>
***************
*** 217,222 **** CREATE [ OR REPLACE ] [ TRUSTED ] [ PROCEDURAL ] LANGUAGE <replaceable class="pa
--- 217,261 ----
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><literal>CHECK</literal> <replaceable class="parameter">checkfunction</replaceable></term>
+
+ <listitem>
+ <para><replaceable class="parameter">checkfunction</replaceable> is the
+ name of a previously registered function that will be called
+ by <command>CHECK FUNCTION</command> and <command>CHECK TRIGGER</command>
+ to check the bodies of functions defined in the language.
+ If no check function is specified, this kind of check is not available.
+ The check function must take four arguments, first of type <type>oid</type>
+ (the OID of the function to be checked), second of type <type>regclass</type>
+ (the table with the trigger for <command>CHECK TRIGGER</command>),
+ third is text array (options) and fourth is boolean (defines if check
+ function should stop on first error found).
+ The check function will typically return set of rows with ten fields:
+ functionid of type <type>oid</> (oid of a checked function), lineno
+ of type <type>integer</> (representing line number of the function
+ body source), statement of type <type>text</> (statement type), sqlstate
+ of type <type>text</>, message of type <type>text</> (error message),
+ detail of type <type>text</> (detail of the error mesaage if any),
+ hint of type <type>text</> (hint for the error message if any),
+ level of type <type>text</> (error level), position of type
+ <type>integer</> (possition of the error in a query if there is a query)
+ and query of type <type>text</> (showing SQL statement in which
+ the error occured).
+ </para>
+
+ <para>
+ A check function would typically perform an in-depth check of the function
+ body, for example that all referenced database objects exist, but it could
+ also check for stylistic problems like defined but unreferenced variables.
+ To signal a problem found during the check, the function should return row
+ for each error found, if fourth input argument is True, it should return
+ immediately after first error.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
<para>
*** a/doc/src/sgml/reference.sgml
--- b/doc/src/sgml/reference.sgml
***************
*** 68,73 ****
--- 68,75 ----
&alterView;
&analyze;
&begin;
+ &checkFunction;
+ &checkTrigger;
&checkpoint;
&close;
&cluster;
*** a/src/backend/catalog/pg_proc.c
--- b/src/backend/catalog/pg_proc.c
***************
*** 1101,1103 **** fail:
--- 1101,1104 ----
*newcursorpos = newcp;
return false;
}
+
*** a/src/backend/commands/functioncmds.c
--- b/src/backend/commands/functioncmds.c
***************
*** 35,53 ****
--- 35,59 ----
#include "access/genam.h"
#include "access/heapam.h"
#include "access/sysattr.h"
+ #include "access/xact.h"
#include "catalog/dependency.h"
#include "catalog/indexing.h"
#include "catalog/objectaccess.h"
#include "catalog/pg_aggregate.h"
#include "catalog/pg_cast.h"
#include "catalog/pg_language.h"
+ #include "catalog/namespace.h"
#include "catalog/pg_namespace.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_proc_fn.h"
+ #include "catalog/pg_trigger.h"
#include "catalog/pg_type.h"
#include "catalog/pg_type_fn.h"
#include "commands/defrem.h"
#include "commands/proclang.h"
+ #include "commands/trigger.h"
+ #include "executor/executor.h"
+ #include "executor/spi_priv.h"
#include "miscadmin.h"
#include "optimizer/var.h"
#include "parser/parse_coerce.h"
***************
*** 60,66 ****
--- 66,74 ----
#include "utils/fmgroids.h"
#include "utils/guc.h"
#include "utils/lsyscache.h"
+ #include "utils/memutils.h"
#include "utils/rel.h"
+ #include "utils/resowner.h"
#include "utils/syscache.h"
#include "utils/tqual.h"
***************
*** 779,785 **** interpret_AS_clause(Oid languageOid, const char *languageName,
}
-
/*
* CreateFunction
* Execute a CREATE FUNCTION utility statement.
--- 787,792 ----
***************
*** 1022,1027 **** RemoveFunctionById(Oid funcOid)
--- 1029,1523 ----
}
}
+ /*
+ * Search and execute related checker function,
+ *
+ * returns true, when checked function is valid
+ *
+ */
+ static bool
+ CheckFunctionById(Oid funcOid, Oid relid, ArrayType *options,
+ bool fatal_errors,
+ TupOutputState *tstate)
+ {
+ HeapTuple tup;
+ Form_pg_proc proc;
+ HeapTuple languageTuple;
+ Form_pg_language languageStruct;
+ Oid languageChecker;
+ char *funcname;
+ StringInfoData sinfo;
+ Form_pg_proc checker_fce;
+ HeapTuple checker_fce_tup;
+ bool result = true;
+
+ tup = SearchSysCache1(PROCOID, ObjectIdGetDatum(funcOid));
+ if (!HeapTupleIsValid(tup)) /* should not happen */
+ elog(ERROR, "cache lookup failed for function %u", funcOid);
+
+ proc = (Form_pg_proc) GETSTRUCT(tup);
+
+ languageTuple = SearchSysCache1(LANGOID, ObjectIdGetDatum(proc->prolang));
+ Assert(HeapTupleIsValid(languageTuple));
+
+ languageStruct = (Form_pg_language) GETSTRUCT(languageTuple);
+ languageChecker = languageStruct->lanchecker;
+
+ funcname = format_procedure(funcOid);
+
+ /* Check a function body */
+ if (OidIsValid(languageChecker))
+ {
+ Oid argtypes[4] = { REGPROCEDUREOID, REGCLASSOID, TEXTARRAYOID, BOOLOID };
+ bool nulls[4] = { false, false, false, false };
+ Datum values[4];
+ int i;
+
+ /* call a checker function and read result */
+ checker_fce_tup = SearchSysCache1(PROCOID, ObjectIdGetDatum(languageChecker));
+ if (!HeapTupleIsValid(checker_fce_tup)) /* should not happen */
+ elog(ERROR, "cache lookup failed for function %u", funcOid);
+ checker_fce = (Form_pg_proc) GETSTRUCT(checker_fce_tup);
+
+ initStringInfo(&sinfo);
+ appendStringInfo(&sinfo, "SELECT * FROM %s($1, $2, $3, $4)",
+ quote_identifier(NameStr(checker_fce->proname)));
+ ReleaseSysCache(checker_fce_tup);
+
+ /*
+ * Connect to SPI manager
+ */
+ if (SPI_connect() != SPI_OK_CONNECT)
+ elog(ERROR, "SPI_connect failed");
+
+ values[0] = ObjectIdGetDatum(funcOid);
+ values[1] = ObjectIdGetDatum(relid);
+ values[2] = PointerGetDatum(options);
+ values[3] = BoolGetDatum(fatal_errors);
+
+ SPI_execute_with_args(sinfo.data,
+ 4, argtypes,
+ values, nulls,
+ true, 0);
+
+ result = SPI_processed == 0;
+
+ if (result)
+ {
+ resetStringInfo(&sinfo);
+ appendStringInfo(&sinfo, "Function is valid: '%s'", funcname);
+ do_text_output_oneline(tstate, sinfo.data);
+ }
+ else
+ {
+ resetStringInfo(&sinfo);
+ appendStringInfo(&sinfo, "In function: '%s'", funcname);
+ do_text_output_oneline(tstate, sinfo.data);
+
+ for (i = 0; i < SPI_processed; i++)
+ {
+ char *query;
+
+ resetStringInfo(&sinfo);
+ appendStringInfo(&sinfo, "%s:%s:%s:%s:%s",
+ SPI_getvalue(SPI_tuptable->vals[i], SPI_tuptable->tupdesc, 8),
+ SPI_getvalue(SPI_tuptable->vals[i], SPI_tuptable->tupdesc, 4),
+ SPI_getvalue(SPI_tuptable->vals[i], SPI_tuptable->tupdesc, 2),
+ SPI_getvalue(SPI_tuptable->vals[i], SPI_tuptable->tupdesc, 3),
+ SPI_getvalue(SPI_tuptable->vals[i], SPI_tuptable->tupdesc, 5));
+
+ do_text_output_oneline(tstate, sinfo.data);
+ resetStringInfo(&sinfo);
+
+ query = SPI_getvalue(SPI_tuptable->vals[i], SPI_tuptable->tupdesc, 10);
+ if (query != NULL)
+ {
+ int position;
+ Datum value;
+ bool isnull;
+
+ appendStringInfo(&sinfo, "query:%s", query);
+ do_text_output_oneline(tstate, sinfo.data);
+
+ value = SPI_getbinval(SPI_tuptable->vals[i], SPI_tuptable->tupdesc, 9, &isnull);
+
+ if (!isnull)
+ {
+ position = DatumGetInt32( value);
+ if (position > 0)
+ {
+ resetStringInfo(&sinfo);
+
+ appendStringInfo(&sinfo, " %*s",
+ position, "^");
+ do_text_output_oneline(tstate, sinfo.data);
+ }
+ }
+ }
+ }
+ }
+
+ /*
+ * Disconnect from SPI manager
+ */
+ if (SPI_finish() != SPI_OK_FINISH)
+ elog(ERROR, "SPI_finish failed");
+ }
+
+ pfree(funcname);
+
+ ReleaseSysCache(languageTuple);
+ ReleaseSysCache(tup);
+
+ return result;
+ }
+
+ TupleDesc
+ CheckFunctionResultDesc(CheckFunctionStmt *stmt)
+ {
+ TupleDesc tupdesc;
+
+ tupdesc = CreateTemplateTupleDesc(1, false);
+
+ TupleDescInitEntry(tupdesc, (AttrNumber) 1,
+ stmt->is_function ? "CHECK FUNCTION" : "CHECK TRIGGER",
+ TEXTOID, -1, 0);
+
+ return tupdesc;
+ }
+
+ /*
+ * CheckFunction
+ * call a PL checker function when this function exists.
+ */
+ void
+ CheckFunction(CheckFunctionStmt *stmt,
+ DestReceiver *dest)
+ {
+ List *functionName = stmt->funcname;
+ List *argTypes = stmt->args; /* list of TypeName nodes */
+ Oid funcOid = InvalidOid;
+ HeapTuple tup;
+ Oid trgOid = InvalidOid;
+ Oid relid = InvalidOid;
+ Oid languageId;
+ int nkeys = 0;
+ List *objects = NIL;
+ ArrayType *options;
+ ListCell *option;
+ bool stop_on_first_error = false;
+ ArrayBuildState *astate = NULL;
+ int dims[2] = {0, 2};
+ int lbs[2] = {1, 1};
+ TupOutputState *tstate;
+
+ tstate = begin_tup_output_tupdesc(dest, CheckFunctionResultDesc(stmt));
+
+ /*
+ * iterate over defelem check_options and serialize it to two dimensional
+ * text array. These option will be send to checker_function. When options
+ * are not entered, then prepare empty array - it's necessary - checker
+ * function is strict.
+ */
+ if (stmt->check_options != NIL)
+ {
+
+ /* complete dimensions */
+ dims[0] = list_length(stmt->check_options);
+
+ foreach(option, stmt->check_options)
+ {
+ DefElem *defel = (DefElem *) lfirst(option);
+ char *option_name = defel->defname;
+ Datum value;
+ bool isnull;
+
+ astate = accumArrayResult(astate,
+ CStringGetTextDatum(option_name), false,
+ TEXTOID,
+ CurrentMemoryContext);
+
+ if (strcmp(option_name, "fatal_errors") == 0)
+ {
+ stop_on_first_error = defGetBoolean(defel);
+ value = stop_on_first_error ? CStringGetTextDatum("on") : CStringGetTextDatum("off");
+ isnull = false;
+ }
+ else if (defel->arg != NULL)
+ {
+ value = CStringGetTextDatum(defGetString(defel));
+ isnull = false;
+ }
+ else
+ {
+ value = (Datum) 0;
+ isnull = true;
+ }
+
+ astate = accumArrayResult(astate,
+ value, isnull,
+ TEXTOID, CurrentMemoryContext);
+ }
+ }
+
+ if (astate != NULL)
+ options = DatumGetArrayTypeP(makeMdArrayResult(astate, 2, dims, lbs,
+ CurrentMemoryContext, true));
+ else
+ options = construct_empty_array(TEXTOID);
+
+ if (stmt->funcname != NULL)
+ {
+ /*
+ * Find the function,
+ */
+ funcOid = LookupFuncNameTypeNames(functionName, argTypes, false);
+ }
+ else if (stmt->trgname != NULL)
+ {
+ HeapTuple ht_trig;
+ Form_pg_trigger trigrec;
+ ScanKeyData skey[1];
+ Relation tgrel;
+ SysScanDesc tgscan;
+
+ /* find a trigger function */
+ relid = RangeVarGetRelid(stmt->relation, ShareLock, false);
+ trgOid = get_trigger_oid(relid, stmt->trgname, false);
+
+ /*
+ * Fetch the pg_trigger tuple by the Oid of the trigger
+ */
+ tgrel = heap_open(TriggerRelationId, AccessShareLock);
+
+ ScanKeyInit(&skey[0],
+ ObjectIdAttributeNumber,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(trgOid));
+
+ tgscan = systable_beginscan(tgrel, TriggerOidIndexId, true,
+ SnapshotNow, 1, skey);
+
+ ht_trig = systable_getnext(tgscan);
+
+ if (!HeapTupleIsValid(ht_trig))
+ elog(ERROR, "could not find tuple for trigger %u", trgOid);
+
+ trigrec = (Form_pg_trigger) GETSTRUCT(ht_trig);
+
+ /* we need to know trigger function to get PL checker function */
+ funcOid = trigrec->tgfoid;
+
+ /* Clean up */
+ systable_endscan(tgscan);
+
+ heap_close(tgrel, AccessShareLock);
+ }
+ else
+ {
+ ScanKeyData key[5];
+ Relation rel;
+ HeapScanDesc scan;
+ bool language_opt = false;
+ bool schema_opt = false;
+ bool owner_opt = false;
+
+ /*
+ * when Check stmt is multiple statement, then
+ * prepare list of processed functions.
+ */
+ foreach(option, stmt->options)
+ {
+ DefElem *defel = (DefElem *) lfirst(option);
+
+ if (strcmp(defel->defname, "language") == 0)
+ {
+ HeapTuple languageTuple;
+ Form_pg_language languageStruct;
+ Oid languageChecker;
+ char *language = defGetString(defel);
+
+ if (language_opt)
+ elog(ERROR, "multiple usage of IN LANGUAGE option");
+ language_opt = true;
+
+ languageTuple = SearchSysCache1(LANGNAME, PointerGetDatum(language));
+ if (!HeapTupleIsValid(languageTuple))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("language \"%s\" does not exist", language),
+ (PLTemplateExists(language) ?
+ errhint("Use CREATE LANGUAGE to load the language into the database.") : 0)));
+
+ languageId = HeapTupleGetOid(languageTuple);
+ if (languageId == INTERNALlanguageId || languageId == ClanguageId)
+ elog(ERROR, "cannot to check functions in C or internal languages");
+
+ languageStruct = (Form_pg_language) GETSTRUCT(languageTuple);
+ languageChecker = languageStruct->lanchecker;
+ if (!OidIsValid(languageChecker))
+ elog(ERROR, "language \"%s\" has no defined checker function",
+ language);
+
+ ScanKeyInit(&key[nkeys++],
+ Anum_pg_proc_prolang,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(languageId));
+
+ ReleaseSysCache(languageTuple);
+ }
+ else if (strcmp(defel->defname, "schema") == 0)
+ {
+ Oid namespaceId = LookupExplicitNamespace(defGetString(defel));
+
+ if (schema_opt)
+ elog(ERROR, "multiple usage of IN SCHEMA option");
+ schema_opt = true;
+
+ ScanKeyInit(&key[nkeys++],
+ Anum_pg_proc_pronamespace,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(namespaceId));
+
+ }
+ else if (strcmp(defel->defname, "owner") == 0)
+ {
+ Oid ownerId = get_role_oid(defGetString(defel), false);
+
+ if (owner_opt)
+ elog(ERROR, "multiple usage of FOR ROLE option");
+ owner_opt = true;
+
+ ScanKeyInit(&key[nkeys++],
+ Anum_pg_proc_proowner,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(ownerId));
+ }
+ else
+ elog(ERROR, "option \"%s\" not recognized",
+ defel->defname);
+ }
+
+ /*
+ * We don't would to iterate over pg_catalog functions without
+ * explicit request and information_schema.
+ */
+ if (!schema_opt)
+ {
+ Oid information_schemaId = LookupExplicitNamespace("information_schema");
+
+ ScanKeyInit(&key[nkeys++],
+ Anum_pg_proc_pronamespace,
+ BTEqualStrategyNumber, F_OIDNE,
+ ObjectIdGetDatum(PG_CATALOG_NAMESPACE));
+
+ ScanKeyInit(&key[nkeys++],
+ Anum_pg_proc_pronamespace,
+ BTEqualStrategyNumber, F_OIDNE,
+ ObjectIdGetDatum(information_schemaId));
+ }
+
+ rel = heap_open(ProcedureRelationId,AccessShareLock);
+ scan = heap_beginscan(rel, SnapshotNow, nkeys, key);
+
+ while (HeapTupleIsValid(tup = heap_getnext(scan, ForwardScanDirection)))
+ {
+ Form_pg_proc proc = (Form_pg_proc) GETSTRUCT(tup);
+
+ funcOid = HeapTupleGetOid(tup);
+
+ /* when language is not specified, then check checker */
+ if (!language_opt)
+ {
+ Oid prolang = proc->prolang;
+
+ /* skip C, internal or SQL */
+ if (prolang == INTERNALlanguageId)
+ {
+ elog(NOTICE, "skip check function \"%s\", it uses internal language",
+ format_procedure(funcOid));
+ continue;
+ }
+ else if (prolang == ClanguageId)
+ {
+ elog(NOTICE, "skip check function \"%s\", uses C language",
+ format_procedure(funcOid));
+ continue;
+ }
+ else if (prolang == SQLlanguageId)
+ {
+ elog(NOTICE, "skip check function \"%s\", uses SQL language",
+ format_procedure(funcOid));
+ continue;
+ }
+ }
+
+ if (proc->prorettype == TRIGGEROID)
+ {
+ elog(NOTICE, "skip check function \"%s\", it is trigger function",
+ format_procedure(funcOid));
+ continue;
+ }
+
+ objects = lappend_oid(objects, funcOid);
+ }
+
+ heap_endscan(scan);
+ heap_close(rel, AccessShareLock);
+ }
+
+ if (funcOid == InvalidOid && objects == NIL)
+ {
+ elog(NOTICE, "nothing to check");
+ return;
+ }
+
+ if (objects != NIL)
+ {
+ ListCell *object;
+ bool isfirst = true;
+ bool prev_error = false;
+
+ foreach(object, objects)
+ {
+ CHECK_FOR_INTERRUPTS();
+
+ /*
+ * append empty line when prev. was error,
+ * for better readability.
+ */
+ if (!isfirst && prev_error)
+ do_text_output_oneline(tstate, "");
+
+ funcOid = lfirst_oid(object);
+ if (!CheckFunctionById(funcOid, InvalidOid, options,
+ stop_on_first_error,
+ tstate))
+ {
+ prev_error = true;
+ if (stop_on_first_error)
+ break;
+ }
+ else
+ prev_error = false;
+
+ isfirst = false;
+ }
+ }
+ else
+ {
+ CheckFunctionById(funcOid, relid, options,
+ stop_on_first_error,
+ tstate);
+ }
+
+ end_tup_output(tstate);
+ }
+
+ /*
+ * CheckFunctionResultDesc -
+ * construct the result tupledesc for an CHECK
+ */
/*
* Rename function
*** a/src/backend/commands/proclang.c
--- b/src/backend/commands/proclang.c
***************
*** 46,57 **** typedef struct
char *tmplhandler; /* name of handler function */
char *tmplinline; /* name of anonymous-block handler, or NULL */
char *tmplvalidator; /* name of validator function, or NULL */
char *tmpllibrary; /* path of shared library */
} PLTemplate;
static void create_proc_lang(const char *languageName, bool replace,
Oid languageOwner, Oid handlerOid, Oid inlineOid,
! Oid valOid, bool trusted);
static PLTemplate *find_language_template(const char *languageName);
static void AlterLanguageOwner_internal(HeapTuple tup, Relation rel,
Oid newOwnerId);
--- 46,58 ----
char *tmplhandler; /* name of handler function */
char *tmplinline; /* name of anonymous-block handler, or NULL */
char *tmplvalidator; /* name of validator function, or NULL */
+ char *tmplchecker; /* name of checker function, or NULL */
char *tmpllibrary; /* path of shared library */
} PLTemplate;
static void create_proc_lang(const char *languageName, bool replace,
Oid languageOwner, Oid handlerOid, Oid inlineOid,
! Oid valOid, Oid checkerOid, bool trusted);
static PLTemplate *find_language_template(const char *languageName);
static void AlterLanguageOwner_internal(HeapTuple tup, Relation rel,
Oid newOwnerId);
***************
*** 67,75 **** CreateProceduralLanguage(CreatePLangStmt *stmt)
PLTemplate *pltemplate;
Oid handlerOid,
inlineOid,
! valOid;
Oid funcrettype;
! Oid funcargtypes[1];
/*
* If we have template information for the language, ignore the supplied
--- 68,77 ----
PLTemplate *pltemplate;
Oid handlerOid,
inlineOid,
! valOid,
! checkerOid;
Oid funcrettype;
! Oid funcargtypes[4];
/*
* If we have template information for the language, ignore the supplied
***************
*** 219,228 **** CreateProceduralLanguage(CreatePLangStmt *stmt)
else
valOid = InvalidOid;
/* ok, create it */
create_proc_lang(stmt->plname, stmt->replace, GetUserId(),
handlerOid, inlineOid,
! valOid, pltemplate->tmpltrusted);
}
else
{
--- 221,309 ----
else
valOid = InvalidOid;
+ /*
+ * Likewise for the checker, if required; but we don't care about
+ * its return type.
+ */
+ if (pltemplate->tmplchecker)
+ {
+ funcname = SystemFuncName(pltemplate->tmplchecker);
+ funcargtypes[0] = REGPROCEDUREOID;
+ funcargtypes[1] = REGCLASSOID;
+ funcargtypes[2] = TEXTARRAYOID;
+ funcargtypes[3] = BOOLOID;
+
+ checkerOid = LookupFuncName(funcname, 4, funcargtypes, true);
+ if (!OidIsValid(checkerOid))
+ {
+ Datum allTypes[14];
+ Datum paramModes[14];
+ Datum paramNames[14];
+ ArrayType *allParameterTypes;
+ ArrayType *parameterModes;
+ ArrayType *parameterNames;
+
+ Oid Types[14] = { REGPROCEDUREOID, REGCLASSOID, TEXTARRAYOID, BOOLOID,
+ OIDOID, INT4OID, TEXTOID, TEXTOID,
+ TEXTOID, TEXTOID, TEXTOID, TEXTOID,
+ INT4OID, TEXTOID };
+
+ char Modes[14] = { FUNC_PARAM_IN, FUNC_PARAM_IN, FUNC_PARAM_IN, FUNC_PARAM_IN,
+ FUNC_PARAM_OUT, FUNC_PARAM_OUT, FUNC_PARAM_OUT, FUNC_PARAM_OUT,
+ FUNC_PARAM_OUT, FUNC_PARAM_OUT, FUNC_PARAM_OUT, FUNC_PARAM_OUT,
+ FUNC_PARAM_OUT, FUNC_PARAM_OUT };
+
+ char *Names[14] = { "functionid", "tableid", "options", "fatal_errors",
+ "functionid", "lineno", "statement", "sqlstate",
+ "message", "detail", "hint", "level",
+ "position", "query" };
+ int i;
+
+ for (i = 0; i < 14; i++)
+ {
+ allTypes[i] = ObjectIdGetDatum(Types[i]);
+ paramModes[i] = CharGetDatum(Modes[i]);
+ paramNames[i] = CStringGetTextDatum(Names[i]);
+ }
+
+ allParameterTypes = construct_array(allTypes, 14, OIDOID,
+ sizeof(Oid), true, 'i');
+ parameterModes = construct_array(paramModes, 14, CHAROID,
+ 1, true, 'c');
+ parameterNames = construct_array(paramNames, 14, TEXTOID,
+ -1, false, 'i');
+
+ checkerOid = ProcedureCreate(pltemplate->tmplchecker,
+ PG_CATALOG_NAMESPACE,
+ false, /* replace */
+ true, /* returnsSet */
+ RECORDOID,
+ ClanguageId,
+ F_FMGR_C_VALIDATOR,
+ pltemplate->tmplchecker,
+ pltemplate->tmpllibrary,
+ false, /* isAgg */
+ false, /* isWindowFunc */
+ false, /* security_definer */
+ true, /* isStrict */
+ PROVOLATILE_VOLATILE,
+ buildoidvector(funcargtypes, 4), /* parameterTypes */
+ PointerGetDatum(allParameterTypes),
+ PointerGetDatum(parameterModes),
+ PointerGetDatum(parameterNames),
+ NIL,
+ PointerGetDatum(NULL),
+ 1,
+ 100);
+ }
+ }
+ else
+ checkerOid = InvalidOid;
+
/* ok, create it */
create_proc_lang(stmt->plname, stmt->replace, GetUserId(),
handlerOid, inlineOid,
! valOid, checkerOid, pltemplate->tmpltrusted);
}
else
{
***************
*** 294,303 **** CreateProceduralLanguage(CreatePLangStmt *stmt)
else
valOid = InvalidOid;
/* ok, create it */
create_proc_lang(stmt->plname, stmt->replace, GetUserId(),
handlerOid, inlineOid,
! valOid, stmt->pltrusted);
}
}
--- 375,396 ----
else
valOid = InvalidOid;
+ /* validate the checker function */
+ if (stmt->plchecker)
+ {
+ funcargtypes[0] = OIDOID;
+ funcargtypes[1] = REGCLASSOID;
+ funcargtypes[2] = TEXTARRAYOID;
+ checkerOid = LookupFuncName(stmt->plchecker, 3, funcargtypes, false);
+ /* return value is ignored, so we don't check the type */
+ }
+ else
+ checkerOid = InvalidOid;
+
/* ok, create it */
create_proc_lang(stmt->plname, stmt->replace, GetUserId(),
handlerOid, inlineOid,
! valOid, checkerOid, stmt->pltrusted);
}
}
***************
*** 307,313 **** CreateProceduralLanguage(CreatePLangStmt *stmt)
static void
create_proc_lang(const char *languageName, bool replace,
Oid languageOwner, Oid handlerOid, Oid inlineOid,
! Oid valOid, bool trusted)
{
Relation rel;
TupleDesc tupDesc;
--- 400,406 ----
static void
create_proc_lang(const char *languageName, bool replace,
Oid languageOwner, Oid handlerOid, Oid inlineOid,
! Oid valOid, Oid checkerOid, bool trusted)
{
Relation rel;
TupleDesc tupDesc;
***************
*** 337,342 **** create_proc_lang(const char *languageName, bool replace,
--- 430,436 ----
values[Anum_pg_language_lanplcallfoid - 1] = ObjectIdGetDatum(handlerOid);
values[Anum_pg_language_laninline - 1] = ObjectIdGetDatum(inlineOid);
values[Anum_pg_language_lanvalidator - 1] = ObjectIdGetDatum(valOid);
+ values[Anum_pg_language_lanchecker - 1] = ObjectIdGetDatum(checkerOid);
nulls[Anum_pg_language_lanacl - 1] = true;
/* Check for pre-existing definition */
***************
*** 423,428 **** create_proc_lang(const char *languageName, bool replace,
--- 517,531 ----
recordDependencyOn(&myself, &referenced, DEPENDENCY_NORMAL);
}
+ /* dependency on the checker function, if any */
+ if (OidIsValid(checkerOid))
+ {
+ referenced.classId = ProcedureRelationId;
+ referenced.objectId = checkerOid;
+ referenced.objectSubId = 0;
+ recordDependencyOn(&myself, &referenced, DEPENDENCY_NORMAL);
+ }
+
/* Post creation hook for new procedural language */
InvokeObjectAccessHook(OAT_POST_CREATE,
LanguageRelationId, myself.objectId, 0);
***************
*** 478,483 **** find_language_template(const char *languageName)
--- 581,591 ----
if (!isnull)
result->tmplvalidator = TextDatumGetCString(datum);
+ datum = heap_getattr(tup, Anum_pg_pltemplate_tmplchecker,
+ RelationGetDescr(rel), &isnull);
+ if (!isnull)
+ result->tmplchecker = TextDatumGetCString(datum);
+
datum = heap_getattr(tup, Anum_pg_pltemplate_tmpllibrary,
RelationGetDescr(rel), &isnull);
if (!isnull)
*** a/src/backend/nodes/copyfuncs.c
--- b/src/backend/nodes/copyfuncs.c
***************
*** 2883,2888 **** _copyAlterFunctionStmt(const AlterFunctionStmt *from)
--- 2883,2904 ----
return newnode;
}
+ static CheckFunctionStmt *
+ _copyCheckFunctionStmt(const CheckFunctionStmt *from)
+ {
+ CheckFunctionStmt *newnode = makeNode(CheckFunctionStmt);
+
+ COPY_NODE_FIELD(funcname);
+ COPY_NODE_FIELD(args);
+ COPY_STRING_FIELD(trgname);
+ COPY_NODE_FIELD(relation);
+ COPY_SCALAR_FIELD(is_function);
+ COPY_NODE_FIELD(options);
+ COPY_NODE_FIELD(check_options);
+
+ return newnode;
+ }
+
static DoStmt *
_copyDoStmt(const DoStmt *from)
{
***************
*** 4171,4176 **** copyObject(const void *from)
--- 4187,4195 ----
case T_AlterFunctionStmt:
retval = _copyAlterFunctionStmt(from);
break;
+ case T_CheckFunctionStmt:
+ retval = _copyCheckFunctionStmt(from);
+ break;
case T_DoStmt:
retval = _copyDoStmt(from);
break;
*** a/src/backend/nodes/equalfuncs.c
--- b/src/backend/nodes/equalfuncs.c
***************
*** 1294,1299 **** _equalAlterFunctionStmt(const AlterFunctionStmt *a, const AlterFunctionStmt *b)
--- 1294,1313 ----
}
static bool
+ _equalCheckFunctionStmt(const CheckFunctionStmt *a, const CheckFunctionStmt *b)
+ {
+ COMPARE_NODE_FIELD(funcname);
+ COMPARE_NODE_FIELD(args);
+ COMPARE_STRING_FIELD(trgname);
+ COMPARE_NODE_FIELD(relation);
+ COMPARE_SCALAR_FIELD(is_function);
+ COMPARE_NODE_FIELD(options);
+ COMPARE_NODE_FIELD(check_options);
+
+ return true;
+ }
+
+ static bool
_equalDoStmt(const DoStmt *a, const DoStmt *b)
{
COMPARE_NODE_FIELD(args);
***************
*** 2714,2719 **** equal(const void *a, const void *b)
--- 2728,2736 ----
case T_AlterFunctionStmt:
retval = _equalAlterFunctionStmt(a, b);
break;
+ case T_CheckFunctionStmt:
+ retval = _equalCheckFunctionStmt(a, b);
+ break;
case T_DoStmt:
retval = _equalDoStmt(a, b);
break;
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
***************
*** 227,232 **** static void processCASbits(int cas_bits, int location, const char *constrType,
--- 227,233 ----
DeallocateStmt PrepareStmt ExecuteStmt
DropOwnedStmt ReassignOwnedStmt
AlterTSConfigurationStmt AlterTSDictionaryStmt
+ CheckFunctionStmt
%type <node> select_no_parens select_with_parens select_clause
simple_select values_clause
***************
*** 276,282 **** static void processCASbits(int cas_bits, int location, const char *constrType,
%type <list> func_name handler_name qual_Op qual_all_Op subquery_Op
opt_class opt_inline_handler opt_validator validator_clause
! opt_collate
%type <range> qualified_name OptConstrFromTable
--- 277,283 ----
%type <list> func_name handler_name qual_Op qual_all_Op subquery_Op
opt_class opt_inline_handler opt_validator validator_clause
! opt_collate opt_checker
%type <range> qualified_name OptConstrFromTable
***************
*** 463,468 **** static void processCASbits(int cas_bits, int location, const char *constrType,
--- 464,473 ----
%type <windef> window_definition over_clause window_specification
opt_frame_clause frame_extent frame_bound
%type <str> opt_existing_window_name
+ %type <defelt> CheckFunctionOptElem check_option_elem
+ %type <list> CheckFunctionOpts OptCheckFunctionOpts check_option_list opt_check_options
+ %type <str> check_option_name
+ %type <node> check_option_arg
/*
***************
*** 700,705 **** stmt :
--- 705,711 ----
| AlterUserSetStmt
| AlterUserStmt
| AnalyzeStmt
+ | CheckFunctionStmt
| CheckPointStmt
| ClosePortalStmt
| ClusterStmt
***************
*** 3216,3226 **** CreatePLangStmt:
n->plhandler = NIL;
n->plinline = NIL;
n->plvalidator = NIL;
n->pltrusted = false;
$$ = (Node *)n;
}
| CREATE opt_or_replace opt_trusted opt_procedural LANGUAGE ColId_or_Sconst
! HANDLER handler_name opt_inline_handler opt_validator
{
CreatePLangStmt *n = makeNode(CreatePLangStmt);
n->replace = $2;
--- 3222,3233 ----
n->plhandler = NIL;
n->plinline = NIL;
n->plvalidator = NIL;
+ n->plchecker = NIL;
n->pltrusted = false;
$$ = (Node *)n;
}
| CREATE opt_or_replace opt_trusted opt_procedural LANGUAGE ColId_or_Sconst
! HANDLER handler_name opt_inline_handler opt_validator opt_checker
{
CreatePLangStmt *n = makeNode(CreatePLangStmt);
n->replace = $2;
***************
*** 3228,3233 **** CreatePLangStmt:
--- 3235,3241 ----
n->plhandler = $8;
n->plinline = $9;
n->plvalidator = $10;
+ n->plchecker = $11;
n->pltrusted = $3;
$$ = (Node *)n;
}
***************
*** 3262,3267 **** opt_validator:
--- 3270,3280 ----
| /*EMPTY*/ { $$ = NIL; }
;
+ opt_checker:
+ CHECK handler_name { $$ = $2; }
+ | /*EMPTY*/ { $$ = NIL; }
+ ;
+
DropPLangStmt:
DROP opt_procedural LANGUAGE ColId_or_Sconst opt_drop_behavior
{
***************
*** 6319,6324 **** any_operator:
--- 6332,6459 ----
/*****************************************************************************
*
+ * CHECK FUNCTION funcname(args)
+ * CHECK TRIGGER triggername ON table
+ *
+ *
+ *****************************************************************************/
+
+
+ CheckFunctionStmt:
+ CHECK FUNCTION func_name func_args opt_check_options
+ {
+ CheckFunctionStmt *n = makeNode(CheckFunctionStmt);
+ n->funcname = $3;
+ n->args = extractArgTypes($4);
+ n->trgname = NULL;
+ n->relation = NULL;
+ n->is_function = true;
+ n->options = NIL;
+ n->check_options = $5;
+ $$ = (Node *) n;
+ }
+ | CHECK TRIGGER name ON qualified_name opt_check_options
+ {
+ CheckFunctionStmt *n = makeNode(CheckFunctionStmt);
+ n->funcname = NULL;
+ n->args = NIL;
+ n->trgname = $3;
+ n->relation = $5;
+ n->is_function = false;
+ n->options = NIL;
+ n->check_options = $6;
+ $$ = (Node *) n;
+ }
+ | CHECK FUNCTION ALL OptCheckFunctionOpts opt_check_options
+ {
+ CheckFunctionStmt *n = makeNode(CheckFunctionStmt);
+ n->funcname = NULL;
+ n->args = NIL;
+ n->trgname = NULL;
+ n->relation = NULL;
+ n->is_function = true;
+ n->options = $4;
+ n->check_options = $5;
+ $$ = (Node *) n;
+ }
+ ;
+
+ OptCheckFunctionOpts:
+ CheckFunctionOpts
+ {
+ $$ = $1;
+ }
+ | /* EMPTY */
+ {
+ $$ = NIL;
+ }
+ ;
+
+ CheckFunctionOpts:
+ CheckFunctionOptElem { $$ = list_make1($1); }
+ | CheckFunctionOpts CheckFunctionOptElem { $$ = lappend($1, $2); }
+ ;
+
+ CheckFunctionOptElem:
+ IN_P LANGUAGE ColId
+ {
+ $$ = makeDefElem("language",
+ (Node *) makeString($3));
+ }
+ | IN_P SCHEMA ColId
+ {
+ $$ = makeDefElem("schema",
+ (Node *) makeString($3));
+ }
+ | FOR ROLE ColId
+ {
+ $$ = makeDefElem("owner",
+ (Node *) makeString($3));
+ }
+ ;
+
+ opt_check_options:
+ WITH '(' check_option_list ')'
+ {
+ $$ = $3;
+ }
+ | /* EMPTY */
+ {
+ $$ = NIL;
+ }
+ ;
+
+ check_option_list:
+ check_option_elem
+ {
+ $$ = list_make1($1);
+ }
+ | check_option_list ',' check_option_elem
+ {
+ $$ = lappend($1, $3);
+ }
+ ;
+
+ check_option_elem:
+ check_option_name check_option_arg
+ {
+ $$ = makeDefElem($1, $2);
+ }
+ ;
+
+ check_option_name:
+ ColId { $$ = $1; }
+ | analyze_keyword { $$ = "analyze"; }
+ | VERBOSE { $$ = "verbose"; }
+ ;
+
+ check_option_arg:
+ opt_boolean_or_string { $$ = (Node *) makeString($1); }
+ | /* EMPTY */ { $$ = NULL; }
+ ;
+
+ /*****************************************************************************
+ *
* DO <anonymous code block> [ LANGUAGE language ]
*
* We use a DefElem list for future extensibility, and to allow flexibility
*** a/src/backend/tcop/utility.c
--- b/src/backend/tcop/utility.c
***************
*** 901,906 **** standard_ProcessUtility(Node *parsetree,
--- 901,910 ----
AlterFunction((AlterFunctionStmt *) parsetree);
break;
+ case T_CheckFunctionStmt:
+ CheckFunction((CheckFunctionStmt *) parsetree, dest);
+ break;
+
case T_IndexStmt: /* CREATE INDEX */
{
IndexStmt *stmt = (IndexStmt *) parsetree;
***************
*** 1243,1248 **** UtilityReturnsTuples(Node *parsetree)
--- 1247,1255 ----
case T_ExplainStmt:
return true;
+ case T_CheckFunctionStmt:
+ return true;
+
case T_VariableShowStmt:
return true;
***************
*** 1293,1298 **** UtilityTupleDescriptor(Node *parsetree)
--- 1300,1308 ----
case T_ExplainStmt:
return ExplainResultDesc((ExplainStmt *) parsetree);
+ case T_CheckFunctionStmt:
+ return CheckFunctionResultDesc((CheckFunctionStmt *) parsetree);
+
case T_VariableShowStmt:
{
VariableShowStmt *n = (VariableShowStmt *) parsetree;
***************
*** 2144,2149 **** CreateCommandTag(Node *parsetree)
--- 2154,2166 ----
}
break;
+ case T_CheckFunctionStmt:
+ if (((CheckFunctionStmt *) parsetree)->is_function)
+ tag = "CHECK FUNCTION";
+ else
+ tag = "CHECK TRIGGER";
+ break;
+
default:
elog(WARNING, "unrecognized node type: %d",
(int) nodeTag(parsetree));
***************
*** 2584,2589 **** GetCommandLogLevel(Node *parsetree)
--- 2601,2610 ----
}
break;
+ case T_CheckFunctionStmt:
+ lev = LOGSTMT_ALL;
+ break;
+
default:
elog(WARNING, "unrecognized node type: %d",
(int) nodeTag(parsetree));
*** a/src/bin/pg_dump/pg_dump.c
--- b/src/bin/pg_dump/pg_dump.c
***************
*** 5398,5410 **** getProcLangs(int *numProcLangs)
int i_lanplcallfoid;
int i_laninline;
int i_lanvalidator;
int i_lanacl;
int i_lanowner;
/* Make sure we are in proper schema */
selectSourceSchema("pg_catalog");
! if (g_fout->remoteVersion >= 90000)
{
/* pg_language has a laninline column */
appendPQExpBuffer(query, "SELECT tableoid, oid, "
--- 5398,5423 ----
int i_lanplcallfoid;
int i_laninline;
int i_lanvalidator;
+ int i_lanchecker;
int i_lanacl;
int i_lanowner;
/* Make sure we are in proper schema */
selectSourceSchema("pg_catalog");
! if (g_fout->remoteVersion >= 90200)
! {
! /* pg_language has a lanchecker column */
! appendPQExpBuffer(query, "SELECT tableoid, oid, "
! "lanname, lanpltrusted, lanplcallfoid, "
! "laninline, lanvalidator, lanchecker, lanacl, "
! "(%s lanowner) AS lanowner "
! "FROM pg_language "
! "WHERE lanispl "
! "ORDER BY oid",
! username_subquery);
! }
! else if (g_fout->remoteVersion >= 90000)
{
/* pg_language has a laninline column */
appendPQExpBuffer(query, "SELECT tableoid, oid, "
***************
*** 5481,5486 **** getProcLangs(int *numProcLangs)
--- 5494,5500 ----
/* these may fail and return -1: */
i_laninline = PQfnumber(res, "laninline");
i_lanvalidator = PQfnumber(res, "lanvalidator");
+ i_lanchecker = PQfnumber(res, "lanchecker");
i_lanacl = PQfnumber(res, "lanacl");
i_lanowner = PQfnumber(res, "lanowner");
***************
*** 5494,5499 **** getProcLangs(int *numProcLangs)
--- 5508,5517 ----
planginfo[i].dobj.name = pg_strdup(PQgetvalue(res, i, i_lanname));
planginfo[i].lanpltrusted = *(PQgetvalue(res, i, i_lanpltrusted)) == 't';
planginfo[i].lanplcallfoid = atooid(PQgetvalue(res, i, i_lanplcallfoid));
+ if (i_lanchecker >= 0)
+ planginfo[i].lanchecker = atooid(PQgetvalue(res, i, i_lanchecker));
+ else
+ planginfo[i].lanchecker = InvalidOid;
if (i_laninline >= 0)
planginfo[i].laninline = atooid(PQgetvalue(res, i, i_laninline));
else
***************
*** 8709,8714 **** dumpProcLang(Archive *fout, ProcLangInfo *plang)
--- 8727,8733 ----
char *qlanname;
char *lanschema;
FuncInfo *funcInfo;
+ FuncInfo *checkerInfo = NULL;
FuncInfo *inlineInfo = NULL;
FuncInfo *validatorInfo = NULL;
***************
*** 8728,8733 **** dumpProcLang(Archive *fout, ProcLangInfo *plang)
--- 8747,8759 ----
if (funcInfo != NULL && !funcInfo->dobj.dump)
funcInfo = NULL; /* treat not-dumped same as not-found */
+ if (OidIsValid(plang->lanchecker))
+ {
+ checkerInfo = findFuncByOid(plang->lanchecker);
+ if (checkerInfo != NULL && !checkerInfo->dobj.dump)
+ checkerInfo = NULL;
+ }
+
if (OidIsValid(plang->laninline))
{
inlineInfo = findFuncByOid(plang->laninline);
***************
*** 8754,8759 **** dumpProcLang(Archive *fout, ProcLangInfo *plang)
--- 8780,8786 ----
* don't, this might not work terribly nicely.
*/
useParams = (funcInfo != NULL &&
+ (checkerInfo != NULL || !OidIsValid(plang->lanchecker)) &&
(inlineInfo != NULL || !OidIsValid(plang->laninline)) &&
(validatorInfo != NULL || !OidIsValid(plang->lanvalidator)));
***************
*** 8809,8814 **** dumpProcLang(Archive *fout, ProcLangInfo *plang)
--- 8836,8851 ----
appendPQExpBuffer(defqry, "%s",
fmtId(validatorInfo->dobj.name));
}
+ if (OidIsValid(plang->lanchecker))
+ {
+ appendPQExpBuffer(defqry, " CHECK ");
+ /* Cope with possibility that checker is in different schema */
+ if (checkerInfo->dobj.namespace != funcInfo->dobj.namespace)
+ appendPQExpBuffer(defqry, "%s.",
+ fmtId(checkerInfo->dobj.namespace->dobj.name));
+ appendPQExpBuffer(defqry, "%s",
+ fmtId(checkerInfo->dobj.name));
+ }
}
else
{
*** a/src/bin/pg_dump/pg_dump.h
--- b/src/bin/pg_dump/pg_dump.h
***************
*** 390,395 **** typedef struct _procLangInfo
--- 390,396 ----
Oid lanplcallfoid;
Oid laninline;
Oid lanvalidator;
+ Oid lanchecker;
char *lanacl;
char *lanowner; /* name of owner, or empty string */
} ProcLangInfo;
*** a/src/bin/psql/tab-complete.c
--- b/src/bin/psql/tab-complete.c
***************
*** 1,4 ****
--- 1,5 ----
/*
+ *
* psql - the PostgreSQL interactive terminal
*
* Copyright (c) 2000-2012, PostgreSQL Global Development Group
***************
*** 727,733 **** psql_completion(char *text, int start, int end)
#define prev6_wd (previous_words[5])
static const char *const sql_commands[] = {
! "ABORT", "ALTER", "ANALYZE", "BEGIN", "CHECKPOINT", "CLOSE", "CLUSTER",
"COMMENT", "COMMIT", "COPY", "CREATE", "DEALLOCATE", "DECLARE",
"DELETE FROM", "DISCARD", "DO", "DROP", "END", "EXECUTE", "EXPLAIN", "FETCH",
"GRANT", "INSERT", "LISTEN", "LOAD", "LOCK", "MOVE", "NOTIFY", "PREPARE",
--- 728,734 ----
#define prev6_wd (previous_words[5])
static const char *const sql_commands[] = {
! "ABORT", "ALTER", "ANALYZE", "BEGIN", "CHECK", "CHECKPOINT", "CLOSE", "CLUSTER",
"COMMENT", "COMMIT", "COPY", "CREATE", "DEALLOCATE", "DECLARE",
"DELETE FROM", "DISCARD", "DO", "DROP", "END", "EXECUTE", "EXPLAIN", "FETCH",
"GRANT", "INSERT", "LISTEN", "LOAD", "LOCK", "MOVE", "NOTIFY", "PREPARE",
***************
*** 1524,1529 **** psql_completion(char *text, int start, int end)
--- 1525,1552 ----
COMPLETE_WITH_LIST(list_TRANS);
}
+
+ /* CHECK */
+ else if (pg_strcasecmp(prev_wd, "CHECK") == 0)
+ {
+ static const char *const list_CHECK[] =
+ {"FUNCTION", "TRIGGER", NULL};
+
+ COMPLETE_WITH_LIST(list_CHECK);
+ }
+ else if (pg_strcasecmp(prev3_wd, "CHECK") == 0 &&
+ pg_strcasecmp(prev2_wd, "TRIGGER") == 0)
+ {
+ COMPLETE_WITH_CONST("ON");
+ }
+ else if (pg_strcasecmp(prev4_wd, "CHECK") == 0 &&
+ pg_strcasecmp(prev3_wd, "TRIGGER") == 0 &&
+ pg_strcasecmp(prev_wd, "ON") == 0)
+ {
+ completion_info_charp = prev2_wd;
+ COMPLETE_WITH_QUERY(Query_for_list_of_tables_for_trigger);
+ }
+
/* CLUSTER */
/*
*** a/src/include/catalog/pg_language.h
--- b/src/include/catalog/pg_language.h
***************
*** 37,42 **** CATALOG(pg_language,2612)
--- 37,43 ----
Oid lanplcallfoid; /* Call handler for PL */
Oid laninline; /* Optional anonymous-block handler function */
Oid lanvalidator; /* Optional validation function */
+ Oid lanchecker; /* Optional checker function */
#ifdef CATALOG_VARLEN /* variable-length fields start here */
aclitem lanacl[1]; /* Access privileges */
#endif
***************
*** 53,59 **** typedef FormData_pg_language *Form_pg_language;
* compiler constants for pg_language
* ----------------
*/
! #define Natts_pg_language 8
#define Anum_pg_language_lanname 1
#define Anum_pg_language_lanowner 2
#define Anum_pg_language_lanispl 3
--- 54,60 ----
* compiler constants for pg_language
* ----------------
*/
! #define Natts_pg_language 9
#define Anum_pg_language_lanname 1
#define Anum_pg_language_lanowner 2
#define Anum_pg_language_lanispl 3
***************
*** 61,80 **** typedef FormData_pg_language *Form_pg_language;
#define Anum_pg_language_lanplcallfoid 5
#define Anum_pg_language_laninline 6
#define Anum_pg_language_lanvalidator 7
! #define Anum_pg_language_lanacl 8
/* ----------------
* initial contents of pg_language
* ----------------
*/
! DATA(insert OID = 12 ( "internal" PGUID f f 0 0 2246 _null_ ));
DESCR("built-in functions");
#define INTERNALlanguageId 12
! DATA(insert OID = 13 ( "c" PGUID f f 0 0 2247 _null_ ));
DESCR("dynamically-loaded C functions");
#define ClanguageId 13
! DATA(insert OID = 14 ( "sql" PGUID f t 0 0 2248 _null_ ));
DESCR("SQL-language functions");
#define SQLlanguageId 14
--- 62,82 ----
#define Anum_pg_language_lanplcallfoid 5
#define Anum_pg_language_laninline 6
#define Anum_pg_language_lanvalidator 7
! #define Anum_pg_language_lanchecker 8
! #define Anum_pg_language_lanacl 9
/* ----------------
* initial contents of pg_language
* ----------------
*/
! DATA(insert OID = 12 ( "internal" PGUID f f 0 0 2246 0 _null_ ));
DESCR("built-in functions");
#define INTERNALlanguageId 12
! DATA(insert OID = 13 ( "c" PGUID f f 0 0 2247 0 _null_ ));
DESCR("dynamically-loaded C functions");
#define ClanguageId 13
! DATA(insert OID = 14 ( "sql" PGUID f t 0 0 2248 0 _null_ ));
DESCR("SQL-language functions");
#define SQLlanguageId 14
*** a/src/include/catalog/pg_pltemplate.h
--- b/src/include/catalog/pg_pltemplate.h
***************
*** 37,42 **** CATALOG(pg_pltemplate,1136) BKI_SHARED_RELATION BKI_WITHOUT_OIDS
--- 37,43 ----
text tmplhandler; /* name of call handler function */
text tmplinline; /* name of anonymous-block handler, or NULL */
text tmplvalidator; /* name of validator function, or NULL */
+ text tmplchecker; /* name of checker function, or NULL */
text tmpllibrary; /* path of shared library */
aclitem tmplacl[1]; /* access privileges for template */
#endif
***************
*** 53,67 **** typedef FormData_pg_pltemplate *Form_pg_pltemplate;
* compiler constants for pg_pltemplate
* ----------------
*/
! #define Natts_pg_pltemplate 8
#define Anum_pg_pltemplate_tmplname 1
#define Anum_pg_pltemplate_tmpltrusted 2
#define Anum_pg_pltemplate_tmpldbacreate 3
#define Anum_pg_pltemplate_tmplhandler 4
#define Anum_pg_pltemplate_tmplinline 5
#define Anum_pg_pltemplate_tmplvalidator 6
! #define Anum_pg_pltemplate_tmpllibrary 7
! #define Anum_pg_pltemplate_tmplacl 8
/* ----------------
--- 54,69 ----
* compiler constants for pg_pltemplate
* ----------------
*/
! #define Natts_pg_pltemplate 9
#define Anum_pg_pltemplate_tmplname 1
#define Anum_pg_pltemplate_tmpltrusted 2
#define Anum_pg_pltemplate_tmpldbacreate 3
#define Anum_pg_pltemplate_tmplhandler 4
#define Anum_pg_pltemplate_tmplinline 5
#define Anum_pg_pltemplate_tmplvalidator 6
! #define Anum_pg_pltemplate_tmplchecker 7
! #define Anum_pg_pltemplate_tmpllibrary 8
! #define Anum_pg_pltemplate_tmplacl 9
/* ----------------
***************
*** 69,81 **** typedef FormData_pg_pltemplate *Form_pg_pltemplate;
* ----------------
*/
! DATA(insert ( "plpgsql" t t "plpgsql_call_handler" "plpgsql_inline_handler" "plpgsql_validator" "$libdir/plpgsql" _null_ ));
! DATA(insert ( "pltcl" t t "pltcl_call_handler" _null_ _null_ "$libdir/pltcl" _null_ ));
! DATA(insert ( "pltclu" f f "pltclu_call_handler" _null_ _null_ "$libdir/pltcl" _null_ ));
! DATA(insert ( "plperl" t t "plperl_call_handler" "plperl_inline_handler" "plperl_validator" "$libdir/plperl" _null_ ));
! DATA(insert ( "plperlu" f f "plperlu_call_handler" "plperlu_inline_handler" "plperlu_validator" "$libdir/plperl" _null_ ));
! DATA(insert ( "plpythonu" f f "plpython_call_handler" "plpython_inline_handler" "plpython_validator" "$libdir/plpython2" _null_ ));
! DATA(insert ( "plpython2u" f f "plpython2_call_handler" "plpython2_inline_handler" "plpython2_validator" "$libdir/plpython2" _null_ ));
! DATA(insert ( "plpython3u" f f "plpython3_call_handler" "plpython3_inline_handler" "plpython3_validator" "$libdir/plpython3" _null_ ));
#endif /* PG_PLTEMPLATE_H */
--- 71,83 ----
* ----------------
*/
! DATA(insert ( "plpgsql" t t "plpgsql_call_handler" "plpgsql_inline_handler" "plpgsql_validator" "plpgsql_checker" "$libdir/plpgsql" _null_ ));
! DATA(insert ( "pltcl" t t "pltcl_call_handler" _null_ _null_ _null_ "$libdir/pltcl" _null_ ));
! DATA(insert ( "pltclu" f f "pltclu_call_handler" _null_ _null_ _null_ "$libdir/pltcl" _null_ ));
! DATA(insert ( "plperl" t t "plperl_call_handler" "plperl_inline_handler" "plperl_validator" _null_ "$libdir/plperl" _null_ ));
! DATA(insert ( "plperlu" f f "plperlu_call_handler" "plperlu_inline_handler" "plperlu_validator" _null_ "$libdir/plperl" _null_ ));
! DATA(insert ( "plpythonu" f f "plpython_call_handler" "plpython_inline_handler" "plpython_validator" _null_ "$libdir/plpython2" _null_ ));
! DATA(insert ( "plpython2u" f f "plpython2_call_handler" "plpython2_inline_handler" "plpython2_validator" _null_ "$libdir/plpython2" _null_ ));
! DATA(insert ( "plpython3u" f f "plpython3_call_handler" "plpython3_inline_handler" "plpython3_validator" _null_ "$libdir/plpython3" _null_ ));
#endif /* PG_PLTEMPLATE_H */
*** a/src/include/commands/defrem.h
--- b/src/include/commands/defrem.h
***************
*** 15,20 ****
--- 15,21 ----
#define DEFREM_H
#include "nodes/parsenodes.h"
+ #include "tcop/dest.h"
/* commands/dropcmds.c */
extern void RemoveObjects(DropStmt *stmt);
***************
*** 62,67 **** extern Oid GetDefaultOpClass(Oid type_id, Oid am_id);
--- 63,70 ----
/* commands/functioncmds.c */
extern void CreateFunction(CreateFunctionStmt *stmt, const char *queryString);
extern void RemoveFunctionById(Oid funcOid);
+ extern void CheckFunction(CheckFunctionStmt *stmt, DestReceiver *dest);
+ extern TupleDesc CheckFunctionResultDesc(CheckFunctionStmt *stmt);
extern void SetFunctionReturnType(Oid funcOid, Oid newRetType);
extern void SetFunctionArgType(Oid funcOid, int argIndex, Oid newArgType);
extern void RenameFunction(List *name, List *argtypes, const char *newname);
*** a/src/include/nodes/nodes.h
--- b/src/include/nodes/nodes.h
***************
*** 290,295 **** typedef enum NodeTag
--- 290,296 ----
T_IndexStmt,
T_CreateFunctionStmt,
T_AlterFunctionStmt,
+ T_CheckFunctionStmt,
T_DoStmt,
T_RenameStmt,
T_RuleStmt,
*** a/src/include/nodes/parsenodes.h
--- b/src/include/nodes/parsenodes.h
***************
*** 1740,1745 **** typedef struct CreatePLangStmt
--- 1740,1746 ----
List *plhandler; /* PL call handler function (qual. name) */
List *plinline; /* optional inline function (qual. name) */
List *plvalidator; /* optional validator function (qual. name) */
+ List *plchecker; /* optional checker function (qual. name) */
bool pltrusted; /* PL is trusted */
} CreatePLangStmt;
***************
*** 2084,2089 **** typedef struct AlterFunctionStmt
--- 2085,2106 ----
} AlterFunctionStmt;
/* ----------------------
+ * Check {Function|Trigger} Statement
+ * ----------------------
+ */
+ typedef struct CheckFunctionStmt
+ {
+ NodeTag type;
+ List *funcname; /* qualified name of checked object */
+ List *args; /* types of the arguments */
+ char *trgname; /* trigger's name */
+ RangeVar *relation; /* trigger's relation */
+ bool is_function; /* true for CHECK FUNCTION statement */
+ List *options; /* other DefElem options */
+ List *check_options; /* options for check function */
+ } CheckFunctionStmt;
+
+ /* ----------------------
* DO Statement
*
* DoStmt is the raw parser output, InlineCodeBlock is the execution-time API
*** a/src/pl/plpgsql/src/pl_comp.c
--- b/src/pl/plpgsql/src/pl_comp.c
***************
*** 115,121 **** static PLpgSQL_function *plpgsql_HashTableLookup(PLpgSQL_func_hashkey *func_key)
static void plpgsql_HashTableInsert(PLpgSQL_function *function,
PLpgSQL_func_hashkey *func_key);
static void plpgsql_HashTableDelete(PLpgSQL_function *function);
- static void delete_function(PLpgSQL_function *func);
/* ----------
* plpgsql_compile Make an execution tree for a PL/pgSQL function.
--- 115,120 ----
***************
*** 175,181 **** recheck:
* Nope, so remove it from hashtable and try to drop associated
* storage (if not done already).
*/
! delete_function(function);
/*
* If the function isn't in active use then we can overwrite the
--- 174,180 ----
* Nope, so remove it from hashtable and try to drop associated
* storage (if not done already).
*/
! plpgsql_delete_function(function);
/*
* If the function isn't in active use then we can overwrite the
***************
*** 2426,2432 **** plpgsql_resolve_polymorphic_argtypes(int numargs,
}
/*
! * delete_function - clean up as much as possible of a stale function cache
*
* We can't release the PLpgSQL_function struct itself, because of the
* possibility that there are fn_extra pointers to it. We can release
--- 2425,2431 ----
}
/*
! * plpgsql_delete_function - clean up as much as possible of a stale function cache
*
* We can't release the PLpgSQL_function struct itself, because of the
* possibility that there are fn_extra pointers to it. We can release
***************
*** 2439,2446 **** plpgsql_resolve_polymorphic_argtypes(int numargs,
* pointers to the same function cache. Hence be careful not to do things
* twice.
*/
! static void
! delete_function(PLpgSQL_function *func)
{
/* remove function from hash table (might be done already) */
plpgsql_HashTableDelete(func);
--- 2438,2445 ----
* pointers to the same function cache. Hence be careful not to do things
* twice.
*/
! void
! plpgsql_delete_function(PLpgSQL_function *func)
{
/* remove function from hash table (might be done already) */
plpgsql_HashTableDelete(func);
*** a/src/pl/plpgsql/src/pl_exec.c
--- b/src/pl/plpgsql/src/pl_exec.c
***************
*** 210,216 **** static void free_params_data(PreparedParamsData *ppd);
--- 210,237 ----
static Portal exec_dynquery_with_params(PLpgSQL_execstate *estate,
PLpgSQL_expr *dynquery, List *params,
const char *portalname, int cursorOptions);
+ static bool check_row_or_rec(PLpgSQL_checkstate *cstate, PLpgSQL_row *row, PLpgSQL_rec *rec);
+ static bool check_expr(PLpgSQL_checkstate *cstate, PLpgSQL_expr *expr);
+ static void assign_tupdesc_row_or_rec(PLpgSQL_checkstate *cstate,
+ PLpgSQL_row *row, PLpgSQL_rec *rec,
+ TupleDesc tupdesc);
+ static TupleDesc expr_get_desc(PLpgSQL_checkstate *cstate,
+ PLpgSQL_expr *query,
+ bool use_element_type,
+ bool expand_record,
+ bool is_expression);
+ static void var_init_to_null(PLpgSQL_checkstate *cstate, int varno);
+ static bool check_stmts(PLpgSQL_checkstate *cstate, List *stmts);
+ static bool check_stmt(PLpgSQL_checkstate *cstate, PLpgSQL_stmt *stmt);
+ static bool prepare_expr(PLpgSQL_checkstate *cstate,
+ PLpgSQL_expr *expr, int cursorOptions);
+ /*
+ * check_function raises exception, when this variable is true.
+ * A reason why global variable is used is a change of context
+ * log - see check_error_callback.
+ */
+ bool plpgsql_stop_on_first_error;
/* ----------
* plpgsql_exec_function Called by the call handler for
***************
*** 6176,6178 **** exec_dynquery_with_params(PLpgSQL_execstate *estate,
--- 6197,7744 ----
return portal;
}
+
+ /*
+ * Following code ensures a CHECK FUNCTION and CHECK TRIGGER statements for PL/pgSQL
+ *
+ */
+
+ /*
+ * Store error record to tuplestore
+ */
+ static void
+ report_error(PLpgSQL_checkstate *cstate,
+ PLpgSQL_stmt *stmt,
+ int sqlerrcode,
+ char *message,
+ char *detail,
+ char *hint,
+ char *level,
+ int position,
+ char *query)
+ {
+ Datum values[10];
+ bool nulls[10];
+
+ values[0] = ObjectIdGetDatum(cstate->estate.func->fn_oid);
+ nulls[0] = false;
+
+ if (stmt != NULL)
+ {
+ values[1] = Int32GetDatum(stmt->lineno);
+ values[2] = CStringGetTextDatum(plpgsql_stmt_typename(stmt));
+ nulls[1] = false;
+ nulls[2] = false;
+ }
+ else
+ {
+ nulls[1] = true;
+ nulls[2] = true;
+ }
+
+ values[3] = CStringGetTextDatum(unpack_sql_state(sqlerrcode));
+ nulls[3] = false;
+
+ if (message != NULL)
+ {
+ values[4] = CStringGetTextDatum(message);
+ nulls[4] = false;
+ }
+ else
+ nulls[4] = true;
+
+ if (detail != NULL)
+ {
+ values[5] = CStringGetTextDatum(detail);
+ nulls[5] = false;
+ }
+ else
+ nulls[5] = true;
+
+ if (hint != NULL)
+ {
+ values[6] = CStringGetTextDatum(hint);
+ nulls[6] = false;
+ }
+ else
+ nulls[6] = true;
+
+ if (level != NULL)
+ {
+ values[7] = CStringGetTextDatum(level);
+ nulls[7] = false;
+ }
+ else
+ nulls[7] = true;
+
+ if (query != NULL)
+ {
+ values[8] = Int32GetDatum(position);
+ values[9] = CStringGetTextDatum(query);
+ nulls[8] = false;
+ nulls[9] = false;
+ }
+ else
+ {
+ nulls[8] = true;
+ nulls[9] = true;
+ }
+
+ tuplestore_putvalues(cstate->tupstore, cstate->tupdesc, values, nulls);
+ }
+
+ static void
+ report_error_edata(PLpgSQL_checkstate *cstate,
+ ErrorData *edata)
+ {
+ report_error(cstate, cstate->estate.err_stmt,
+ edata->sqlerrcode,
+ edata->message,
+ edata->detail,
+ edata->hint,
+ "error",
+ edata->internalpos,
+ edata->internalquery);
+ }
+
+ /*
+ * Check function - it prepare variables and starts a prepare plan walker
+ * called by function checker
+ */
+ void
+ plpgsql_check_function(PLpgSQL_function *func, FunctionCallInfo fcinfo,
+ TupleDesc tupdesc,
+ Tuplestorestate *tupstore,
+ bool fatal_errors)
+ {
+ PLpgSQL_checkstate cstate;
+ int i;
+
+ /*
+ * Setup the execution state - we would to reuse some exec routines
+ * so we need a estate
+ */
+ plpgsql_estate_setup(&cstate.estate, func, (ReturnSetInfo *) fcinfo->resultinfo);
+ cstate.tupdesc = tupdesc;
+ cstate.tupstore = tupstore;
+ cstate.fatal_errors = fatal_errors;
+
+ /*
+ * Make local execution copies of all the datums
+ */
+ for (i = 0; i < cstate.estate.ndatums; i++)
+ cstate.estate.datums[i] = copy_plpgsql_datum(func->datums[i]);
+
+ /*
+ * Store the actual call argument values into the appropriate variables
+ */
+ for (i = 0; i < func->fn_nargs; i++)
+ {
+ int n = func->fn_argvarnos[i];
+
+ switch (cstate.estate.datums[n]->dtype)
+ {
+ case PLPGSQL_DTYPE_VAR:
+ {
+ var_init_to_null(&cstate, n);
+ }
+ break;
+
+ case PLPGSQL_DTYPE_ROW:
+ {
+ PLpgSQL_row *row = (PLpgSQL_row *) cstate.estate.datums[n];
+
+ exec_move_row(&cstate.estate, NULL, row, NULL, NULL);
+ }
+ break;
+
+ default:
+ elog(ERROR, "unrecognized dtype: %d", func->datums[i]->dtype);
+ }
+ }
+
+ /*
+ * Now check the toplevel block of statements
+ */
+ check_stmt(&cstate, (PLpgSQL_stmt *) func->action);
+
+ /* Cleanup temporary memory */
+ plpgsql_destroy_econtext(&cstate.estate);
+ }
+
+ /*
+ * Check trigger - prepare fake environments for testing trigger
+ *
+ */
+ void
+ plpgsql_check_trigger(PLpgSQL_function *func,
+ TriggerData *trigdata,
+ TupleDesc tupdesc,
+ Tuplestorestate *tupstore,
+ bool fatal_errors)
+ {
+ PLpgSQL_checkstate cstate;
+ PLpgSQL_rec *rec_new,
+ *rec_old;
+ int i;
+
+ /*
+ * Setup the execution state - we would to reuse some exec routines
+ * so we need a estate
+ */
+ plpgsql_estate_setup(&cstate.estate, func, NULL);
+ cstate.tupdesc = tupdesc;
+ cstate.tupstore = tupstore;
+ cstate.fatal_errors = fatal_errors;
+
+ /*
+ * Make local execution copies of all the datums
+ */
+ for (i = 0; i < cstate.estate.ndatums; i++)
+ cstate.estate.datums[i] = copy_plpgsql_datum(func->datums[i]);
+
+ /*
+ * Put the OLD and NEW tuples into record variables
+ *
+ * We make the tupdescs available in both records even though only one may
+ * have a value. This allows parsing of record references to succeed in
+ * functions that are used for multiple trigger types. For example, we
+ * might have a test like "if (TG_OP = 'INSERT' and NEW.foo = 'xyz')",
+ * which should parse regardless of the current trigger type.
+ */
+ rec_new = (PLpgSQL_rec *) (cstate.estate.datums[func->new_varno]);
+ rec_new->freetup = false;
+ rec_new->freetupdesc = false;
+ assign_tupdesc_row_or_rec(&cstate, NULL, rec_new, trigdata->tg_relation->rd_att);
+
+ rec_old = (PLpgSQL_rec *) (cstate.estate.datums[func->old_varno]);
+ rec_old->freetup = false;
+ rec_old->freetupdesc = false;
+ assign_tupdesc_row_or_rec(&cstate, NULL, rec_old, trigdata->tg_relation->rd_att);
+
+ /*
+ * Assign the special tg_ variables
+ */
+ var_init_to_null(&cstate, func->tg_op_varno);
+ var_init_to_null(&cstate, func->tg_name_varno);
+ var_init_to_null(&cstate, func->tg_when_varno);
+ var_init_to_null(&cstate, func->tg_level_varno);
+ var_init_to_null(&cstate, func->tg_relid_varno);
+ var_init_to_null(&cstate, func->tg_relname_varno);
+ var_init_to_null(&cstate, func->tg_table_name_varno);
+ var_init_to_null(&cstate, func->tg_table_schema_varno);
+ var_init_to_null(&cstate, func->tg_nargs_varno);
+ var_init_to_null(&cstate, func->tg_argv_varno);
+
+ /*
+ * Now check the toplevel block of statements
+ */
+ check_stmt(&cstate, (PLpgSQL_stmt *) func->action);
+
+ /* Cleanup temporary memory */
+ plpgsql_destroy_econtext(&cstate.estate);
+ }
+
+ /*
+ * Verify lvalue
+ * It doesn't repeat a checks that are done.
+ * Checks a subscript expressions, verify a validity of record's fields,
+ * Returns true, when target is valid
+ */
+ static bool
+ check_target(PLpgSQL_checkstate *cstate, int varno)
+ {
+ PLpgSQL_datum *target = cstate->estate.datums[varno];
+ StringInfoData msg;
+
+ switch (target->dtype)
+ {
+ case PLPGSQL_DTYPE_VAR:
+ case PLPGSQL_DTYPE_REC:
+ return true;
+
+ case PLPGSQL_DTYPE_ROW:
+ return check_row_or_rec(cstate, (PLpgSQL_row *) target, NULL);
+
+ case PLPGSQL_DTYPE_RECFIELD:
+ {
+ PLpgSQL_recfield *recfield = (PLpgSQL_recfield *) target;
+ PLpgSQL_rec *rec;
+ int fno;
+
+ rec = (PLpgSQL_rec *) (cstate->estate.datums[recfield->recparentno]);
+
+ /*
+ * Check that there is already a tuple in the record. We need
+ * that because records don't have any predefined field
+ * structure.
+ */
+ if (!HeapTupleIsValid(rec->tup))
+ {
+ initStringInfo(&msg);
+ appendStringInfo(&msg, "record \"%s\" is not assigned to tuple structure",
+ rec->refname);
+
+ report_error(cstate,
+ cstate->estate.err_stmt,
+ ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE,
+ msg.data,
+ NULL, NULL,
+ "error",
+ 0, NULL);
+ pfree(msg.data);
+
+ return false;
+ }
+
+ /*
+ * Get the number of the records field to change and the
+ * number of attributes in the tuple. Note: disallow system
+ * column names because the code below won't cope.
+ */
+ fno = SPI_fnumber(rec->tupdesc, recfield->fieldname);
+ if (fno <= 0)
+ {
+ initStringInfo(&msg);
+ appendStringInfo(&msg, "record \"%s\" has no field \"%s\"",
+ rec->refname, recfield->fieldname);
+ report_error(cstate,
+ cstate->estate.err_stmt,
+ ERRCODE_UNDEFINED_COLUMN,
+ msg.data,
+ NULL, NULL,
+ "error",
+ 0, NULL);
+ pfree(msg.data);
+
+ return false;
+ }
+ }
+ return true;
+
+ case PLPGSQL_DTYPE_ARRAYELEM:
+ {
+ /*
+ * Target is an element of an array
+ */
+ int nsubscripts;
+ Oid arrayelemtypeid;
+ Oid arraytypeid;
+ bool result = true;
+ bool overlimit_detected = false;
+
+ /*
+ * To handle constructs like x[1][2] := something, we have to
+ * be prepared to deal with a chain of arrayelem datums. Chase
+ * back to find the base array datum, and save the subscript
+ * expressions as we go. (We are scanning right to left here,
+ * but want to evaluate the subscripts left-to-right to
+ * minimize surprises.)
+ */
+ nsubscripts = 0;
+ do
+ {
+ PLpgSQL_arrayelem *arrayelem = (PLpgSQL_arrayelem *) target;
+ bool subscript_is_valid;
+
+ if (nsubscripts++ >= MAXDIM)
+ {
+ /* don't repeet message */
+ if (!overlimit_detected)
+ {
+
+ initStringInfo(&msg);
+ appendStringInfo(&msg, "number of array dimensions (%d) exceeds the maximum allowed (%d)",
+ nsubscripts + 1, MAXDIM);
+
+ report_error(cstate,
+ cstate->estate.err_stmt,
+ ERRCODE_PROGRAM_LIMIT_EXCEEDED,
+ msg.data,
+ NULL, NULL,
+ "error",
+ 0, NULL);
+ pfree(msg.data);
+
+ overlimit_detected = true;
+ result = false;
+
+ if (cstate->fatal_errors)
+ {
+ return false;
+ }
+ }
+ }
+
+ /*
+ * validate expression, cannot to use check expression, because we
+ * we have to know if expression is valid or not. Result of check_expr
+ * is based on fatal_errors flag.
+ */
+ subscript_is_valid = false;
+ if (prepare_expr(cstate, arrayelem->subscript, 0))
+ {
+ TupleDesc tupdesc = expr_get_desc(cstate, arrayelem->subscript, false, false, true);
+
+ if (tupdesc != NULL)
+ {
+ ReleaseTupleDesc(tupdesc);
+ subscript_is_valid = true;
+ }
+ }
+
+ if (!subscript_is_valid)
+ {
+ result = false;
+ if (cstate->fatal_errors)
+ {
+ return false;
+ }
+ }
+
+ target = cstate->estate.datums[arrayelem->arrayparentno];
+ } while (target->dtype == PLPGSQL_DTYPE_ARRAYELEM);
+
+ /* If target is domain over array, reduce to base type */
+ arraytypeid = exec_get_datum_type(&(cstate->estate), target);
+ arraytypeid = getBaseType(arraytypeid);
+
+ arrayelemtypeid = get_element_type(arraytypeid);
+
+ if (!OidIsValid(arrayelemtypeid))
+ {
+ report_error(cstate,
+ cstate->estate.err_stmt,
+ ERRCODE_DATATYPE_MISMATCH,
+ "subscripted object is not an array",
+ NULL, NULL,
+ "error",
+ 0, NULL);
+
+ result = false;
+ if (cstate->fatal_errors)
+ return false;
+ }
+
+ return result;
+ }
+ break;
+ }
+
+ return true;
+ }
+
+ /*
+ * Check composed lvalue
+ * There is nothing to check on rec variables
+ */
+ static bool
+ check_row_or_rec(PLpgSQL_checkstate *cstate, PLpgSQL_row *row, PLpgSQL_rec *rec)
+ {
+ int fnum;
+ bool result = true;
+
+ /* there are nothing to check on rec now */
+ if (row != NULL)
+ {
+ for (fnum = 0; fnum < row->nfields; fnum++)
+ {
+ /* skip dropped columns */
+ if (row->varnos[fnum] < 0)
+ continue;
+
+ if (!check_target(cstate, row->varnos[fnum]))
+ {
+ result = false;
+
+ if (cstate->fatal_errors)
+ break;
+ }
+ }
+ }
+
+ return result;
+ }
+
+ /*
+ * Generate a prepared plan - this is simplyfied copy from pl_exec.c
+ * Is not necessary to check simple plan,
+ * returns true, when expression is succesfully prepared.
+ */
+ static bool
+ prepare_expr(PLpgSQL_checkstate *cstate,
+ PLpgSQL_expr *expr, int cursorOptions)
+ {
+ ResourceOwner oldowner;
+ MemoryContext oldCxt = CurrentMemoryContext;
+ SPIPlanPtr plan = NULL;
+ bool reported_bug;
+
+ /* leave when there are not expression */
+ if (expr == NULL)
+ return true;
+
+ /* leave when plan is created */
+ if (expr->plan != NULL)
+ return true;
+
+ /*
+ * The grammar can't conveniently set expr->func while building the parse
+ * tree, so make sure it's set before parser hooks need it.
+ */
+ expr->func = cstate->estate.func;
+
+ oldowner = CurrentResourceOwner;
+ BeginInternalSubTransaction(NULL);
+ MemoryContextSwitchTo(oldCxt);
+
+ PG_TRY();
+ {
+
+ /*
+ * Generate and save the plan
+ */
+ plan = SPI_prepare_params(expr->query,
+ (ParserSetupHook) plpgsql_parser_setup,
+ (void *) expr,
+ cursorOptions);
+
+ RollbackAndReleaseCurrentSubTransaction();
+ MemoryContextSwitchTo(oldCxt);
+ CurrentResourceOwner = oldowner;
+
+ SPI_restore_connection();
+ reported_bug = false;
+ }
+ PG_CATCH();
+ {
+ ErrorData *edata;
+
+ MemoryContextSwitchTo(oldCxt);
+ edata = CopyErrorData();
+ FlushErrorState();
+
+ RollbackAndReleaseCurrentSubTransaction();
+ MemoryContextSwitchTo(oldCxt);
+ CurrentResourceOwner = oldowner;
+
+ report_error_edata(cstate, edata);
+ MemoryContextSwitchTo(oldCxt);
+
+ /* reconnect spi */
+ SPI_restore_connection();
+
+ reported_bug = true;
+ }
+ PG_END_TRY();
+
+ if (plan == NULL && !reported_bug)
+ {
+ /* Some SPI errors deserve specific error messages */
+ switch (SPI_result)
+ {
+ case SPI_ERROR_COPY:
+ report_error(cstate,
+ cstate->estate.err_stmt,
+ ERRCODE_FEATURE_NOT_SUPPORTED,
+ "cannot COPY to/from client in PL/pgSQL", NULL, NULL,
+ "error",
+ 0, expr->query);
+ break;
+
+ case SPI_ERROR_TRANSACTION:
+ report_error(cstate,
+ cstate->estate.err_stmt,
+ ERRCODE_FEATURE_NOT_SUPPORTED,
+ "cannot begin/end transactions in PL/pgSQL",
+ NULL,
+ "Use a BEGIN block with an EXCEPTION clause instead.",
+ "error",
+ 0, expr->query);
+ break;
+
+ default:
+ elog(ERROR, "SPI_prepare_params failed for \"%s\": %s",
+ expr->query, SPI_result_code_string(SPI_result));
+ }
+ }
+
+ if (plan != NULL)
+ {
+ expr->plan = SPI_saveplan(plan);
+ SPI_freeplan(plan);
+ }
+
+ return plan != NULL;
+ }
+
+ /*
+ * Verify a expression
+ */
+ static bool
+ check_expr(PLpgSQL_checkstate *cstate, PLpgSQL_expr *expr)
+ {
+ TupleDesc tupdesc;
+
+ if (expr != NULL)
+ {
+ if (prepare_expr(cstate, expr, 0))
+ {
+ tupdesc = expr_get_desc(cstate, expr, false, false, true);
+ if (tupdesc != NULL)
+ {
+ ReleaseTupleDesc(tupdesc);
+ }
+ else
+ return cstate->fatal_errors;
+ }
+ else
+ return cstate->fatal_errors;
+ }
+
+ return false;
+ }
+
+ /*
+ * We have to assign TupleDesc to all used record variables step by step.
+ * We would to use a exec routines for query preprocessing, so we must
+ * to create a typed NULL value, and this value is assigned to record
+ * variable.
+ */
+ static void
+ assign_tupdesc_row_or_rec(PLpgSQL_checkstate *cstate,
+ PLpgSQL_row *row, PLpgSQL_rec *rec,
+ TupleDesc tupdesc)
+ {
+ bool *nulls;
+ HeapTuple tup;
+
+ if (tupdesc == NULL)
+ {
+ report_error(cstate,
+ cstate->estate.err_stmt, 0,
+ "tuple descriptor is empty", NULL, NULL,
+ "warning",
+ 0, NULL);
+ return;
+ }
+
+ /*
+ * row variable has assigned TupleDesc already, so don't be processed
+ * here
+ */
+ if (rec != NULL)
+ {
+ PLpgSQL_rec *target = (PLpgSQL_rec *)(cstate->estate.datums[rec->dno]);
+
+ if (target->freetup)
+ heap_freetuple(target->tup);
+
+ if (rec->freetupdesc)
+ FreeTupleDesc(target->tupdesc);
+
+ /* initialize rec by NULLs */
+ nulls = (bool *) palloc(tupdesc->natts * sizeof(bool));
+ memset(nulls, true, tupdesc->natts * sizeof(bool));
+
+ target->tupdesc = CreateTupleDescCopy(tupdesc);
+ target->freetupdesc = true;
+
+ tup = heap_form_tuple(tupdesc, NULL, nulls);
+ if (HeapTupleIsValid(tup))
+ {
+ target->tup = tup;
+ target->freetup = true;
+ }
+ else
+ elog(ERROR, "cannot to build valid composite value");
+ }
+ }
+
+ /*
+ * Assign a tuple descriptor to variable specified by dno
+ */
+ static void
+ assign_tupdesc_dno(PLpgSQL_checkstate *cstate, int varno, TupleDesc tupdesc)
+ {
+ PLpgSQL_datum *target = cstate->estate.datums[varno];
+
+ if (target->dtype == PLPGSQL_DTYPE_REC)
+ assign_tupdesc_row_or_rec(cstate, NULL, (PLpgSQL_rec *) target, tupdesc);
+ }
+
+ /*
+ * Returns a tuple descriptor based on existing plan,
+ * When error is detected returns null.
+ */
+ static TupleDesc
+ expr_get_desc(PLpgSQL_checkstate *cstate,
+ PLpgSQL_expr *query,
+ bool use_element_type,
+ bool expand_record,
+ bool is_expression)
+ {
+ TupleDesc tupdesc = NULL;
+ CachedPlanSource *plansource = NULL;
+ StringInfoData msg;
+
+ if (query->plan != NULL)
+ {
+ SPIPlanPtr plan = query->plan;
+
+ if (plan == NULL || plan->magic != _SPI_PLAN_MAGIC)
+ elog(ERROR, "cached plan is not valid plan");
+
+ if (list_length(plan->plancache_list) != 1)
+ elog(ERROR, "plan is not single execution plan");
+
+ plansource = (CachedPlanSource *) linitial(plan->plancache_list);
+
+ tupdesc = CreateTupleDescCopy(plansource->resultDesc);
+ }
+ else
+ elog(ERROR, "there are no plan for query: \"%s\"",
+ query->query);
+
+ /*
+ * try to get a element type, when result is a array (used with FOREACH ARRAY stmt)
+ */
+ if (use_element_type)
+ {
+ Oid elemtype;
+ TupleDesc elemtupdesc;
+
+ /* result should be a array */
+ if (tupdesc->natts != 1)
+ {
+ initStringInfo(&msg);
+ appendStringInfo(&msg, "query \"%s\" returned %d columns",
+ query->query,
+ tupdesc->natts);
+ report_error(cstate,
+ cstate->estate.err_stmt,
+ ERRCODE_SYNTAX_ERROR,
+ msg.data, NULL, NULL,
+ "error",
+ 0, query->query);
+
+ pfree(msg.data);
+ FreeTupleDesc(tupdesc);
+
+ return NULL;
+ }
+
+ /* check the type of the expression - must be an array */
+ elemtype = get_element_type(tupdesc->attrs[0]->atttypid);
+ if (!OidIsValid(elemtype))
+ {
+ initStringInfo(&msg);
+ appendStringInfo(&msg, "FOREACH expression must yield an array, not type %s",
+ format_type_be(tupdesc->attrs[0]->atttypid));
+ report_error(cstate,
+ cstate->estate.err_stmt,
+ ERRCODE_DATATYPE_MISMATCH,
+ msg.data, NULL, NULL,
+ "error",
+ 0, query->query);
+
+ pfree(msg.data);
+ FreeTupleDesc(tupdesc);
+
+ return NULL;
+ }
+
+ /* we can't know typmod now */
+ elemtupdesc = lookup_rowtype_tupdesc_noerror(elemtype, -1, true);
+ if (elemtupdesc != NULL)
+ {
+ FreeTupleDesc(tupdesc);
+ tupdesc = CreateTupleDescCopy(elemtupdesc);
+ ReleaseTupleDesc(elemtupdesc);
+ }
+ else
+ report_error(cstate,
+ cstate->estate.err_stmt,
+ 0,
+ "cannot to identify real type for record type variable", NULL, NULL,
+ "warning",
+ 0, query->query);
+
+ FreeTupleDesc(tupdesc);
+
+ return NULL;
+ }
+
+ if (is_expression && tupdesc->natts != 1)
+ {
+ initStringInfo(&msg);
+ appendStringInfo(&msg, "query \"%s\" returned %d columns",
+ query->query,
+ tupdesc->natts);
+ report_error(cstate,
+ cstate->estate.err_stmt,
+ ERRCODE_SYNTAX_ERROR,
+ msg.data, NULL, NULL,
+ "error",
+ 0, query->query);
+
+ pfree(msg.data);
+ FreeTupleDesc(tupdesc);
+
+ return NULL;
+ }
+
+ /*
+ * One spacial case is when record is assigned to composite type, then
+ * we should to unpack composite type.
+ */
+ if (tupdesc->tdtypeid == RECORDOID &&
+ tupdesc->tdtypmod == -1 &&
+ tupdesc->natts == 1 && expand_record)
+ {
+ TupleDesc unpack_tupdesc;
+
+ unpack_tupdesc = lookup_rowtype_tupdesc_noerror(tupdesc->attrs[0]->atttypid,
+ tupdesc->attrs[0]->atttypmod,
+ true);
+ if (unpack_tupdesc != NULL)
+ {
+ FreeTupleDesc(tupdesc);
+ tupdesc = CreateTupleDescCopy(unpack_tupdesc);
+ ReleaseTupleDesc(unpack_tupdesc);
+ }
+ }
+
+ /*
+ * There is special case, when returned tupdesc contains only
+ * unpined record: rec := func_with_out_parameters(). IN this case
+ * we must to dig more deep - we have to find oid of function and
+ * get their parameters,
+ *
+ * This is support for assign statement
+ * recvar := func_with_out_parameters(..)
+ */
+ if (tupdesc->tdtypeid == RECORDOID &&
+ tupdesc->tdtypmod == -1 &&
+ tupdesc->natts == 1 &&
+ tupdesc->attrs[0]->atttypid == RECORDOID &&
+ tupdesc->attrs[0]->atttypmod == -1 &&
+ expand_record)
+ {
+ PlannedStmt *_stmt;
+ Plan *_plan;
+ TargetEntry *tle;
+ CachedPlan *cplan;
+
+ /*
+ * When tupdesc is related to unpined record, we will try
+ * to check plan if it is just function call and if it is
+ * then we can try to derive a tupledes from function's
+ * description.
+ */
+ cplan = GetCachedPlan(plansource, NULL, true);
+ _stmt = (PlannedStmt *) linitial(cplan->stmt_list);
+
+ if (IsA(_stmt, PlannedStmt) && _stmt->commandType == CMD_SELECT)
+ {
+ _plan = _stmt->planTree;
+ if (IsA(_plan, Result) && list_length(_plan->targetlist) == 1)
+ {
+ tle = (TargetEntry *) linitial(_plan->targetlist);
+ if (((Node *) tle->expr)->type == T_FuncExpr)
+ {
+ FuncExpr *fn = (FuncExpr *) tle->expr;
+ FmgrInfo flinfo;
+ FunctionCallInfoData fcinfo;
+ TupleDesc rd;
+ Oid rt;
+
+ fmgr_info(fn->funcid, &flinfo);
+ flinfo.fn_expr = (Node *) fn;
+ fcinfo.flinfo = &flinfo;
+
+ get_call_result_type(&fcinfo, &rt, &rd);
+ if (rd == NULL)
+ {
+ report_error(cstate,
+ cstate->estate.err_stmt,
+ ERRCODE_DATATYPE_MISMATCH,
+ "function does not return composite type, is not possible to identify composite type",
+ NULL, NULL,
+ "error",
+ 0, query->query);
+
+ FreeTupleDesc(tupdesc);
+ ReleaseCachedPlan(cplan, true);
+ return NULL;
+ }
+
+ FreeTupleDesc(tupdesc);
+ BlessTupleDesc(rd);
+
+ tupdesc = rd;
+ }
+ }
+ }
+
+ ReleaseCachedPlan(cplan, true);
+ }
+
+ return tupdesc;
+ }
+
+ /*
+ * Ensure check for all statements in list
+ */
+ bool
+ check_stmts(PLpgSQL_checkstate *cstate, List *stmts)
+ {
+ ListCell *lc;
+
+ foreach(lc, stmts)
+ {
+ if (check_stmt(cstate, (PLpgSQL_stmt *) lfirst(lc)) && cstate->fatal_errors)
+ return true;
+ }
+
+ return false;
+ }
+
+ /*
+ * walk over all statements
+ */
+ bool
+ check_stmt(PLpgSQL_checkstate *cstate, PLpgSQL_stmt *stmt)
+ {
+ TupleDesc tupdesc = NULL;
+ PLpgSQL_function *func;
+ ListCell *l;
+
+ if (stmt == NULL)
+ return false;
+
+ cstate->estate.err_stmt = stmt;
+ func = cstate->estate.func;
+
+ switch ((enum PLpgSQL_stmt_types) stmt->cmd_type)
+ {
+ case PLPGSQL_STMT_BLOCK:
+ {
+ PLpgSQL_stmt_block *stmt_block = (PLpgSQL_stmt_block *) stmt;
+ int i;
+ PLpgSQL_datum *d;
+
+ for (i = 0; i < stmt_block->n_initvars; i++)
+ {
+ d = func->datums[stmt_block->initvarnos[i]];
+
+ if (d->dtype == PLPGSQL_DTYPE_VAR)
+ {
+ PLpgSQL_var *var = (PLpgSQL_var *) d;
+
+ if (check_expr(cstate, var->default_val))
+ return true;
+ }
+ }
+
+ if (check_stmts(cstate, stmt_block->body))
+ return true;;
+
+ if (stmt_block->exceptions)
+ {
+ foreach(l, stmt_block->exceptions->exc_list)
+ {
+ if (check_stmts(cstate, ((PLpgSQL_exception *) lfirst(l))->action))
+ return true;
+ }
+ }
+ }
+ break;
+
+ case PLPGSQL_STMT_ASSIGN:
+ {
+ PLpgSQL_stmt_assign *stmt_assign = (PLpgSQL_stmt_assign *) stmt;
+ bool is_valid = false;
+ bool target_is_valid;
+
+ target_is_valid = check_target(cstate, stmt_assign->varno);
+ if (!target_is_valid && cstate->fatal_errors)
+ return true;
+
+ /* prepare plan if desn't exist yet */
+ if (prepare_expr(cstate, stmt_assign->expr, 0))
+ {
+ tupdesc = expr_get_desc(cstate,
+ stmt_assign->expr,
+ false, /* no element type */
+ true, /* expand record */
+ true); /* is expression */
+
+ if (tupdesc != NULL)
+ {
+ /* assign a tupdesc to record variable */
+ if (target_is_valid)
+ assign_tupdesc_dno(cstate, stmt_assign->varno, tupdesc);
+ is_valid = true;
+ ReleaseTupleDesc(tupdesc);
+ }
+ }
+
+ if (!is_valid && cstate->fatal_errors)
+ return true;
+ }
+ break;
+
+ case PLPGSQL_STMT_IF:
+ {
+ PLpgSQL_stmt_if *stmt_if = (PLpgSQL_stmt_if *) stmt;
+ ListCell *l;
+
+ if (check_expr(cstate, stmt_if->cond))
+ return true;
+
+ if (check_stmts(cstate, stmt_if->then_body))
+ return true;
+
+ foreach(l, stmt_if->elsif_list)
+ {
+ PLpgSQL_if_elsif *elif = (PLpgSQL_if_elsif *) lfirst(l);
+
+ if (check_expr(cstate, elif->cond))
+ return true;
+
+ if (check_stmts(cstate, elif->stmts))
+ return true;
+ }
+
+ if (check_stmts(cstate, stmt_if->else_body))
+ return true;
+ }
+ break;
+
+ case PLPGSQL_STMT_CASE:
+ {
+ PLpgSQL_stmt_case *stmt_case = (PLpgSQL_stmt_case *) stmt;
+ Oid result_oid;
+
+ if (stmt_case->t_expr != NULL)
+ {
+ PLpgSQL_var *t_var = (PLpgSQL_var *) cstate->estate.datums[stmt_case->t_varno];
+ bool is_valid = false;
+
+ /* we need to set hidden variable type */
+ if (prepare_expr(cstate, stmt_case->t_expr, 0))
+ {
+ tupdesc = expr_get_desc(cstate,
+ stmt_case->t_expr,
+ false, /* no element type */
+ false, /* expand record */
+ true); /* is expression */
+ if (tupdesc != NULL)
+ {
+ result_oid = tupdesc->attrs[0]->atttypid;
+
+ /*
+ * When expected datatype is different from real, change it. Note that
+ * what we're modifying here is an execution copy of the datum, so
+ * this doesn't affect the originally stored function parse tree.
+ */
+
+ if (t_var->datatype->typoid != result_oid)
+ t_var->datatype = plpgsql_build_datatype(result_oid,
+ -1,
+ cstate->estate.func->fn_input_collation);
+
+ ReleaseTupleDesc(tupdesc);
+ is_valid = true;
+ }
+ }
+
+ if (!is_valid && cstate->fatal_errors)
+ return true;
+ }
+
+ foreach(l, stmt_case->case_when_list)
+ {
+ PLpgSQL_case_when *cwt = (PLpgSQL_case_when *) lfirst(l);
+
+ if (check_expr(cstate, cwt->expr))
+ return true;
+
+ if (check_stmts(cstate, cwt->stmts))
+ return true;
+ }
+
+ if (check_stmts(cstate, stmt_case->else_stmts))
+ return true;
+ }
+ break;
+
+ case PLPGSQL_STMT_LOOP:
+ if (check_stmts(cstate, ((PLpgSQL_stmt_loop *) stmt)->body))
+ return true;
+ break;
+
+ case PLPGSQL_STMT_WHILE:
+ {
+ PLpgSQL_stmt_while *stmt_while = (PLpgSQL_stmt_while *) stmt;
+
+ if (check_expr(cstate, stmt_while->cond))
+ return true;
+
+ if (check_stmts(cstate, stmt_while->body))
+ return true;
+ }
+ break;
+
+ case PLPGSQL_STMT_FORI:
+ {
+ PLpgSQL_stmt_fori *stmt_fori = (PLpgSQL_stmt_fori *) stmt;
+
+ if (check_expr(cstate, stmt_fori->lower))
+ return true;
+
+ if (check_expr(cstate, stmt_fori->upper))
+ return true;
+
+ if (check_expr(cstate, stmt_fori->step))
+ return true;
+
+ if (check_stmts(cstate, stmt_fori->body))
+ return true;
+ }
+ break;
+
+ case PLPGSQL_STMT_FORS:
+ {
+ PLpgSQL_stmt_fors *stmt_fors = (PLpgSQL_stmt_fors *) stmt;
+ bool is_valid = false;
+ bool target_is_valid;
+
+ target_is_valid = check_row_or_rec(cstate, stmt_fors->row, stmt_fors->rec);
+ if (!target_is_valid && cstate->fatal_errors)
+ return true;
+
+ /* we need to set hidden variable type */
+ if (prepare_expr(cstate, stmt_fors->query, 0))
+ {
+ tupdesc = expr_get_desc(cstate,
+ stmt_fors->query,
+ false, /* no element type */
+ false, /* expand record */
+ false); /* is expression */
+ if (tupdesc != NULL)
+ {
+ if (target_is_valid)
+ assign_tupdesc_row_or_rec(cstate, stmt_fors->row, stmt_fors->rec, tupdesc);
+
+ is_valid = true;
+ ReleaseTupleDesc(tupdesc);
+ }
+ }
+
+ if (!is_valid && cstate->fatal_errors)
+ return true;
+
+ if (check_stmts(cstate, stmt_fors->body))
+ return true;
+ }
+ break;
+
+ case PLPGSQL_STMT_FORC:
+ {
+ PLpgSQL_stmt_forc *stmt_forc = (PLpgSQL_stmt_forc *) stmt;
+ PLpgSQL_var *var = (PLpgSQL_var *) func->datums[stmt_forc->curvar];
+ bool is_valid = false;
+ bool target_is_valid;
+
+ target_is_valid = check_row_or_rec(cstate, stmt_forc->row, stmt_forc->rec);
+ if (!target_is_valid && cstate->fatal_errors)
+ return true;
+
+ if (!prepare_expr(cstate, stmt_forc->argquery, 0) && cstate->fatal_errors)
+ return true;
+
+ if (var->cursor_explicit_expr != NULL)
+ {
+ if (prepare_expr(cstate, var->cursor_explicit_expr,
+ var->cursor_options))
+ {
+ tupdesc = expr_get_desc(cstate,
+ var->cursor_explicit_expr,
+ false, /* no element type */
+ false, /* expand record */
+ false); /* is expression */
+
+ if (target_is_valid)
+ assign_tupdesc_row_or_rec(cstate, stmt_forc->row, stmt_forc->rec, tupdesc);
+
+ is_valid = true;
+ ReleaseTupleDesc(tupdesc);
+ }
+ if (!is_valid && cstate->fatal_errors)
+ return true;
+ }
+
+ if (check_stmts(cstate, stmt_forc->body))
+ return true;
+ }
+ break;
+
+ case PLPGSQL_STMT_DYNFORS:
+ {
+ PLpgSQL_stmt_dynfors * stmt_dynfors = (PLpgSQL_stmt_dynfors *) stmt;
+
+ if (stmt_dynfors->rec != NULL)
+ {
+ report_error(cstate,
+ cstate->estate.err_stmt,
+ 0,
+ "cannot determinate a result of dynamic SQL",
+ "Cannot to contine in check.",
+ "Don't use dynamic SQL and record type together, when you would check function.",
+ "warning",
+ 0, NULL);
+
+ /*
+ * don't continue in checking. Behave should be indeterministic.
+ */
+ return true;
+ }
+
+ if (check_expr(cstate, stmt_dynfors->query))
+ return true;
+
+ foreach(l, stmt_dynfors->params)
+ {
+ if (check_expr(cstate, (PLpgSQL_expr *) lfirst(l)))
+ return true;
+ }
+
+ if (check_stmts(cstate, stmt_dynfors->body))
+ return true;
+ }
+ break;
+
+ case PLPGSQL_STMT_FOREACH_A:
+ {
+ PLpgSQL_stmt_foreach_a *stmt_foreach_a = (PLpgSQL_stmt_foreach_a *) stmt;
+ bool is_valid = false;
+ bool target_is_valid;
+
+ target_is_valid = check_target(cstate, stmt_foreach_a->varno);
+ if (!target_is_valid && cstate->fatal_errors)
+ return true;
+
+ if (prepare_expr(cstate, stmt_foreach_a->expr, 0))
+ {
+ tupdesc = expr_get_desc(cstate,
+ stmt_foreach_a->expr,
+ true, /* no element type */
+ false, /* expand record */
+ true); /* is expression */
+ if (tupdesc != NULL)
+ {
+ if (target_is_valid)
+ assign_tupdesc_dno(cstate, stmt_foreach_a->varno, tupdesc);
+ is_valid = true;
+ ReleaseTupleDesc(tupdesc);
+ }
+ }
+ if (!is_valid && cstate->fatal_errors)
+ return true;
+
+ if (check_stmts(cstate, stmt_foreach_a->body))
+ return true;
+ }
+ break;
+
+ case PLPGSQL_STMT_EXIT:
+ if (check_expr(cstate, ((PLpgSQL_stmt_exit *) stmt)->cond))
+ return true;
+ break;
+
+ case PLPGSQL_STMT_PERFORM:
+ if (prepare_expr(cstate, ((PLpgSQL_stmt_perform *) stmt)->expr, 0))
+ return true;
+ break;
+
+ case PLPGSQL_STMT_RETURN:
+ if (check_expr(cstate, ((PLpgSQL_stmt_return *) stmt)->expr))
+ return true;
+ break;
+
+ case PLPGSQL_STMT_RETURN_NEXT:
+ if (check_expr(cstate, ((PLpgSQL_stmt_return_next *) stmt)->expr))
+ return true;
+ break;
+
+ case PLPGSQL_STMT_RETURN_QUERY:
+ {
+ PLpgSQL_stmt_return_query *stmt_rq = (PLpgSQL_stmt_return_query *) stmt;
+
+ if (check_expr(cstate, stmt_rq->dynquery))
+ return true;
+
+ if (prepare_expr(cstate, stmt_rq->query, 0) && cstate->fatal_errors)
+ return true;
+
+ foreach(l, stmt_rq->params)
+ {
+ if (check_expr(cstate, (PLpgSQL_expr *) lfirst(l)))
+ return true;
+ }
+ }
+ break;
+
+ case PLPGSQL_STMT_RAISE:
+ {
+ PLpgSQL_stmt_raise *stmt_raise = (PLpgSQL_stmt_raise *) stmt;
+ ListCell *current_param;
+ char *cp;
+
+ foreach(l, stmt_raise->params)
+ {
+ if (check_expr(cstate, (PLpgSQL_expr *) lfirst(l)))
+ return true;
+ }
+
+ foreach(l, stmt_raise->options)
+ {
+ PLpgSQL_raise_option *opt = (PLpgSQL_raise_option *) lfirst(l);
+
+ if (check_expr(cstate, opt->expr))
+ return true;
+ }
+
+ current_param = list_head(stmt_raise->params);
+
+ /* ensure any single % has a own parameter */
+ if (stmt_raise->message != NULL)
+ {
+ for (cp = stmt_raise->message; *cp; cp++)
+ {
+ if (cp[0] == '%')
+ {
+ if (cp[1] == '%')
+ {
+ cp++;
+ continue;
+ }
+
+ if (current_param == NULL)
+ {
+ report_error(cstate,
+ cstate->estate.err_stmt,
+ ERRCODE_SYNTAX_ERROR,
+ "too few parameters specified for RAISE",
+ NULL, NULL,
+ "error",
+ 0, NULL);
+ if (cstate->fatal_errors)
+ return true;
+ break;
+ }
+
+ current_param = lnext(current_param);
+ }
+ }
+ }
+
+ if (current_param != NULL)
+ {
+ report_error(cstate,
+ cstate->estate.err_stmt,
+ ERRCODE_SYNTAX_ERROR,
+ "too many parameters specified for RAISE",
+ NULL, NULL,
+ "error",
+ 0, NULL);
+ if (cstate->fatal_errors)
+ return true;
+ }
+ }
+ break;
+
+ case PLPGSQL_STMT_EXECSQL:
+ {
+ PLpgSQL_stmt_execsql *stmt_execsql = (PLpgSQL_stmt_execsql *) stmt;
+
+ if (stmt_execsql->into)
+ {
+ bool is_valid = false;
+ bool target_is_valid;
+
+ target_is_valid = check_row_or_rec(cstate, stmt_execsql->row, stmt_execsql->rec);
+ if (!target_is_valid && cstate->fatal_errors)
+ return true;
+
+ if (prepare_expr(cstate, stmt_execsql->sqlstmt, 0))
+ {
+ tupdesc = expr_get_desc(cstate,
+ stmt_execsql->sqlstmt,
+ false, /* no element type */
+ false, /* expand record */
+ false); /* is expression */
+ if (tupdesc != NULL)
+ {
+ if (target_is_valid)
+ assign_tupdesc_row_or_rec(cstate, stmt_execsql->row, stmt_execsql->rec, tupdesc);
+ ReleaseTupleDesc(tupdesc);
+ is_valid = true;
+ }
+ }
+
+ if (!is_valid && cstate->fatal_errors)
+ return true;
+ }
+ else
+ {
+ /* only statement */
+ if (!prepare_expr(cstate, stmt_execsql->sqlstmt, 0) && cstate->fatal_errors)
+ return true;
+ }
+ }
+ break;
+
+ case PLPGSQL_STMT_DYNEXECUTE:
+ {
+ PLpgSQL_stmt_dynexecute *stmt_dynexecute = (PLpgSQL_stmt_dynexecute *) stmt;
+
+ if (check_expr(cstate, stmt_dynexecute->query))
+ return true;
+
+ foreach(l, stmt_dynexecute->params)
+ {
+ if (check_expr(cstate, (PLpgSQL_expr *) lfirst(l)))
+ return true;
+ }
+
+ if (stmt_dynexecute->into)
+ {
+ if (!check_row_or_rec(cstate, stmt_dynexecute->row, stmt_dynexecute->rec)
+ && cstate->fatal_errors)
+ return true;
+
+ if (stmt_dynexecute->rec != NULL)
+ {
+ report_error(cstate,
+ cstate->estate.err_stmt,
+ 0,
+ "cannot determinate a result of dynamic SQL",
+ "Cannot to contine in check.",
+ "Don't use dynamic SQL and record type together, when you would check function.",
+ "warning",
+ 0, NULL);
+
+ /*
+ * don't continue in checking. Behave should be indeterministic.
+ */
+ return true;
+ }
+ }
+ }
+ break;
+
+ case PLPGSQL_STMT_OPEN:
+ {
+ PLpgSQL_stmt_open *stmt_open = (PLpgSQL_stmt_open *) stmt;
+ PLpgSQL_var *var = (PLpgSQL_var *) func->datums[stmt_open->curvar];
+
+ if (var->cursor_explicit_expr)
+ {
+ if (!prepare_expr(cstate, var->cursor_explicit_expr,
+ var->cursor_options) && cstate->fatal_errors)
+ return true;
+ }
+
+ if (!prepare_expr(cstate, stmt_open->query, 0) && cstate->fatal_errors)
+ return true;
+
+ if (!prepare_expr(cstate, stmt_open->argquery, 0) && cstate->fatal_errors)
+ return true;
+
+ if (check_expr(cstate, stmt_open->dynquery))
+ return true;
+
+ foreach(l, stmt_open->params)
+ {
+ if (check_expr(cstate, (PLpgSQL_expr *) lfirst(l)))
+ return true;
+ }
+ }
+ break;
+
+ case PLPGSQL_STMT_GETDIAG:
+ {
+ PLpgSQL_stmt_getdiag *stmt_getdiag = (PLpgSQL_stmt_getdiag *) stmt;
+ ListCell *lc;
+
+ foreach(lc, stmt_getdiag->diag_items)
+ {
+ PLpgSQL_diag_item *diag_item = (PLpgSQL_diag_item *) lfirst(lc);
+
+ if (!check_target(cstate, diag_item->target) && cstate->fatal_errors)
+ return true;
+ }
+ }
+ break;
+
+ case PLPGSQL_STMT_FETCH:
+ {
+ PLpgSQL_stmt_fetch *stmt_fetch = (PLpgSQL_stmt_fetch *) stmt;
+ PLpgSQL_var *var = (PLpgSQL_var *)(cstate->estate.datums[stmt_fetch->curvar]);
+ bool is_valid = false;
+ bool target_is_valid;
+
+ target_is_valid = check_row_or_rec(cstate, stmt_fetch->row, stmt_fetch->rec);
+ if (!target_is_valid && cstate->fatal_errors)
+ return true;
+
+ if (var != NULL && var->cursor_explicit_expr != NULL)
+ {
+ if (prepare_expr(cstate, var->cursor_explicit_expr,
+ var->cursor_options))
+ {
+ tupdesc = expr_get_desc(cstate,
+ var->cursor_explicit_expr,
+ false, /* no element type */
+ false, /* expand record */
+ false); /* is expression */
+ if (tupdesc)
+ {
+ if (target_is_valid)
+ assign_tupdesc_row_or_rec(cstate, stmt_fetch->row, stmt_fetch->rec, tupdesc);
+ ReleaseTupleDesc(tupdesc);
+ is_valid = true;
+ }
+ }
+
+ if (!is_valid && cstate->fatal_errors)
+ return true;
+ }
+ }
+ break;
+
+ case PLPGSQL_STMT_CLOSE:
+ break;
+
+ default:
+ elog(ERROR, "unrecognized cmd_type: %d", stmt->cmd_type);
+ return false; /* be compiler quite */
+ }
+
+ return false;
+ }
+
+ /*
+ * Initialize variable to NULL
+ */
+ static void
+ var_init_to_null(PLpgSQL_checkstate *cstate, int varno)
+ {
+ PLpgSQL_var *var = (PLpgSQL_var *) cstate->estate.datums[varno];
+ var->value = (Datum) 0;
+ var->isnull = true;
+ var->freeval = false;
+ }
*** a/src/pl/plpgsql/src/pl_handler.c
--- b/src/pl/plpgsql/src/pl_handler.c
***************
*** 15,24 ****
--- 15,26 ----
#include "plpgsql.h"
+ #include "catalog/pg_language.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_type.h"
#include "funcapi.h"
#include "miscadmin.h"
+ #include "utils/array.h"
#include "utils/builtins.h"
#include "utils/guc.h"
#include "utils/lsyscache.h"
***************
*** 312,314 **** plpgsql_validator(PG_FUNCTION_ARGS)
--- 314,582 ----
PG_RETURN_VOID();
}
+
+ /* ----------
+ * plpgsql_checker
+ *
+ * This function attempts to check a embeded SQL inside a PL/pgSQL function at
+ * CHECK FUNCTION time. It should to have one or two parameters. Second
+ * parameter is a relation (used when function is trigger).
+ * ----------
+ */
+ PG_FUNCTION_INFO_V1(plpgsql_checker);
+
+ Datum
+ plpgsql_checker(PG_FUNCTION_ARGS)
+ {
+ Oid funcoid = PG_GETARG_OID(0);
+ Oid relid = PG_GETARG_OID(1);
+ ArrayType *options = PG_GETARG_ARRAYTYPE_P_COPY(2);
+ bool fatal_errors = PG_GETARG_BOOL(3);
+ HeapTuple tuple;
+ FunctionCallInfoData fake_fcinfo;
+ FmgrInfo flinfo;
+ TriggerData trigdata;
+ int rc;
+ PLpgSQL_function *function;
+ PLpgSQL_execstate *cur_estate;
+ Form_pg_proc proc;
+ char functyptype;
+ bool istrigger = false;
+ HeapTuple languageTuple;
+ Form_pg_language languageStruct;
+ TupleDesc tupdesc;
+ Tuplestorestate *tupstore;
+ MemoryContext per_query_ctx;
+ MemoryContext oldcontext;
+ ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+ ArrayType *set_items = NULL;
+ int save_nestlevel = 0;
+ Datum datum;
+ bool isnull;
+ char *funcname;
+
+ /* check to see if caller supports us returning a tuplestore */
+ if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("set-valued function called in context that cannot accept a set")));
+
+ if (!(rsinfo->allowedModes & SFRM_Materialize))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("materialize mode required, but it is not " \
+ "allowed in this context")));
+
+ /* need to build tuplestore in query context */
+ per_query_ctx = rsinfo->econtext->ecxt_per_query_memory;
+ oldcontext = MemoryContextSwitchTo(per_query_ctx);
+
+ tupdesc = CreateTupleDescCopy(rsinfo->expectedDesc);
+ tupstore = tuplestore_begin_heap(rsinfo->allowedModes & SFRM_Materialize_Random,
+ false, work_mem);
+ MemoryContextSwitchTo(oldcontext);
+
+ /*
+ * Options should be two dimensional text array. Actually just
+ * fatal_errors option is supported and this option modify
+ * a format of context string (add a function name).
+ */
+ if (ARR_NDIM(options) > 0)
+ {
+ int *dims;
+ int *lbs;
+ ArrayIterator array_iterator;
+ bool isnull;
+ Datum value;
+
+ if (ARR_NDIM(options) != 2)
+ elog(ERROR, "only two diminsional array is allowed as options");
+
+ if (ARR_ELEMTYPE(options) != TEXTOID)
+ elog(ERROR, "expected text array");
+
+ dims = ARR_DIMS(options);
+ lbs = ARR_LBOUND(options);
+ if (dims[1] - lbs[1] + 1 != 2)
+ elog(ERROR, "second dimension must be equal to two");
+
+ /* iterate over array of options */
+ array_iterator = array_create_iterator(options, 0);
+
+ while(array_iterate(array_iterator, &value, &isnull))
+ {
+ char *option_name;
+
+ if (isnull)
+ elog(ERROR, "option name is NULL");
+
+ option_name = TextDatumGetCString(value);
+
+ /* no option supported yet */
+ elog(ERROR, "unknown option \"%s\"", option_name);
+ }
+
+ /* Release temporary memory */
+ array_free_iterator(array_iterator);
+ pfree(options);
+ }
+
+ tuple = SearchSysCache1(PROCOID, ObjectIdGetDatum(funcoid));
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "cache lookup failed for function %u", funcoid);
+ proc = (Form_pg_proc) GETSTRUCT(tuple);
+
+ funcname = format_procedure(funcoid);
+
+ /* used language must be plpgsql */
+ languageTuple = SearchSysCache1(LANGOID, ObjectIdGetDatum(proc->prolang));
+ Assert(HeapTupleIsValid(languageTuple));
+
+ languageStruct = (Form_pg_language) GETSTRUCT(languageTuple);
+ if (strcmp(NameStr(languageStruct->lanname), "plpgsql") != 0)
+ elog(ERROR, "\"%s\" is not plpgsql function",
+ funcname);
+
+ ReleaseSysCache(languageTuple);
+
+ functyptype = get_typtype(proc->prorettype);
+
+ if (functyptype == TYPTYPE_PSEUDO)
+ {
+ /* we assume OPAQUE with no arguments means a trigger */
+ if (proc->prorettype == TRIGGEROID ||
+ (proc->prorettype == OPAQUEOID && proc->pronargs == 0))
+ {
+ istrigger = true;
+ if (!OidIsValid(relid))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("PL/pgSQL trigger functions cannot be checked directly"),
+ errhint("use CHECK TRIGGER statement instead")));
+ }
+ }
+
+ /*
+ * Must to set a per-function configuration parameter here, because we
+ * would to allow direct usage of checker function.
+ */
+ datum = SysCacheGetAttr(PROCOID, tuple, Anum_pg_proc_proconfig, &isnull);
+ if (!isnull)
+ {
+ /* Set per-function configuration parameters */
+ set_items = DatumGetArrayTypeP(datum);
+
+ if (set_items) /* Need a new GUC nesting level */
+ {
+ save_nestlevel = NewGUCNestLevel();
+ ProcessGUCArray(set_items,
+ (superuser() ? PGC_SUSET : PGC_USERSET),
+ PGC_S_SESSION,
+ GUC_ACTION_SAVE);
+ }
+ else
+ save_nestlevel = 0; /* keep compiler quiet */
+ }
+
+ /*
+ * Connect to SPI manager
+ */
+ if ((rc = SPI_connect()) != SPI_OK_CONNECT)
+ elog(ERROR, "SPI_connect failed: %s", SPI_result_code_string(rc));
+
+ /*
+ * Set up a fake fcinfo with just enough info to satisfy
+ * plpgsql_compile().
+ *
+ * there should be a different real argtypes for polymorphic params
+ */
+ MemSet(&fake_fcinfo, 0, sizeof(fake_fcinfo));
+ MemSet(&flinfo, 0, sizeof(flinfo));
+ fake_fcinfo.flinfo = &flinfo;
+ flinfo.fn_oid = funcoid;
+ flinfo.fn_mcxt = CurrentMemoryContext;
+
+ if (istrigger)
+ {
+ MemSet(&trigdata, 0, sizeof(trigdata));
+ trigdata.type = T_TriggerData;
+ trigdata.tg_relation = relation_open(relid, AccessShareLock);
+ fake_fcinfo.context = (Node *) &trigdata;
+ }
+
+ /* Get a compiled function */
+ function = plpgsql_compile(&fake_fcinfo, false);
+
+ /* Must save and restore prior value of cur_estate */
+ cur_estate = function->cur_estate;
+
+ /* Mark the function as busy, so it can't be deleted from under us */
+ function->use_count++;
+
+ /* Create a fake runtime environment and process check */
+ PG_TRY();
+ {
+ if (!istrigger)
+ plpgsql_check_function(function, &fake_fcinfo,
+ tupdesc,
+ tupstore,
+ fatal_errors);
+ else
+ plpgsql_check_trigger(function, &trigdata,
+ tupdesc,
+ tupstore,
+ fatal_errors);
+ }
+ PG_CATCH();
+ {
+ if (istrigger)
+ relation_close(trigdata.tg_relation, AccessShareLock);
+
+ function->cur_estate = cur_estate;
+ function->use_count--;
+
+ /*
+ * We cannot to preserve instance of this function, because
+ * expressions are not consistent - a tests on simple expression
+ * was be processed newer.
+ */
+ plpgsql_delete_function(function);
+
+ PG_RE_THROW();
+ }
+ PG_END_TRY();
+
+ if (istrigger)
+ relation_close(trigdata.tg_relation, AccessShareLock);
+
+ function->cur_estate = cur_estate;
+ function->use_count--;
+
+ /* reload back a GUC */
+ if (set_items)
+ AtEOXact_GUC(true, save_nestlevel);
+
+ /*
+ * We cannot to preserve instance of this function, because
+ * expressions are not consistent - a tests on simple expression
+ * was be processed newer.
+ */
+ plpgsql_delete_function(function);
+
+ /*
+ * Disconnect from SPI manager
+ */
+ if ((rc = SPI_finish()) != SPI_OK_FINISH)
+ elog(ERROR, "SPI_finish failed: %s", SPI_result_code_string(rc));
+
+ ReleaseSysCache(tuple);
+
+ /* clean up and return the tuplestore */
+ tuplestore_donestoring(tupstore);
+
+ rsinfo->returnMode = SFRM_Materialize;
+ rsinfo->setResult = tupstore;
+ rsinfo->setDesc = tupdesc;
+
+ return (Datum) 0;
+ }
*** a/src/pl/plpgsql/src/plpgsql--unpackaged--1.0.sql
--- b/src/pl/plpgsql/src/plpgsql--unpackaged--1.0.sql
***************
*** 5,7 **** ALTER EXTENSION plpgsql ADD PROCEDURAL LANGUAGE plpgsql;
--- 5,8 ----
ALTER EXTENSION plpgsql ADD FUNCTION plpgsql_call_handler();
ALTER EXTENSION plpgsql ADD FUNCTION plpgsql_inline_handler(internal);
ALTER EXTENSION plpgsql ADD FUNCTION plpgsql_validator(oid);
+ ALTER EXTENSION plpgsql ADD FUNCTION plpgsql_checker(oid, regclass, text[]);
*** a/src/pl/plpgsql/src/plpgsql.h
--- b/src/pl/plpgsql/src/plpgsql.h
***************
*** 767,772 **** typedef struct PLpgSQL_execstate
--- 767,779 ----
void *plugin_info; /* reserved for use by optional plugin */
} PLpgSQL_execstate;
+ typedef struct PLpgSQL_checkstate
+ {
+ PLpgSQL_execstate estate; /* check state is estate extension */
+ TupleDesc tupdesc;
+ Tuplestorestate *tupstore;
+ bool fatal_errors; /* stop on first error */
+ } PLpgSQL_checkstate;
/*
* A PLpgSQL_plugin structure represents an instrumentation plugin.
***************
*** 866,871 **** extern MemoryContext compile_tmp_cxt;
--- 873,880 ----
extern PLpgSQL_plugin **plugin_ptr;
+ extern bool plpgsql_stop_on_first_error;
+
/**********************************************************************
* Function declarations
**********************************************************************/
***************
*** 902,907 **** extern PLpgSQL_condition *plpgsql_parse_err_condition(char *condname);
--- 911,917 ----
extern void plpgsql_adddatum(PLpgSQL_datum *new);
extern int plpgsql_add_initdatums(int **varnos);
extern void plpgsql_HashTableInit(void);
+ extern void plpgsql_delete_function(PLpgSQL_function *func);
/* ----------
* Functions in pl_handler.c
***************
*** 911,916 **** extern void _PG_init(void);
--- 921,927 ----
extern Datum plpgsql_call_handler(PG_FUNCTION_ARGS);
extern Datum plpgsql_inline_handler(PG_FUNCTION_ARGS);
extern Datum plpgsql_validator(PG_FUNCTION_ARGS);
+ extern Datum plpgsql_checker(PG_FUNCTION_ARGS);
/* ----------
* Functions in pl_exec.c
***************
*** 928,933 **** extern Oid exec_get_datum_type(PLpgSQL_execstate *estate,
--- 939,954 ----
extern void exec_get_datum_type_info(PLpgSQL_execstate *estate,
PLpgSQL_datum *datum,
Oid *typeid, int32 *typmod, Oid *collation);
+ extern void plpgsql_check_function(PLpgSQL_function *func,
+ FunctionCallInfo fcinfo,
+ TupleDesc tupdesc,
+ Tuplestorestate *tupstore,
+ bool fatal_errors);
+ extern void plpgsql_check_trigger(PLpgSQL_function *func,
+ TriggerData *trigdata,
+ TupleDesc tupdesc,
+ Tuplestorestate *tupstore,
+ bool fatal_errors);
/* ----------
* Functions for namespace handling in pl_funcs.c
*** a/src/test/regress/expected/plpgsql.out
--- b/src/test/regress/expected/plpgsql.out
***************
*** 302,307 **** end;
--- 302,314 ----
' language plpgsql;
create trigger tg_hslot_biu before insert or update
on HSlot for each row execute procedure tg_hslot_biu();
+ -- check trigger should not fail
+ select count(*) from plpgsql_checker('tg_hslot_biu()', 'HSlot', '{}', true);
+ count
+ -------
+ 0
+ (1 row)
+
-- ************************************************************
-- * BEFORE DELETE on HSlot
-- * - prevent from manual manipulation
***************
*** 635,640 **** begin
--- 642,654 ----
raise exception ''illegal backlink beginning with %'', mytype;
end;
' language plpgsql;
+ -- check function should not fail
+ select count(*) from plpgsql_checker('tg_backlink_set(bpchar, bpchar)', 0, '{}', true);
+ count
+ -------
+ 0
+ (1 row)
+
-- ************************************************************
-- * Support function to clear out the backlink field if
-- * it still points to specific slot
***************
*** 2931,2936 **** NOTICE: 4 bb cc
--- 2945,2983 ----
(1 row)
+ -- check function should not fail
+ select count(*) from plpgsql_checker('for_vect()', 0, '{}', true);
+ count
+ -------
+ 0
+ (1 row)
+
+ -- recheck after check function
+ select for_vect();
+ NOTICE: 1
+ NOTICE: 2
+ NOTICE: 3
+ NOTICE: 1 BB CC
+ NOTICE: 2 BB CC
+ NOTICE: 3 BB CC
+ NOTICE: 4 BB CC
+ NOTICE: 1
+ NOTICE: 2
+ NOTICE: 3
+ NOTICE: 4
+ NOTICE: 1 BB CC
+ NOTICE: 2 BB CC
+ NOTICE: 3 BB CC
+ NOTICE: 4 BB CC
+ NOTICE: 1 bb cc
+ NOTICE: 2 bb cc
+ NOTICE: 3 bb cc
+ NOTICE: 4 bb cc
+ for_vect
+ ----------
+
+ (1 row)
+
-- regression test: verify that multiple uses of same plpgsql datum within
-- a SQL command all get mapped to the same $n parameter. The return value
-- of the SELECT is not important, we only care that it doesn't fail with
***************
*** 3412,3417 **** begin
--- 3459,3471 ----
return;
end;
$$ language plpgsql;
+ -- check function should not fail
+ select count(*) from plpgsql_checker('forc01()', 0, '{}', true);
+ count
+ -------
+ 0
+ (1 row)
+
select forc01();
NOTICE: 5 from c
NOTICE: 6 from c
***************
*** 3845,3850 **** begin
--- 3899,3911 ----
end case;
end;
$$ language plpgsql immutable;
+ -- check function should not fail
+ select count(*) from plpgsql_checker('case_test(bigint)', 0, '{}', true);
+ count
+ -------
+ 0
+ (1 row)
+
select case_test(1);
case_test
-----------
***************
*** 4700,4702 **** ERROR: value for domain orderedarray violates check constraint "sorted"
--- 4761,5237 ----
CONTEXT: PL/pgSQL function "testoa" line 5 at assignment
drop function arrayassign1();
drop function testoa(x1 int, x2 int, x3 int);
+ --
+ -- check function statement tests
+ --
+ --should fail - is not plpgsql
+ select lineno, statement, sqlstate, message, detail, hint, level, "position", query from plpgsql_checker('session_user()', 0, '{}', true);
+ ERROR: ""session_user"()" is not plpgsql function
+ create table t1(a int, b int);
+ create function f1()
+ returns void as $$
+ begin
+ if false then
+ update t1 set c = 30;
+ end if;
+ if false then
+ raise notice '% %', r.c;
+ end if;
+ end;
+ $$ language plpgsql;
+ select f1();
+ f1
+ ----
+
+ (1 row)
+
+ select lineno, statement, sqlstate, message, detail, hint, level, "position", query from plpgsql_checker('f1()', 0, '{}', true);
+ lineno | statement | sqlstate | message | detail | hint | level | position | query
+ --------+---------------+----------+--------------------------------------------+--------+------+-------+----------+----------------------
+ 4 | SQL statement | 42703 | column "c" of relation "t1" does not exist | | | error | 15 | update t1 set c = 30
+ (1 row)
+
+ select lineno, statement, sqlstate, message, detail, hint, level, "position", query from plpgsql_checker('f1()', 0, '{}', false);
+ lineno | statement | sqlstate | message | detail | hint | level | position | query
+ --------+---------------+----------+--------------------------------------------+--------+------+-------+----------+----------------------
+ 4 | SQL statement | 42703 | column "c" of relation "t1" does not exist | | | error | 15 | update t1 set c = 30
+ 7 | RAISE | 42P01 | missing FROM-clause entry for table "r" | | | error | 8 | SELECT r.c
+ 7 | RAISE | 42601 | too few parameters specified for RAISE | | | error | |
+ (3 rows)
+
+ check function f1();
+ CHECK FUNCTION
+ ------------------------------------------------------------------------
+ In function: 'f1()'
+ error:42703:4:SQL statement:column "c" of relation "t1" does not exist
+ query:update t1 set c = 30
+ ^
+ error:42P01:7:RAISE:missing FROM-clause entry for table "r"
+ query:SELECT r.c
+ ^
+ error:42601:7:RAISE:too few parameters specified for RAISE
+ (8 rows)
+
+ select f1();
+ f1
+ ----
+
+ (1 row)
+
+ drop function f1();
+ create function g1(out a int, out b int)
+ as $$
+ select 10,20;
+ $$ language sql;
+ create function f1()
+ returns void as $$
+ declare r record;
+ begin
+ r := g1();
+ if false then
+ raise notice '%', r.c;
+ end if;
+ end;
+ $$ language plpgsql;
+ select f1();
+ f1
+ ----
+
+ (1 row)
+
+ select lineno, statement, sqlstate, message, detail, hint, level, "position", query from plpgsql_checker('f1()', 0, '{}', true);
+ lineno | statement | sqlstate | message | detail | hint | level | position | query
+ --------+-----------+----------+-----------------------------+--------+------+-------+----------+-------
+ 6 | RAISE | 42703 | record "r" has no field "c" | | | error | |
+ (1 row)
+
+ check function f1();
+ CHECK FUNCTION
+ -------------------------------------------------
+ In function: 'f1()'
+ error:42703:6:RAISE:record "r" has no field "c"
+ (2 rows)
+
+ select f1();
+ f1
+ ----
+
+ (1 row)
+
+ drop function f1();
+ drop function g1();
+ create function g1(out a int, out b int)
+ returns setof record as $$
+ select * from t1;
+ $$ language sql;
+ create function f1()
+ returns void as $$
+ declare r record;
+ begin
+ for r in select * from g1()
+ loop
+ raise notice '%', r.c;
+ end loop;
+ end;
+ $$ language plpgsql;
+ select f1();
+ f1
+ ----
+
+ (1 row)
+
+ select lineno, statement, sqlstate, message, detail, hint, level, "position", query from plpgsql_checker('f1()', 0, '{}', true);
+ lineno | statement | sqlstate | message | detail | hint | level | position | query
+ --------+-----------+----------+-----------------------------+--------+------+-------+----------+-------
+ 6 | RAISE | 42703 | record "r" has no field "c" | | | error | |
+ (1 row)
+
+ check function f1();
+ CHECK FUNCTION
+ -------------------------------------------------
+ In function: 'f1()'
+ error:42703:6:RAISE:record "r" has no field "c"
+ (2 rows)
+
+ select f1();
+ f1
+ ----
+
+ (1 row)
+
+ create or replace function f1()
+ returns void as $$
+ declare r record;
+ begin
+ for r in select * from g1()
+ loop
+ r.c := 20;
+ end loop;
+ end;
+ $$ language plpgsql;
+ select f1();
+ f1
+ ----
+
+ (1 row)
+
+ select lineno, statement, sqlstate, message, detail, hint, level, "position", query from plpgsql_checker('f1()', 0, '{}', true);
+ lineno | statement | sqlstate | message | detail | hint | level | position | query
+ --------+------------+----------+-----------------------------+--------+------+-------+----------+-------
+ 6 | assignment | 42703 | record "r" has no field "c" | | | error | |
+ (1 row)
+
+ check function f1();
+ CHECK FUNCTION
+ ------------------------------------------------------
+ In function: 'f1()'
+ error:42703:6:assignment:record "r" has no field "c"
+ (2 rows)
+
+ select f1();
+ f1
+ ----
+
+ (1 row)
+
+ drop function f1();
+ drop function g1();
+ create function f1()
+ returns int as $$
+ declare r int;
+ begin
+ if false then
+ r := a + b;
+ end if;
+ return r;
+ end;
+ $$ language plpgsql;
+ select f1();
+ f1
+ ----
+
+ (1 row)
+
+ select lineno, statement, sqlstate, message, detail, hint, level, "position", query from plpgsql_checker('f1()', 0, '{}', true);
+ lineno | statement | sqlstate | message | detail | hint | level | position | query
+ --------+------------+----------+---------------------------+--------+------+-------+----------+--------------
+ 5 | assignment | 42703 | column "a" does not exist | | | error | 8 | SELECT a + b
+ (1 row)
+
+ check function f1();
+ CHECK FUNCTION
+ ----------------------------------------------------
+ In function: 'f1()'
+ error:42703:5:assignment:column "a" does not exist
+ query:SELECT a + b
+ ^
+ (4 rows)
+
+ select f1();
+ f1
+ ----
+
+ (1 row)
+
+ drop function f1();
+ create or replace function f1()
+ returns void as $$
+ begin
+ if false then
+ raise notice '%', 1, 2;
+ end if;
+ end;
+ $$ language plpgsql;
+ select f1();
+ f1
+ ----
+
+ (1 row)
+
+ select lineno, statement, sqlstate, message, detail, hint, level, "position", query from plpgsql_checker('f1()', 0, '{}', true);
+ lineno | statement | sqlstate | message | detail | hint | level | position | query
+ --------+-----------+----------+-----------------------------------------+--------+------+-------+----------+-------
+ 4 | RAISE | 42601 | too many parameters specified for RAISE | | | error | |
+ (1 row)
+
+ check function f1();
+ CHECK FUNCTION
+ -------------------------------------------------------------
+ In function: 'f1()'
+ error:42601:4:RAISE:too many parameters specified for RAISE
+ (2 rows)
+
+ select f1();
+ f1
+ ----
+
+ (1 row)
+
+ drop function f1();
+ create or replace function f1()
+ returns void as $$
+ begin
+ if false then
+ raise notice '% %';
+ end if;
+ end;
+ $$ language plpgsql;
+ select f1();
+ f1
+ ----
+
+ (1 row)
+
+ select lineno, statement, sqlstate, message, detail, hint, level, "position", query from plpgsql_checker('f1()', 0, '{}', true);
+ lineno | statement | sqlstate | message | detail | hint | level | position | query
+ --------+-----------+----------+----------------------------------------+--------+------+-------+----------+-------
+ 4 | RAISE | 42601 | too few parameters specified for RAISE | | | error | |
+ (1 row)
+
+ check function f1();
+ CHECK FUNCTION
+ ------------------------------------------------------------
+ In function: 'f1()'
+ error:42601:4:RAISE:too few parameters specified for RAISE
+ (2 rows)
+
+ select f1();
+ f1
+ ----
+
+ (1 row)
+
+ drop function f1();
+ create or replace function f1()
+ returns void as $$
+ declare r int[];
+ begin
+ if false then
+ r[c+10] := 20;
+ end if;
+ end;
+ $$ language plpgsql;
+ select f1();
+ f1
+ ----
+
+ (1 row)
+
+ select lineno, statement, sqlstate, message, detail, hint, level, "position", query from plpgsql_checker('f1()', 0, '{}', true);
+ lineno | statement | sqlstate | message | detail | hint | level | position | query
+ --------+------------+----------+---------------------------+--------+------+-------+----------+-------------
+ 5 | assignment | 42703 | column "c" does not exist | | | error | 8 | SELECT c+10
+ (1 row)
+
+ check function f1();
+ CHECK FUNCTION
+ ----------------------------------------------------
+ In function: 'f1()'
+ error:42703:5:assignment:column "c" does not exist
+ query:SELECT c+10
+ ^
+ (4 rows)
+
+ select f1();
+ f1
+ ----
+
+ (1 row)
+
+ drop function f1();
+ create or replace function f1()
+ returns void as $$
+ declare r int;
+ begin
+ if false then
+ r[10] := 20;
+ end if;
+ end;
+ $$ language plpgsql set search_path = public;
+ select f1();
+ f1
+ ----
+
+ (1 row)
+
+ select lineno, statement, sqlstate, message, detail, hint, level, "position", query from plpgsql_checker('f1()', 0, '{}', true);
+ lineno | statement | sqlstate | message | detail | hint | level | position | query
+ --------+------------+----------+------------------------------------+--------+------+-------+----------+-------
+ 5 | assignment | 42804 | subscripted object is not an array | | | error | |
+ (1 row)
+
+ check function f1();
+ CHECK FUNCTION
+ -------------------------------------------------------------
+ In function: 'f1()'
+ error:42804:5:assignment:subscripted object is not an array
+ (2 rows)
+
+ select f1();
+ f1
+ ----
+
+ (1 row)
+
+ drop function f1();
+ create type _exception_type as (
+ state text,
+ message text,
+ detail text);
+ create or replace function f1()
+ returns void as $$
+ declare
+ _exception record;
+ begin
+ _exception := NULL::_exception_type;
+ exception when others then
+ get stacked diagnostics
+ _exception.state = RETURNED_SQLSTATE,
+ _exception.message = MESSAGE_TEXT,
+ _exception.detail = PG_EXCEPTION_DETAIL,
+ _exception.hint = PG_EXCEPTION_HINT;
+ end;
+ $$ language plpgsql;
+ select f1();
+ f1
+ ----
+
+ (1 row)
+
+ select lineno, statement, sqlstate, message, detail, hint, level, "position", query from plpgsql_checker('f1()', 0, '{}', true);
+ lineno | statement | sqlstate | message | detail | hint | level | position | query
+ --------+-----------------+----------+-----------------------------------------+--------+------+-------+----------+-------
+ 7 | GET DIAGNOSTICS | 42703 | record "_exception" has no field "hint" | | | error | |
+ (1 row)
+
+ check function f1();
+ CHECK FUNCTION
+ -----------------------------------------------------------------------
+ In function: 'f1()'
+ error:42703:7:GET DIAGNOSTICS:record "_exception" has no field "hint"
+ (2 rows)
+
+ drop function f1();
+ create or replace function f1_trg()
+ returns trigger as $$
+ begin
+ if new.a > 10 then
+ raise notice '%', new.b;
+ raise notice '%', new.c;
+ end if;
+ return new;
+ end;
+ $$ language plpgsql;
+ create trigger t1_f1 before insert on t1
+ for each row
+ execute procedure f1_trg();
+ insert into t1 values(6,30);
+ select lineno, statement, sqlstate, message, detail, hint, level, "position", query from plpgsql_checker('f1_trg()', 't1', '{}', true);
+ lineno | statement | sqlstate | message | detail | hint | level | position | query
+ --------+-----------+----------+-------------------------------+--------+------+-------+----------+-------
+ 5 | RAISE | 42703 | record "new" has no field "c" | | | error | |
+ (1 row)
+
+ check trigger t1_f1 on t1;
+ CHECK TRIGGER
+ ---------------------------------------------------
+ In function: 'f1_trg()'
+ error:42703:5:RAISE:record "new" has no field "c"
+ (2 rows)
+
+ insert into t1 values(6,30);
+ create or replace function f1_trg()
+ returns trigger as $$
+ begin
+ new.a := new.a + 10;
+ new.b := new.b + 10;
+ new.c := 30;
+ return new;
+ end;
+ $$ language plpgsql;
+ -- should to fail
+ select lineno, statement, sqlstate, message, detail, hint, level, "position", query from plpgsql_checker('f1_trg()', 't1', '{}', true);
+ lineno | statement | sqlstate | message | detail | hint | level | position | query
+ --------+------------+----------+-------------------------------+--------+------+-------+----------+-------
+ 5 | assignment | 42703 | record "new" has no field "c" | | | error | |
+ (1 row)
+
+ check trigger t1_f1 on t1;
+ CHECK TRIGGER
+ --------------------------------------------------------
+ In function: 'f1_trg()'
+ error:42703:5:assignment:record "new" has no field "c"
+ (2 rows)
+
+ -- should to fail but not crash
+ insert into t1 values(6,30);
+ ERROR: record "new" has no field "c"
+ CONTEXT: PL/pgSQL function "f1_trg" line 5 at assignment
+ create or replace function f1_trg()
+ returns trigger as $$
+ begin
+ new.a := new.a + 10;
+ new.b := new.b + 10;
+ return new;
+ end;
+ $$ language plpgsql;
+ -- ok
+ select count(*) from plpgsql_checker('f1_trg()', 't1', '{}', true);
+ count
+ -------
+ 0
+ (1 row)
+
+ -- ok
+ insert into t1 values(6,30);
+ select * from t1;
+ a | b
+ ----+----
+ 6 | 30
+ 6 | 30
+ 16 | 40
+ (3 rows)
+
+ drop table t1;
+ drop type _exception_type;
+ drop function f1_trg();
*** a/src/test/regress/sql/plpgsql.sql
--- b/src/test/regress/sql/plpgsql.sql
***************
*** 366,371 **** end;
--- 366,373 ----
create trigger tg_hslot_biu before insert or update
on HSlot for each row execute procedure tg_hslot_biu();
+ -- check trigger should not fail
+ select count(*) from plpgsql_checker('tg_hslot_biu()', 'HSlot', '{}', true);
-- ************************************************************
-- * BEFORE DELETE on HSlot
***************
*** 747,752 **** begin
--- 749,757 ----
end;
' language plpgsql;
+ -- check function should not fail
+ select count(*) from plpgsql_checker('tg_backlink_set(bpchar, bpchar)', 0, '{}', true);
+
-- ************************************************************
-- * Support function to clear out the backlink field if
***************
*** 2443,2448 **** $proc$ language plpgsql;
--- 2448,2460 ----
select for_vect();
+ -- check function should not fail
+ select count(*) from plpgsql_checker('for_vect()', 0, '{}', true);
+
+ -- recheck after check function
+ select for_vect();
+
+
-- regression test: verify that multiple uses of same plpgsql datum within
-- a SQL command all get mapped to the same $n parameter. The return value
-- of the SELECT is not important, we only care that it doesn't fail with
***************
*** 2822,2827 **** begin
--- 2834,2842 ----
end;
$$ language plpgsql;
+ -- check function should not fail
+ select count(*) from plpgsql_checker('forc01()', 0, '{}', true);
+
select forc01();
-- try updating the cursor's current row
***************
*** 3156,3161 **** begin
--- 3171,3180 ----
end;
$$ language plpgsql immutable;
+ -- check function should not fail
+ select count(*) from plpgsql_checker('case_test(bigint)', 0, '{}', true);
+
+
select case_test(1);
select case_test(2);
select case_test(3);
***************
*** 3708,3710 **** select testoa(1,2,1); -- fail at update
--- 3727,4013 ----
drop function arrayassign1();
drop function testoa(x1 int, x2 int, x3 int);
+
+ --
+ -- check function statement tests
+ --
+
+ --should fail - is not plpgsql
+ select lineno, statement, sqlstate, message, detail, hint, level, "position", query from plpgsql_checker('session_user()', 0, '{}', true);
+
+ create table t1(a int, b int);
+
+ create function f1()
+ returns void as $$
+ begin
+ if false then
+ update t1 set c = 30;
+ end if;
+ if false then
+ raise notice '% %', r.c;
+ end if;
+ end;
+ $$ language plpgsql;
+
+ select f1();
+ select lineno, statement, sqlstate, message, detail, hint, level, "position", query from plpgsql_checker('f1()', 0, '{}', true);
+ select lineno, statement, sqlstate, message, detail, hint, level, "position", query from plpgsql_checker('f1()', 0, '{}', false);
+
+ check function f1();
+
+ select f1();
+
+ drop function f1();
+
+ create function g1(out a int, out b int)
+ as $$
+ select 10,20;
+ $$ language sql;
+
+ create function f1()
+ returns void as $$
+ declare r record;
+ begin
+ r := g1();
+ if false then
+ raise notice '%', r.c;
+ end if;
+ end;
+ $$ language plpgsql;
+
+ select f1();
+ select lineno, statement, sqlstate, message, detail, hint, level, "position", query from plpgsql_checker('f1()', 0, '{}', true);
+
+ check function f1();
+
+ select f1();
+
+ drop function f1();
+ drop function g1();
+
+ create function g1(out a int, out b int)
+ returns setof record as $$
+ select * from t1;
+ $$ language sql;
+
+ create function f1()
+ returns void as $$
+ declare r record;
+ begin
+ for r in select * from g1()
+ loop
+ raise notice '%', r.c;
+ end loop;
+ end;
+ $$ language plpgsql;
+
+ select f1();
+ select lineno, statement, sqlstate, message, detail, hint, level, "position", query from plpgsql_checker('f1()', 0, '{}', true);
+
+ check function f1();
+
+ select f1();
+
+ create or replace function f1()
+ returns void as $$
+ declare r record;
+ begin
+ for r in select * from g1()
+ loop
+ r.c := 20;
+ end loop;
+ end;
+ $$ language plpgsql;
+
+ select f1();
+ select lineno, statement, sqlstate, message, detail, hint, level, "position", query from plpgsql_checker('f1()', 0, '{}', true);
+
+ check function f1();
+
+ select f1();
+
+ drop function f1();
+ drop function g1();
+
+ create function f1()
+ returns int as $$
+ declare r int;
+ begin
+ if false then
+ r := a + b;
+ end if;
+ return r;
+ end;
+ $$ language plpgsql;
+
+ select f1();
+ select lineno, statement, sqlstate, message, detail, hint, level, "position", query from plpgsql_checker('f1()', 0, '{}', true);
+
+ check function f1();
+
+ select f1();
+
+ drop function f1();
+
+ create or replace function f1()
+ returns void as $$
+ begin
+ if false then
+ raise notice '%', 1, 2;
+ end if;
+ end;
+ $$ language plpgsql;
+
+ select f1();
+ select lineno, statement, sqlstate, message, detail, hint, level, "position", query from plpgsql_checker('f1()', 0, '{}', true);
+
+ check function f1();
+
+ select f1();
+
+ drop function f1();
+
+ create or replace function f1()
+ returns void as $$
+ begin
+ if false then
+ raise notice '% %';
+ end if;
+ end;
+ $$ language plpgsql;
+
+ select f1();
+ select lineno, statement, sqlstate, message, detail, hint, level, "position", query from plpgsql_checker('f1()', 0, '{}', true);
+
+ check function f1();
+
+ select f1();
+
+ drop function f1();
+
+ create or replace function f1()
+ returns void as $$
+ declare r int[];
+ begin
+ if false then
+ r[c+10] := 20;
+ end if;
+ end;
+ $$ language plpgsql;
+
+ select f1();
+ select lineno, statement, sqlstate, message, detail, hint, level, "position", query from plpgsql_checker('f1()', 0, '{}', true);
+
+ check function f1();
+
+ select f1();
+
+ drop function f1();
+
+ create or replace function f1()
+ returns void as $$
+ declare r int;
+ begin
+ if false then
+ r[10] := 20;
+ end if;
+ end;
+ $$ language plpgsql set search_path = public;
+
+ select f1();
+ select lineno, statement, sqlstate, message, detail, hint, level, "position", query from plpgsql_checker('f1()', 0, '{}', true);
+
+ check function f1();
+
+ select f1();
+
+ drop function f1();
+
+ create type _exception_type as (
+ state text,
+ message text,
+ detail text);
+
+ create or replace function f1()
+ returns void as $$
+ declare
+ _exception record;
+ begin
+ _exception := NULL::_exception_type;
+ exception when others then
+ get stacked diagnostics
+ _exception.state = RETURNED_SQLSTATE,
+ _exception.message = MESSAGE_TEXT,
+ _exception.detail = PG_EXCEPTION_DETAIL,
+ _exception.hint = PG_EXCEPTION_HINT;
+ end;
+ $$ language plpgsql;
+
+ select f1();
+ select lineno, statement, sqlstate, message, detail, hint, level, "position", query from plpgsql_checker('f1()', 0, '{}', true);
+
+ check function f1();
+
+ drop function f1();
+
+ create or replace function f1_trg()
+ returns trigger as $$
+ begin
+ if new.a > 10 then
+ raise notice '%', new.b;
+ raise notice '%', new.c;
+ end if;
+ return new;
+ end;
+ $$ language plpgsql;
+
+ create trigger t1_f1 before insert on t1
+ for each row
+ execute procedure f1_trg();
+
+ insert into t1 values(6,30);
+ select lineno, statement, sqlstate, message, detail, hint, level, "position", query from plpgsql_checker('f1_trg()', 't1', '{}', true);
+
+ check trigger t1_f1 on t1;
+
+ insert into t1 values(6,30);
+
+ create or replace function f1_trg()
+ returns trigger as $$
+ begin
+ new.a := new.a + 10;
+ new.b := new.b + 10;
+ new.c := 30;
+ return new;
+ end;
+ $$ language plpgsql;
+
+ -- should to fail
+ select lineno, statement, sqlstate, message, detail, hint, level, "position", query from plpgsql_checker('f1_trg()', 't1', '{}', true);
+
+ check trigger t1_f1 on t1;
+
+ -- should to fail but not crash
+ insert into t1 values(6,30);
+
+ create or replace function f1_trg()
+ returns trigger as $$
+ begin
+ new.a := new.a + 10;
+ new.b := new.b + 10;
+ return new;
+ end;
+ $$ language plpgsql;
+
+ -- ok
+ select count(*) from plpgsql_checker('f1_trg()', 't1', '{}', true);
+
+ -- ok
+ insert into t1 values(6,30);
+
+ select * from t1;
+
+ drop table t1;
+ drop type _exception_type;
+
+ drop function f1_trg();