Re: review: CHECK FUNCTION statement
Bruce Momjian <bruce@momjian.us>
From: Bruce Momjian <bruce@momjian.us>
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: Albe Laurenz <laurenz.albe@wien.gv.at>, PostgreSQL Hackers <pgsql-hackers@postgresql.org>, Tom Lane <tgl@sss.pgh.pa.us>
Date: 2012-08-17T01:08:00Z
Lists: pgsql-hackers
Commits
Same data as JSON:
GET /api/v1/messages/:b64id/commits
the thread's linked commits as JSON, with link sources.
API reference →
-
Add const qualifiers to node inspection functions
- d5f23af6bfbc 9.2.0 cited
What happened to this feature patch? A TODO?
---------------------------------------------------------------------------
On Tue, Nov 29, 2011 at 08:37:15PM +0100, Pavel Stehule wrote:
> Hello
>
> updated patch:
>
> * recheck compilation and initdb
> * working routines moved to pl_exec.c
> * add entry to catalog.sgml about lanchecker field
> * add node's utils
>
> Regards
>
> Pavel Stehule
>
> 2011/11/29 Albe Laurenz <laurenz.albe@wien.gv.at>:
> > Pavel Stehule wrote:
> >> I am sending updated patch, that implements a CHECK FUNCTION and CHECK
> >> TRIGGER statements.
> >>
> >> This patch is significantly redesigned to previous version (PL/pgSQL
> >> part) - it is more readable, more accurate. There are new regress
> >> tests.
> >>
> >> Please, can some English native speaker fix doc and comments?
> >
> >> ToDo:
> >>
> >> CHECK FUNCTION search function according to function signature - it
> >> should be changes for using a actual types - it can be solution for
> >> polymorphic types and useful tool for work with overloaded functions -
> >> when is not clean, that function was executed.
> >>
> >> check function foo(int, int);
> >> NOTICE: checking function foo(variadic anyarray)
> >> ...
> >>
> >> and maybe some support for named parameters
> >> check function foo(name text, surname text);
> >> NOTICE: checking function foo(text, text, text, text)
> >> ...
> >
> > I think that CHECK FUNCTION should work exactly like DROP FUNCTION
> > in these respects.
> >
> > Submission review:
> > ------------------
> >
> > The patch is context diff, applies with some offsets, contains
> > regression tests and documentation.
> >
> > The documentation should be expanded, the doc for CHECK FUNCTION
> > is only a stub. It should describe the procedure and what is checked.
> > That would also make reviewing easier.
> > I think that some documentation should be added to plhandler.sgml.
> > There is a spelling error (statemnt) in the docs.
> >
> > Usability review:
> > -----------------
> >
> > If I understand right, the goal of CHECK FUNCTION is to find errors in
> > the function definition without actually having to execute it.
> > The patch tries to provide this for PL/pgSQL.
> >
> > There hasn't been any discussion on the list, the patch was just posted,
> > so I can't say that we want that. Tom added it to the commitfest page,
> > so there's one important voice against dismissing it right away :^)
> >
> > I don't understand the functional difference between a "validator function"
> > and a "check function" as proposed by this patch. I am probably missing
> > something, but why couldn't these checks be added to function validation
> > when check_function_bodies is set?
> > A new "CHECK FUNCTION" statement could simply call the validator function.
> >
> > I don't see any pg_dump support in this patch, and PL/pgSQL probably doesn't
> > need that, but I think pg_dump support for CREATE LANGUAGE would have to
> > be added for other PLs.
> >
> > I can't test if the functionality is complete because I can't get it to
> > run (see below).
> >
> > Feature test:
> > -------------
> >
> > I can't really test the patch because initdb fails:
> >
> > $ initdb -E UTF8 --locale=de_DE.UTF-8 --lc-messages=en_US.UTF-8 -U postgres /postgres/cvs/dbhome
> > The files belonging to this database system will be owned by user "laurenz".
> > This user must also own the server process.
> >
> > The database cluster will be initialized with locales
> > COLLATE: de_DE.UTF-8
> > CTYPE: de_DE.UTF-8
> > MESSAGES: en_US.UTF-8
> > MONETARY: de_DE.UTF-8
> > NUMERIC: de_DE.UTF-8
> > TIME: de_DE.UTF-8
> > The default text search configuration will be set to "german".
> >
> > creating directory /postgres/cvs/dbhome ... ok
> > creating subdirectories ... ok
> > selecting default max_connections ... 100
> > selecting default shared_buffers ... 32MB
> > creating configuration files ... ok
> > creating template1 database in /postgres/cvs/dbhome/base/1 ... ok
> > initializing pg_authid ... ok
> > initializing dependencies ... ok
> > creating system views ... ok
> > loading system objects' descriptions ... ok
> > creating collations ... ok
> > creating conversions ... ok
> > creating dictionaries ... ok
> > setting privileges on built-in objects ... ok
> > creating information schema ... ok
> > loading PL/pgSQL server-side language ... FATAL: could not load library "/postgres/cvs/pg92/lib/plpgsql.so": /postgres/cvs/pg92/lib/plpgsql.so: undefined symbol: plpgsql_delete_function
> > STATEMENT: CREATE EXTENSION plpgsql;
> >
> > child process exited with exit code 1
> > initdb: removing data directory "/postgres/cvs/dbhome"
> >
> > Coding review:
> > --------------
> >
> > The patch compiles without warnings.
> > The comments in the code should be revised, they are bad English.
> > I can't say if there should be more of them -- I don't know this part of
> > the code well enough to have a well-founded opinion.
> >
> > I don't think there are any portability issues, but I could not test it.
> >
> > There are a lot of small changes to pl/plpgsql/src/pl_exec.c, are they all
> > necessary? For example, why was copy_plpgsql_datum renamed to
> > plpgsql_copy_datum?
> >
> > I'll mark the patch as "Waiting on Author".
> >
> > Yours,
> > Laurenz Albe
> >
> *** ./doc/src/sgml/catalogs.sgml.orig 2011-11-29 19:09:02.000000000 +0100
> --- ./doc/src/sgml/catalogs.sgml 2011-11-29 20:28:00.571246006 +0100
> ***************
> *** 3652,3657 ****
> --- 3652,3668 ----
> </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 language checker function that is responsible
> + for checking a embedded SQL and can provide detailed checking.
> + Zero if no checker is provided.
> + </entry>
> + </row>
> +
> + <row>
> <entry><structfield>lanacl</structfield></entry>
> <entry><type>aclitem[]</type></entry>
> <entry></entry>
> *** ./doc/src/sgml/ref/allfiles.sgml.orig 2011-11-29 19:20:59.468117093 +0100
> --- ./doc/src/sgml/ref/allfiles.sgml 2011-11-29 19:21:24.487804955 +0100
> ***************
> *** 40,45 ****
> --- 40,46 ----
> <!ENTITY alterView SYSTEM "alter_view.sgml">
> <!ENTITY analyze SYSTEM "analyze.sgml">
> <!ENTITY begin SYSTEM "begin.sgml">
> + <!ENTITY checkFunction SYSTEM "check_function.sgml">
> <!ENTITY checkpoint SYSTEM "checkpoint.sgml">
> <!ENTITY close SYSTEM "close.sgml">
> <!ENTITY cluster SYSTEM "cluster.sgml">
> *** ./doc/src/sgml/ref/create_language.sgml.orig 2011-11-29 19:20:59.470117069 +0100
> --- ./doc/src/sgml/ref/create_language.sgml 2011-11-29 19:21:24.488804943 +0100
> ***************
> *** 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> ]
> </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 ****
> --- 217,236 ----
> </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
> + when a new function in the language is created, to check the
> + function by statemnt <command>CHECK FUNCTION</command> or
> + <command>CHECK TRIGGER</command>.
> + </para>
> + </listitem>
> + </varlistentry>
> +
> </variablelist>
>
> <para>
> *** ./doc/src/sgml/reference.sgml.orig 2011-11-29 19:20:59.471117057 +0100
> --- ./doc/src/sgml/reference.sgml 2011-11-29 19:21:24.492804895 +0100
> ***************
> *** 68,73 ****
> --- 68,74 ----
> &alterView;
> &analyze;
> &begin;
> + &checkFunction;
> &checkpoint;
> &close;
> &cluster;
> *** ./src/backend/catalog/pg_proc.c.orig 2011-11-29 19:20:59.474117021 +0100
> --- ./src/backend/catalog/pg_proc.c 2011-11-29 19:21:24.494804869 +0100
> ***************
> *** 1101,1103 ****
> --- 1101,1104 ----
> *newcursorpos = newcp;
> return false;
> }
> +
> *** ./src/backend/commands/functioncmds.c.orig 2011-11-29 19:20:59.475117009 +0100
> --- ./src/backend/commands/functioncmds.c 2011-11-29 19:21:24.496804843 +0100
> ***************
> *** 44,53 ****
> --- 44,55 ----
> #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 "miscadmin.h"
> #include "optimizer/var.h"
> #include "parser/parse_coerce.h"
> ***************
> *** 60,65 ****
> --- 62,68 ----
> #include "utils/fmgroids.h"
> #include "utils/guc.h"
> #include "utils/lsyscache.h"
> + #include "utils/memutils.h"
> #include "utils/rel.h"
> #include "utils/syscache.h"
> #include "utils/tqual.h"
> ***************
> *** 1009,1014 ****
> --- 1012,1152 ----
> }
> }
>
> + /*
> + * CheckFunction
> + * call a PL checker function when this function exists.
> + */
> + void
> + CheckFunction(CheckFunctionStmt *stmt)
> + {
> + List *functionName = stmt->funcname;
> + List *argTypes = stmt->args; /* list of TypeName nodes */
> + Oid funcOid;
> +
> + HeapTuple tup;
> + Form_pg_proc proc;
> +
> + HeapTuple languageTuple;
> + Form_pg_language languageStruct;
> + Oid languageChecker;
> + Oid trgOid = InvalidOid;
> + Oid relid = InvalidOid;
> +
> + /* when we should to check trigger, then we should to find a trigger handler */
> + if (functionName == NULL)
> + {
> + HeapTuple ht_trig;
> + Form_pg_trigger trigrec;
> + ScanKeyData skey[1];
> + Relation tgrel;
> + SysScanDesc tgscan;
> + char *fname;
> +
> + relid = RangeVarGetRelid(stmt->relation, ShareLock, false, 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;
> + fname = format_procedure(funcOid);
> + /* Clean up */
> + systable_endscan(tgscan);
> +
> + elog(NOTICE, "checking function \"%s\"", fname);
> + pfree(fname);
> +
> + heap_close(tgrel, AccessShareLock);
> + }
> + else
> + {
> + /*
> + * Find the function,
> + */
> + funcOid = LookupFuncNameTypeNames(functionName, argTypes, false);
> + }
> +
> + 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;
> +
> + /* Check a function body */
> + if (OidIsValid(languageChecker))
> + {
> + ArrayType *set_items = NULL;
> + int save_nestlevel;
> + Datum datum;
> + bool isnull;
> + MemoryContext oldCxt;
> + MemoryContext checkCxt;
> +
> + datum = SysCacheGetAttr(PROCOID, tup, Anum_pg_proc_proconfig, &isnull);
> +
> + if (!isnull)
> + {
> + /* Set per-function configuration parameters */
> + set_items = (ArrayType *) DatumGetPointer(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 */
> + }
> +
> + checkCxt = AllocSetContextCreate(CurrentMemoryContext,
> + "Check temporary context",
> + ALLOCSET_DEFAULT_MINSIZE,
> + ALLOCSET_DEFAULT_INITSIZE,
> + ALLOCSET_DEFAULT_MAXSIZE);
> +
> + oldCxt = MemoryContextSwitchTo(checkCxt);
> +
> + OidFunctionCall2(languageChecker, ObjectIdGetDatum(funcOid),
> + ObjectIdGetDatum(relid));
> +
> + MemoryContextSwitchTo(oldCxt);
> +
> + if (set_items)
> + AtEOXact_GUC(true, save_nestlevel);
> + }
> + else
> + elog(WARNING, "language \"%s\" has no defined checker function",
> + NameStr(languageStruct->lanname));
> +
> + ReleaseSysCache(languageTuple);
> + ReleaseSysCache(tup);
> + }
>
> /*
> * Rename function
> *** ./src/backend/commands/proclang.c.orig 2011-11-29 19:20:59.477116983 +0100
> --- ./src/backend/commands/proclang.c 2011-11-29 19:21:24.497804830 +0100
> ***************
> *** 46,57 ****
> 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 ****
> 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[2];
>
> /*
> * If we have template information for the language, ignore the supplied
> ***************
> *** 219,228 ****
> else
> valOid = InvalidOid;
>
> /* ok, create it */
> create_proc_lang(stmt->plname, stmt->replace, GetUserId(),
> handlerOid, inlineOid,
> ! valOid, pltemplate->tmpltrusted);
> }
> else
> {
> --- 221,269 ----
> 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] = OIDOID;
> + funcargtypes[1] = REGCLASSOID;
> + checkerOid = LookupFuncName(funcname, 2, funcargtypes, true);
> + if (!OidIsValid(checkerOid))
> + {
> + checkerOid = ProcedureCreate(pltemplate->tmplchecker,
> + PG_CATALOG_NAMESPACE,
> + false, /* replace */
> + false, /* returnsSet */
> + VOIDOID,
> + ClanguageId,
> + F_FMGR_C_VALIDATOR,
> + pltemplate->tmplchecker,
> + pltemplate->tmpllibrary,
> + false, /* isAgg */
> + false, /* isWindowFunc */
> + false, /* security_definer */
> + true, /* isStrict */
> + PROVOLATILE_VOLATILE,
> + buildoidvector(funcargtypes, 2),
> + PointerGetDatum(NULL),
> + PointerGetDatum(NULL),
> + PointerGetDatum(NULL),
> + NIL,
> + PointerGetDatum(NULL),
> + 1,
> + 0);
> + }
> + }
> + else
> + checkerOid = InvalidOid;
> +
> /* ok, create it */
> create_proc_lang(stmt->plname, stmt->replace, GetUserId(),
> handlerOid, inlineOid,
> ! valOid, checkerOid, pltemplate->tmpltrusted);
> }
> else
> {
> ***************
> *** 294,303 ****
> else
> valOid = InvalidOid;
>
> /* ok, create it */
> create_proc_lang(stmt->plname, stmt->replace, GetUserId(),
> handlerOid, inlineOid,
> ! valOid, stmt->pltrusted);
> }
> }
>
> --- 335,355 ----
> else
> valOid = InvalidOid;
>
> + /* validate the checker function */
> + if (stmt->plchecker)
> + {
> + funcargtypes[0] = OIDOID;
> + funcargtypes[1] = REGCLASSOID;
> + checkerOid = LookupFuncName(stmt->plchecker, 2, 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 ****
> static void
> create_proc_lang(const char *languageName, bool replace,
> Oid languageOwner, Oid handlerOid, Oid inlineOid,
> ! Oid valOid, bool trusted)
> {
> Relation rel;
> TupleDesc tupDesc;
> --- 359,365 ----
> 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 ****
> --- 389,395 ----
> 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 ****
> --- 476,490 ----
> 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 ****
> --- 540,550 ----
> 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)
> *** ./src/backend/nodes/copyfuncs.c.orig 2011-11-29 19:09:02.000000000 +0100
> --- ./src/backend/nodes/copyfuncs.c 2011-11-29 20:17:01.339172458 +0100
> ***************
> *** 2880,2885 ****
> --- 2880,2898 ----
> return newnode;
> }
>
> + static CheckFunctionStmt *
> + _copyCheckFunctionStmt(CheckFunctionStmt *from)
> + {
> + CheckFunctionStmt *newnode = makeNode(CheckFunctionStmt);
> +
> + COPY_NODE_FIELD(funcname);
> + COPY_NODE_FIELD(args);
> + COPY_STRING_FIELD(trgname);
> + COPY_NODE_FIELD(relation);
> +
> + return newnode;
> + }
> +
> static DoStmt *
> _copyDoStmt(DoStmt *from)
> {
> ***************
> *** 4165,4170 ****
> --- 4178,4186 ----
> case T_AlterFunctionStmt:
> retval = _copyAlterFunctionStmt(from);
> break;
> + case T_CheckFunctionStmt:
> + retval = _copyCheckFunctionStmt(from);
> + break;
> case T_DoStmt:
> retval = _copyDoStmt(from);
> break;
> *** ./src/backend/nodes/equalfuncs.c.orig 2011-11-29 20:19:55.045587471 +0100
> --- ./src/backend/nodes/equalfuncs.c 2011-11-29 20:19:21.850082357 +0100
> ***************
> *** 1292,1297 ****
> --- 1292,1308 ----
> }
>
> static bool
> + _equalCheckFunctionStmt(CheckFunctionStmt *a, CheckFunctionStmt *b)
> + {
> + COMPARE_NODE_FIELD(funcname);
> + COMPARE_NODE_FIELD(args);
> + COMPARE_STRING_FIELD(trgname);
> + COMPARE_NODE_FIELD(relation);
> +
> + return true;
> + }
> +
> + static bool
> _equalDoStmt(DoStmt *a, DoStmt *b)
> {
> COMPARE_NODE_FIELD(args);
> ***************
> *** 2708,2713 ****
> --- 2719,2727 ----
> case T_AlterFunctionStmt:
> retval = _equalAlterFunctionStmt(a, b);
> break;
> + case T_CheckFunctionStmt:
> + retval = _equalCheckFunctionStmt(a, b);
> + break;
> case T_DoStmt:
> retval = _equalDoStmt(a, b);
> break;
> *** ./src/backend/parser/gram.y.orig 2011-11-29 19:09:02.876463248 +0100
> --- ./src/backend/parser/gram.y 2011-11-29 19:21:24.502804769 +0100
> ***************
> *** 227,232 ****
> --- 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 ****
>
> %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
>
> ***************
> *** 700,705 ****
> --- 701,707 ----
> | AlterUserSetStmt
> | AlterUserStmt
> | AnalyzeStmt
> + | CheckFunctionStmt
> | CheckPointStmt
> | ClosePortalStmt
> | ClusterStmt
> ***************
> *** 3174,3184 ****
> 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;
> --- 3176,3187 ----
> 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;
> ***************
> *** 3186,3191 ****
> --- 3189,3195 ----
> n->plhandler = $8;
> n->plinline = $9;
> n->plvalidator = $10;
> + n->plchecker = $11;
> n->pltrusted = $3;
> $$ = (Node *)n;
> }
> ***************
> *** 3220,3225 ****
> --- 3224,3234 ----
> | /*EMPTY*/ { $$ = NIL; }
> ;
>
> + opt_checker:
> + CHECK handler_name { $$ = $2; }
> + | /*EMPTY*/ { $$ = NIL; }
> + ;
> +
> DropPLangStmt:
> DROP opt_procedural LANGUAGE ColId_or_Sconst opt_drop_behavior
> {
> ***************
> *** 6250,6255 ****
> --- 6259,6294 ----
>
> /*****************************************************************************
> *
> + * CHECK FUNCTION funcname(args)
> + * CHECK TRIGGER triggername ON table
> + *
> + *
> + *****************************************************************************/
> +
> +
> + CheckFunctionStmt:
> + CHECK FUNCTION func_name func_args
> + {
> + CheckFunctionStmt *n = makeNode(CheckFunctionStmt);
> + n->funcname = $3;
> + n->args = extractArgTypes($4);
> + n->trgname = NULL;
> + n->relation = NULL;
> + $$ = (Node *) n;
> + }
> + | CHECK TRIGGER name ON qualified_name
> + {
> + CheckFunctionStmt *n = makeNode(CheckFunctionStmt);
> + n->funcname = NULL;
> + n->args = NIL;
> + n->trgname = $3;
> + n->relation = $5;
> + $$ = (Node *) n;
> + }
> + ;
> +
> + /*****************************************************************************
> + *
> * DO <anonymous code block> [ LANGUAGE language ]
> *
> * We use a DefElem list for future extensibility, and to allow flexibility
> *** ./src/backend/tcop/utility.c.orig 2011-11-29 19:20:59.480116945 +0100
> --- ./src/backend/tcop/utility.c 2011-11-29 19:21:24.513804628 +0100
> ***************
> *** 882,887 ****
> --- 882,891 ----
> AlterFunction((AlterFunctionStmt *) parsetree);
> break;
>
> + case T_CheckFunctionStmt:
> + CheckFunction((CheckFunctionStmt *) parsetree);
> + break;
> +
> case T_IndexStmt: /* CREATE INDEX */
> {
> IndexStmt *stmt = (IndexStmt *) parsetree;
> ***************
> *** 2125,2130 ****
> --- 2129,2141 ----
> }
> break;
>
> + case T_CheckFunctionStmt:
> + if (((CheckFunctionStmt *) parsetree)->funcname != NULL)
> + tag = "CHECK FUNCTION";
> + else
> + tag = "CHECK TRIGGER";
> + break;
> +
> default:
> elog(WARNING, "unrecognized node type: %d",
> (int) nodeTag(parsetree));
> ***************
> *** 2565,2570 ****
> --- 2576,2585 ----
> }
> break;
>
> + case T_CheckFunctionStmt:
> + lev = LOGSTMT_ALL;
> + break;
> +
> default:
> elog(WARNING, "unrecognized node type: %d",
> (int) nodeTag(parsetree));
> *** ./src/bin/pg_dump/pg_dump.c.orig 2011-11-29 19:09:03.000000000 +0100
> --- ./src/bin/pg_dump/pg_dump.c 2011-11-29 20:04:31.094156626 +0100
> ***************
> *** 5326,5338 ****
> 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, "
> --- 5326,5351 ----
> 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, "
> ***************
> *** 5409,5414 ****
> --- 5422,5428 ----
> /* 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");
>
> ***************
> *** 5422,5427 ****
> --- 5436,5445 ----
> 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
> ***************
> *** 8597,8602 ****
> --- 8615,8621 ----
> char *qlanname;
> char *lanschema;
> FuncInfo *funcInfo;
> + FuncInfo *checkerInfo = NULL;
> FuncInfo *inlineInfo = NULL;
> FuncInfo *validatorInfo = NULL;
>
> ***************
> *** 8616,8621 ****
> --- 8635,8647 ----
> 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);
> ***************
> *** 8642,8647 ****
> --- 8668,8674 ----
> * 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)));
>
> ***************
> *** 8697,8702 ****
> --- 8724,8739 ----
> 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
> {
> *** ./src/bin/pg_dump/pg_dump.h.orig 2011-11-29 20:05:48.255044631 +0100
> --- ./src/bin/pg_dump/pg_dump.h 2011-11-29 20:05:08.766614345 +0100
> ***************
> *** 387,392 ****
> --- 387,393 ----
> Oid lanplcallfoid;
> Oid laninline;
> Oid lanvalidator;
> + Oid lanchecker;
> char *lanacl;
> char *lanowner; /* name of owner, or empty string */
> } ProcLangInfo;
> *** ./src/bin/psql/tab-complete.c.orig 2011-11-29 19:20:59.482116921 +0100
> --- ./src/bin/psql/tab-complete.c 2011-11-29 19:21:24.516804592 +0100
> ***************
> *** 1,4 ****
> --- 1,5 ----
> /*
> + *
> * psql - the PostgreSQL interactive terminal
> *
> * Copyright (c) 2000-2011, PostgreSQL Global Development Group
> ***************
> *** 727,733 ****
> #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 ****
> --- 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 */
>
> /*
> *** ./src/include/catalog/pg_language.h.orig 2011-11-29 19:20:59.483116909 +0100
> --- ./src/include/catalog/pg_language.h 2011-11-29 19:21:24.518804568 +0100
> ***************
> *** 37,42 ****
> --- 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 */
> aclitem lanacl[1]; /* Access privileges */
> } FormData_pg_language;
>
> ***************
> *** 51,57 ****
> * 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
> --- 52,58 ----
> * 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
> ***************
> *** 59,78 ****
> #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
>
> --- 60,80 ----
> #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
>
> *** ./src/include/catalog/pg_pltemplate.h.orig 2011-11-29 19:20:59.484116897 +0100
> --- ./src/include/catalog/pg_pltemplate.h 2011-11-29 19:21:24.518804568 +0100
> ***************
> *** 36,41 ****
> --- 36,42 ----
> 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 */
> } FormData_pg_pltemplate;
> ***************
> *** 51,65 ****
> * 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
>
>
> /* ----------------
> --- 52,67 ----
> * 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
>
>
> /* ----------------
> ***************
> *** 67,79 ****
> * ----------------
> */
>
> ! 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 */
> --- 69,81 ----
> * ----------------
> */
>
> ! 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 */
> *** ./src/include/commands/defrem.h.orig 2011-11-29 19:20:59.486116871 +0100
> --- ./src/include/commands/defrem.h 2011-11-29 19:21:24.519804556 +0100
> ***************
> *** 62,67 ****
> --- 62,68 ----
> /* commands/functioncmds.c */
> extern void CreateFunction(CreateFunctionStmt *stmt, const char *queryString);
> extern void RemoveFunctionById(Oid funcOid);
> + extern void CheckFunction(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);
> *** ./src/include/nodes/nodes.h.orig 2011-11-29 19:20:59.487116858 +0100
> --- ./src/include/nodes/nodes.h 2011-11-29 19:21:24.521804532 +0100
> ***************
> *** 291,296 ****
> --- 291,297 ----
> T_IndexStmt,
> T_CreateFunctionStmt,
> T_AlterFunctionStmt,
> + T_CheckFunctionStmt,
> T_DoStmt,
> T_RenameStmt,
> T_RuleStmt,
> *** ./src/include/nodes/parsenodes.h.orig 2011-11-29 19:20:59.489116833 +0100
> --- ./src/include/nodes/parsenodes.h 2011-11-29 19:21:24.523804506 +0100
> ***************
> *** 1734,1739 ****
> --- 1734,1740 ----
> 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;
>
> ***************
> *** 2077,2082 ****
> --- 2078,2096 ----
> } 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 */
> + } CheckFunctionStmt;
> +
> + /* ----------------------
> * DO Statement
> *
> * DoStmt is the raw parser output, InlineCodeBlock is the execution-time API
> *** ./src/pl/plpgsql/src/pl_comp.c.orig 2011-11-29 19:09:03.000000000 +0100
> --- ./src/pl/plpgsql/src/pl_comp.c 2011-11-29 19:42:43.058753779 +0100
> ***************
> *** 115,121 ****
> 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 ****
> * 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 ****
> }
>
> /*
> ! * 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 ****
> * 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);
> *** ./src/pl/plpgsql/src/pl_exec.c.orig 2011-11-29 19:09:03.316459122 +0100
> --- ./src/pl/plpgsql/src/pl_exec.c 2011-11-29 19:37:19.000000000 +0100
> ***************
> *** 210,216 ****
> static Portal exec_dynquery_with_params(PLpgSQL_execstate *estate,
> PLpgSQL_expr *dynquery, List *params,
> const char *portalname, int cursorOptions);
> !
>
> /* ----------
> * plpgsql_exec_function Called by the call handler for
> --- 210,228 ----
> static Portal exec_dynquery_with_params(PLpgSQL_execstate *estate,
> PLpgSQL_expr *dynquery, List *params,
> const char *portalname, int cursorOptions);
> ! static void check_row_or_rec(PLpgSQL_execstate *estate, PLpgSQL_row *row, PLpgSQL_rec *rec);
> ! static void check_expr(PLpgSQL_execstate *estate, PLpgSQL_expr *expr);
> ! static void assign_tupdesc_row_or_rec(PLpgSQL_execstate *estate,
> ! PLpgSQL_row *row, PLpgSQL_rec *rec,
> ! TupleDesc tupdesc);
> ! static TupleDesc expr_get_desc(PLpgSQL_execstate *estate,
> ! PLpgSQL_expr *query,
> ! bool use_element_type,
> ! bool expand_record,
> ! bool is_expression);
> ! static void var_init_to_null(PLpgSQL_execstate *estate, int varno);
> ! static void check_stmts(PLpgSQL_execstate *estate, List *stmts);
> ! static void check_stmt(PLpgSQL_execstate *estate, PLpgSQL_stmt *stmt);
>
> /* ----------
> * plpgsql_exec_function Called by the call handler for
> ***************
> *** 6176,6178 ****
> --- 6188,7242 ----
>
> return portal;
> }
> +
> + /*
> + * Following code ensures a CHECK FUNCTION and CHECK TRIGGER statements for PL/pgSQL
> + *
> + */
> +
> + /*
> + * append a CONTEXT to error message
> + */
> + static void
> + check_error_callback(void *arg)
> + {
> + PLpgSQL_execstate *estate = (PLpgSQL_execstate *) arg;
> +
> + if (estate->err_stmt != NULL)
> + {
> + /* translator: last %s is a plpgsql statement type name */
> + errcontext("checking of PL/pgSQL function \"%s\" line %d at %s",
> + estate->func->fn_name,
> + estate->err_stmt->lineno,
> + plpgsql_stmt_typename(estate->err_stmt));
> + }
> + else
> + errcontext("checking of PL/pgSQL function \"%s\"",
> + estate->func->fn_name);
> + }
> +
> + /*
> + * Check function - it prepare variables and starts a prepare plan walker
> + * called by function checker
> + */
> + void
> + plpgsql_check_function(PLpgSQL_function *func, FunctionCallInfo fcinfo)
> + {
> + PLpgSQL_execstate estate;
> + ErrorContextCallback plerrcontext;
> + int i;
> +
> + /* Setup error callback for ereport */
> + plerrcontext.callback = check_error_callback;
> + plerrcontext.arg = &estate;
> + plerrcontext.previous = error_context_stack;
> + error_context_stack = &plerrcontext;
> +
> + /*
> + * Setup the execution state - we would to reuse some exec routines
> + * so we need a estate
> + */
> + plpgsql_estate_setup(&estate, func, (ReturnSetInfo *) fcinfo->resultinfo);
> +
> + /*
> + * Make local execution copies of all the datums
> + */
> + for (i = 0; i < estate.ndatums; i++)
> + 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 (estate.datums[n]->dtype)
> + {
> + case PLPGSQL_DTYPE_VAR:
> + {
> + var_init_to_null(&estate, n);
> + }
> + break;
> +
> + case PLPGSQL_DTYPE_ROW:
> + {
> + PLpgSQL_row *row = (PLpgSQL_row *) estate.datums[n];
> +
> + exec_move_row(&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(&estate, (PLpgSQL_stmt *) func->action);
> +
> + /* Cleanup temporary memory */
> + plpgsql_destroy_econtext(&estate);
> +
> + /* Pop the error context stack */
> + error_context_stack = plerrcontext.previous;
> + }
> +
> + /*
> + * Check trigger - prepare fake environments for testing trigger
> + *
> + */
> + void
> + plpgsql_check_trigger(PLpgSQL_function *func,
> + TriggerData *trigdata)
> + {
> + PLpgSQL_execstate estate;
> + ErrorContextCallback plerrcontext;
> + PLpgSQL_rec *rec_new,
> + *rec_old;
> + int i;
> +
> + /* Setup error callback for ereport */
> + plerrcontext.callback = check_error_callback;
> + plerrcontext.arg = &estate;
> + plerrcontext.previous = error_context_stack;
> + error_context_stack = &plerrcontext;
> +
> + /*
> + * Setup the execution state - we would to reuse some exec routines
> + * so we need a estate
> + */
> + plpgsql_estate_setup(&estate, func, NULL);
> +
> + /*
> + * Make local execution copies of all the datums
> + */
> + for (i = 0; i < estate.ndatums; i++)
> + 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 *) (estate.datums[func->new_varno]);
> + rec_new->freetup = false;
> + rec_new->freetupdesc = false;
> + assign_tupdesc_row_or_rec(&estate, NULL, rec_new, trigdata->tg_relation->rd_att);
> +
> + rec_old = (PLpgSQL_rec *) (estate.datums[func->old_varno]);
> + rec_old->freetup = false;
> + rec_old->freetupdesc = false;
> + assign_tupdesc_row_or_rec(&estate, NULL, rec_old, trigdata->tg_relation->rd_att);
> +
> + /*
> + * Assign the special tg_ variables
> + */
> + var_init_to_null(&estate, func->tg_op_varno);
> + var_init_to_null(&estate, func->tg_name_varno);
> + var_init_to_null(&estate, func->tg_when_varno);
> + var_init_to_null(&estate, func->tg_level_varno);
> + var_init_to_null(&estate, func->tg_relid_varno);
> + var_init_to_null(&estate, func->tg_relname_varno);
> + var_init_to_null(&estate, func->tg_table_name_varno);
> + var_init_to_null(&estate, func->tg_table_schema_varno);
> + var_init_to_null(&estate, func->tg_nargs_varno);
> + var_init_to_null(&estate, func->tg_argv_varno);
> +
> + /*
> + * Now check the toplevel block of statements
> + */
> + check_stmt(&estate, (PLpgSQL_stmt *) func->action);
> +
> + /* Cleanup temporary memory */
> + plpgsql_destroy_econtext(&estate);
> +
> + /* Pop the error context stack */
> + error_context_stack = plerrcontext.previous;
> + }
> +
> + /*
> + * Verify lvalue
> + * It doesn't repeat a checks that are done.
> + * Checks a subscript expressions, verify a validity of record's fields
> + */
> + static void
> + check_target(PLpgSQL_execstate *estate, int varno)
> + {
> + PLpgSQL_datum *target = estate->datums[varno];
> +
> + switch (target->dtype)
> + {
> + case PLPGSQL_DTYPE_VAR:
> + case PLPGSQL_DTYPE_REC:
> + break;
> +
> + case PLPGSQL_DTYPE_ROW:
> + check_row_or_rec(estate, (PLpgSQL_row *) target, NULL);
> + break;
> +
> + case PLPGSQL_DTYPE_RECFIELD:
> + {
> + PLpgSQL_recfield *recfield = (PLpgSQL_recfield *) target;
> + PLpgSQL_rec *rec;
> + int fno;
> +
> + rec = (PLpgSQL_rec *) (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))
> + ereport(ERROR,
> + (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
> + errmsg("record \"%s\" is not assigned to tuple structure",
> + rec->refname)));
> +
> + /*
> + * 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)
> + ereport(ERROR,
> + (errcode(ERRCODE_UNDEFINED_COLUMN),
> + errmsg("record \"%s\" has no field \"%s\"",
> + rec->refname, recfield->fieldname)));
> + }
> + break;
> +
> + case PLPGSQL_DTYPE_ARRAYELEM:
> + {
> + /*
> + * Target is an element of an array
> + */
> + int nsubscripts;
> + Oid arrayelemtypeid;
> + Oid arraytypeid;
> +
> + /*
> + * 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;
> +
> + if (nsubscripts++ >= MAXDIM)
> + ereport(ERROR,
> + (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
> + errmsg("number of array dimensions (%d) exceeds the maximum allowed (%d)",
> + nsubscripts + 1, MAXDIM)));
> +
> + check_expr(estate, arrayelem->subscript);
> +
> + target = 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(estate, target);
> + arraytypeid = getBaseType(arraytypeid);
> +
> + arrayelemtypeid = get_element_type(arraytypeid);
> +
> + if (!OidIsValid(arrayelemtypeid))
> + ereport(ERROR,
> + (errcode(ERRCODE_DATATYPE_MISMATCH),
> + errmsg("subscripted object is not an array")));
> + }
> + break;
> + }
> + }
> +
> + /*
> + * Check composed lvalue
> + * There is nothing to check on rec variables
> + */
> + static void
> + check_row_or_rec(PLpgSQL_execstate *estate, PLpgSQL_row *row, PLpgSQL_rec *rec)
> + {
> + int fnum;
> +
> + /* 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;
> +
> + check_target(estate, row->varnos[fnum]);
> + }
> + }
> + }
> +
> + /*
> + * Generate a prepared plan - this is simplyfied copy from pl_exec.c
> + * Is not necessary to check simple plan
> + */
> + static void
> + prepare_expr(PLpgSQL_execstate *estate,
> + PLpgSQL_expr *expr, int cursorOptions)
> + {
> + SPIPlanPtr plan;
> +
> + /* leave when there are not expression */
> + if (expr == NULL)
> + return;
> +
> + /* leave when plan is created */
> + if (expr->plan != NULL)
> + return;
> +
> + /*
> + * 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 = estate->func;
> +
> + /*
> + * Generate and save the plan
> + */
> + plan = SPI_prepare_params(expr->query,
> + (ParserSetupHook) plpgsql_parser_setup,
> + (void *) expr,
> + cursorOptions);
> + if (plan == NULL)
> + {
> + /* Some SPI errors deserve specific error messages */
> + switch (SPI_result)
> + {
> + case SPI_ERROR_COPY:
> + ereport(ERROR,
> + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
> + errmsg("cannot COPY to/from client in PL/pgSQL")));
> + case SPI_ERROR_TRANSACTION:
> + ereport(ERROR,
> + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
> + errmsg("cannot begin/end transactions in PL/pgSQL"),
> + errhint("Use a BEGIN block with an EXCEPTION clause instead.")));
> + default:
> + elog(ERROR, "SPI_prepare_params failed for \"%s\": %s",
> + expr->query, SPI_result_code_string(SPI_result));
> + }
> + }
> +
> + expr->plan = SPI_saveplan(plan);
> + SPI_freeplan(plan);
> + }
> +
> + /*
> + * Verify a expression
> + */
> + static void
> + check_expr(PLpgSQL_execstate *estate, PLpgSQL_expr *expr)
> + {
> + TupleDesc tupdesc;
> +
> + if (expr != NULL)
> + {
> + prepare_expr(estate, expr, 0);
> + tupdesc = expr_get_desc(estate, expr, false, false, true);
> + ReleaseTupleDesc(tupdesc);
> + }
> + }
> +
> + /*
> + * 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_execstate *estate,
> + PLpgSQL_row *row, PLpgSQL_rec *rec,
> + TupleDesc tupdesc)
> + {
> + bool *nulls;
> + HeapTuple tup;
> +
> + if (tupdesc == NULL)
> + elog(ERROR, "tuple descriptor is empty");
> +
> + /*
> + * row variable has assigned TupleDesc already, so don't be processed
> + * here
> + */
> + if (rec != NULL)
> + {
> + PLpgSQL_rec *target = (PLpgSQL_rec *)(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_execstate *estate, int varno, TupleDesc tupdesc)
> + {
> + PLpgSQL_datum *target = estate->datums[varno];
> +
> + if (target->dtype == PLPGSQL_DTYPE_REC)
> + assign_tupdesc_row_or_rec(estate, NULL, (PLpgSQL_rec *) target, tupdesc);
> + }
> +
> + /*
> + * Returns a tuple descriptor based on existing plan
> + */
> + static TupleDesc
> + expr_get_desc(PLpgSQL_execstate *estate,
> + PLpgSQL_expr *query,
> + bool use_element_type,
> + bool expand_record,
> + bool is_expression)
> + {
> + TupleDesc tupdesc = NULL;
> + CachedPlanSource *plansource = NULL;
> +
> + 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)
> + ereport(ERROR,
> + (errcode(ERRCODE_SYNTAX_ERROR),
> + errmsg_plural("query \"%s\" returned %d column",
> + "query \"%s\" returned %d columns",
> + tupdesc->natts,
> + query->query,
> + tupdesc->natts)));
> +
> + /* check the type of the expression - must be an array */
> + elemtype = get_element_type(tupdesc->attrs[0]->atttypid);
> + if (!OidIsValid(elemtype))
> + ereport(ERROR,
> + (errcode(ERRCODE_DATATYPE_MISMATCH),
> + errmsg("FOREACH expression must yield an array, not type %s",
> + format_type_be(tupdesc->attrs[0]->atttypid))));
> +
> + /* 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
> + elog(ERROR, "cannot to identify real type for record type variable");
> + }
> +
> + if (is_expression && tupdesc->natts != 1)
> + ereport(ERROR,
> + (errcode(ERRCODE_SYNTAX_ERROR),
> + errmsg_plural("query \"%s\" returned %d column",
> + "query \"%s\" returned %d columns",
> + tupdesc->natts,
> + query->query,
> + tupdesc->natts)));
> +
> + /*
> + * 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)
> + elog(ERROR, "function does not return composite type is not possible to identify composite type");
> +
> + FreeTupleDesc(tupdesc);
> + BlessTupleDesc(rd);
> +
> + tupdesc = rd;
> + }
> + }
> + }
> +
> + ReleaseCachedPlan(cplan, true);
> + }
> +
> + return tupdesc;
> + }
> +
> + /*
> + * Ensure check for all statements in list
> + */
> + static void
> + check_stmts(PLpgSQL_execstate *estate, List *stmts)
> + {
> + ListCell *lc;
> +
> + foreach(lc, stmts)
> + {
> + check_stmt(estate, (PLpgSQL_stmt *) lfirst(lc));
> + }
> + }
> +
> + /*
> + * walk over all statements
> + */
> + static void
> + check_stmt(PLpgSQL_execstate *estate, PLpgSQL_stmt *stmt)
> + {
> + TupleDesc tupdesc = NULL;
> + PLpgSQL_function *func;
> + ListCell *l;
> +
> + if (stmt == NULL)
> + return;
> +
> + estate->err_stmt = stmt;
> + func = 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;
> +
> + check_expr(estate, var->default_val);
> + }
> + }
> +
> + check_stmts(estate, stmt_block->body);
> +
> + if (stmt_block->exceptions)
> + {
> + foreach(l, stmt_block->exceptions->exc_list)
> + {
> + check_stmts(estate, ((PLpgSQL_exception *) lfirst(l))->action);
> + }
> + }
> + }
> + break;
> +
> + case PLPGSQL_STMT_ASSIGN:
> + {
> + PLpgSQL_stmt_assign *stmt_assign = (PLpgSQL_stmt_assign *) stmt;
> +
> + /* prepare plan if desn't exist yet */
> + prepare_expr(estate, stmt_assign->expr, 0);
> +
> + tupdesc = expr_get_desc(estate,
> + stmt_assign->expr,
> + false, /* no element type */
> + true, /* expand record */
> + true); /* is expression */
> +
> + /* check target, ensure target can get a result */
> + check_target(estate, stmt_assign->varno);
> +
> + /* assign a tupdesc to record variable */
> + assign_tupdesc_dno(estate, stmt_assign->varno, tupdesc);
> + ReleaseTupleDesc(tupdesc);
> + }
> + break;
> +
> + case PLPGSQL_STMT_IF:
> + {
> + PLpgSQL_stmt_if *stmt_if = (PLpgSQL_stmt_if *) stmt;
> + ListCell *l;
> +
> + check_expr(estate, stmt_if->cond);
> +
> + check_stmts(estate, stmt_if->then_body);
> +
> + foreach(l, stmt_if->elsif_list)
> + {
> + PLpgSQL_if_elsif *elif = (PLpgSQL_if_elsif *) lfirst(l);
> +
> + check_expr(estate, elif->cond);
> + check_stmts(estate, elif->stmts);
> + }
> +
> + check_stmts(estate, stmt_if->else_body);
> + }
> + 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 *) estate->datums[stmt_case->t_varno];
> +
> + /* we need to set hidden variable type */
> + prepare_expr(estate, stmt_case->t_expr, 0);
> +
> + tupdesc = expr_get_desc(estate,
> + stmt_case->t_expr,
> + false, /* no element type */
> + false, /* expand record */
> + true); /* is expression */
> +
> + 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,
> + estate->func->fn_input_collation);
> +
> + ReleaseTupleDesc(tupdesc);
> + }
> +
> + foreach(l, stmt_case->case_when_list)
> + {
> + PLpgSQL_case_when *cwt = (PLpgSQL_case_when *) lfirst(l);
> +
> + check_expr(estate, cwt->expr);
> + check_stmts(estate, cwt->stmts);
> + }
> +
> + check_stmts(estate, stmt_case->else_stmts);
> + }
> + break;
> +
> + case PLPGSQL_STMT_LOOP:
> + check_stmts(estate, ((PLpgSQL_stmt_loop *) stmt)->body);
> + break;
> +
> + case PLPGSQL_STMT_WHILE:
> + {
> + PLpgSQL_stmt_while *stmt_while = (PLpgSQL_stmt_while *) stmt;
> +
> + check_expr(estate, stmt_while->cond);
> + check_stmts(estate, stmt_while->body);
> + }
> + break;
> +
> + case PLPGSQL_STMT_FORI:
> + {
> + PLpgSQL_stmt_fori *stmt_fori = (PLpgSQL_stmt_fori *) stmt;
> +
> + check_expr(estate, stmt_fori->lower);
> + check_expr(estate, stmt_fori->upper);
> + check_expr(estate, stmt_fori->step);
> +
> + check_stmts(estate, stmt_fori->body);
> + }
> + break;
> +
> + case PLPGSQL_STMT_FORS:
> + {
> + PLpgSQL_stmt_fors *stmt_fors = (PLpgSQL_stmt_fors *) stmt;
> +
> + /* we need to set hidden variable type */
> + prepare_expr(estate, stmt_fors->query, 0);
> +
> + tupdesc = expr_get_desc(estate,
> + stmt_fors->query,
> + false, /* no element type */
> + false, /* expand record */
> + false); /* is expression */
> +
> + check_row_or_rec(estate, stmt_fors->row, stmt_fors->rec);
> + assign_tupdesc_row_or_rec(estate, stmt_fors->row, stmt_fors->rec, tupdesc);
> +
> + check_stmts(estate, stmt_fors->body);
> + ReleaseTupleDesc(tupdesc);
> + }
> + break;
> +
> + case PLPGSQL_STMT_FORC:
> + {
> + PLpgSQL_stmt_forc *stmt_forc = (PLpgSQL_stmt_forc *) stmt;
> + PLpgSQL_var *var = (PLpgSQL_var *) func->datums[stmt_forc->curvar];
> +
> + prepare_expr(estate, stmt_forc->argquery, 0);
> +
> + if (var->cursor_explicit_expr != NULL)
> + {
> + prepare_expr(estate, var->cursor_explicit_expr,
> + var->cursor_options);
> +
> + tupdesc = expr_get_desc(estate,
> + var->cursor_explicit_expr,
> + false, /* no element type */
> + false, /* expand record */
> + false); /* is expression */
> +
> + check_row_or_rec(estate, stmt_forc->row, stmt_forc->rec);
> + assign_tupdesc_row_or_rec(estate, stmt_forc->row, stmt_forc->rec, tupdesc);
> + }
> +
> + check_stmts(estate, stmt_forc->body);
> + if (tupdesc != NULL)
> + ReleaseTupleDesc(tupdesc);
> + }
> + break;
> +
> + case PLPGSQL_STMT_DYNFORS:
> + {
> + PLpgSQL_stmt_dynfors * stmt_dynfors = (PLpgSQL_stmt_dynfors *) stmt;
> +
> + if (stmt_dynfors->rec != NULL)
> + elog(ERROR, "cannot determinate a result of dynamic SQL");
> +
> + check_expr(estate, stmt_dynfors->query);
> +
> + foreach(l, stmt_dynfors->params)
> + {
> + check_expr(estate, (PLpgSQL_expr *) lfirst(l));
> + }
> +
> + check_stmts(estate, stmt_dynfors->body);
> + }
> + break;
> +
> + case PLPGSQL_STMT_FOREACH_A:
> + {
> + PLpgSQL_stmt_foreach_a *stmt_foreach_a = (PLpgSQL_stmt_foreach_a *) stmt;
> +
> + prepare_expr(estate, stmt_foreach_a->expr, 0);
> +
> + tupdesc = expr_get_desc(estate,
> + stmt_foreach_a->expr,
> + true, /* no element type */
> + false, /* expand record */
> + true); /* is expression */
> +
> + check_target(estate, stmt_foreach_a->varno);
> + assign_tupdesc_dno(estate, stmt_foreach_a->varno, tupdesc);
> + ReleaseTupleDesc(tupdesc);
> +
> + check_stmts(estate, stmt_foreach_a->body);
> + }
> + break;
> +
> + case PLPGSQL_STMT_EXIT:
> + check_expr(estate, ((PLpgSQL_stmt_exit *) stmt)->cond);
> + break;
> +
> + case PLPGSQL_STMT_PERFORM:
> + prepare_expr(estate, ((PLpgSQL_stmt_perform *) stmt)->expr, 0);
> + break;
> +
> + case PLPGSQL_STMT_RETURN:
> + check_expr(estate, ((PLpgSQL_stmt_return *) stmt)->expr);
> + break;
> +
> + case PLPGSQL_STMT_RETURN_NEXT:
> + check_expr(estate, ((PLpgSQL_stmt_return_next *) stmt)->expr);
> + break;
> +
> + case PLPGSQL_STMT_RETURN_QUERY:
> + {
> + PLpgSQL_stmt_return_query *stmt_rq = (PLpgSQL_stmt_return_query *) stmt;
> +
> + check_expr(estate, stmt_rq->dynquery);
> + prepare_expr(estate, stmt_rq->query, 0);
> +
> + foreach(l, stmt_rq->params)
> + {
> + check_expr(estate, (PLpgSQL_expr *) lfirst(l));
> + }
> + }
> + break;
> +
> + case PLPGSQL_STMT_RAISE:
> + {
> + PLpgSQL_stmt_raise *stmt_raise = (PLpgSQL_stmt_raise *) stmt;
> + ListCell *current_param;
> + char *cp;
> +
> + foreach(l, stmt_raise->params)
> + {
> + check_expr(estate, (PLpgSQL_expr *) lfirst(l));
> + }
> +
> + foreach(l, stmt_raise->options)
> + {
> + check_expr(estate, (PLpgSQL_expr *) lfirst(l));
> + }
> +
> + 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)
> + ereport(ERROR,
> + (errcode(ERRCODE_SYNTAX_ERROR),
> + errmsg("too few parameters specified for RAISE")));
> +
> + current_param = lnext(current_param);
> + }
> + }
> + }
> +
> + if (current_param != NULL)
> + ereport(ERROR,
> + (errcode(ERRCODE_SYNTAX_ERROR),
> + errmsg("too many parameters specified for RAISE")));
> + }
> + break;
> +
> + case PLPGSQL_STMT_EXECSQL:
> + {
> + PLpgSQL_stmt_execsql *stmt_execsql = (PLpgSQL_stmt_execsql *) stmt;
> +
> + prepare_expr(estate, stmt_execsql->sqlstmt, 0);
> + if (stmt_execsql->into)
> + {
> + tupdesc = expr_get_desc(estate,
> + stmt_execsql->sqlstmt,
> + false, /* no element type */
> + false, /* expand record */
> + false); /* is expression */
> +
> + /* check target, ensure target can get a result */
> + check_row_or_rec(estate, stmt_execsql->row, stmt_execsql->rec);
> + assign_tupdesc_row_or_rec(estate, stmt_execsql->row, stmt_execsql->rec, tupdesc);
> + ReleaseTupleDesc(tupdesc);
> + }
> + }
> + break;
> +
> + case PLPGSQL_STMT_DYNEXECUTE:
> + {
> + PLpgSQL_stmt_dynexecute *stmt_dynexecute = (PLpgSQL_stmt_dynexecute *) stmt;
> +
> + check_expr(estate, stmt_dynexecute->query);
> +
> + foreach(l, stmt_dynexecute->params)
> + {
> + check_expr(estate, (PLpgSQL_expr *) lfirst(l));
> + }
> +
> + if (stmt_dynexecute->into)
> + {
> + if (stmt_dynexecute->rec != NULL)
> + elog(ERROR, "cannot determinate a result of dynamic SQL");
> +
> + check_row_or_rec(estate, stmt_dynexecute->row, stmt_dynexecute->rec);
> + }
> + }
> + 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)
> + prepare_expr(estate, var->cursor_explicit_expr,
> + var->cursor_options);
> +
> + prepare_expr(estate, stmt_open->query, 0);
> + prepare_expr(estate, stmt_open->argquery, 0);
> + check_expr(estate, stmt_open->dynquery);
> +
> + foreach(l, stmt_open->params)
> + {
> + check_expr(estate, (PLpgSQL_expr *) lfirst(l));
> + }
> + }
> + 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);
> +
> + check_target(estate, diag_item->target);
> + }
> + }
> + break;
> +
> + case PLPGSQL_STMT_FETCH:
> + {
> + PLpgSQL_stmt_fetch *stmt_fetch = (PLpgSQL_stmt_fetch *) stmt;
> + PLpgSQL_var *var = (PLpgSQL_var *)(estate->datums[stmt_fetch->curvar]);
> +
> + if (var != NULL && var->cursor_explicit_expr != NULL)
> + {
> + prepare_expr(estate, var->cursor_explicit_expr,
> + var->cursor_options);
> + tupdesc = expr_get_desc(estate,
> + var->cursor_explicit_expr,
> + false, /* no element type */
> + false, /* expand record */
> + false); /* is expression */
> + check_row_or_rec(estate, stmt_fetch->row, stmt_fetch->rec);
> + assign_tupdesc_row_or_rec(estate, stmt_fetch->row, stmt_fetch->rec, tupdesc);
> + ReleaseTupleDesc(tupdesc);
> + }
> + }
> + break;
> +
> + case PLPGSQL_STMT_CLOSE:
> + break;
> +
> + default:
> + elog(ERROR, "unrecognized cmd_type: %d", stmt->cmd_type);
> + return; /* be compiler quite */
> + }
> + }
> +
> + /*
> + * Initialize variable to NULL
> + */
> + static void
> + var_init_to_null(PLpgSQL_execstate *estate, int varno)
> + {
> + PLpgSQL_var *var = (PLpgSQL_var *) estate->datums[varno];
> + var->value = (Datum) 0;
> + var->isnull = true;
> + var->freeval = false;
> + }
> *** ./src/pl/plpgsql/src/pl_handler.c.orig 2011-11-29 19:20:59.494116771 +0100
> --- ./src/pl/plpgsql/src/pl_handler.c 2011-11-29 19:21:24.529804431 +0100
> ***************
> *** 312,314 ****
> --- 312,452 ----
>
> 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);
> + 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;
> +
> + /* we don't need to repair a check done by validator */
> +
> + tuple = SearchSysCache1(PROCOID, ObjectIdGetDatum(funcoid));
> + if (!HeapTupleIsValid(tuple))
> + elog(ERROR, "cache lookup failed for function %u", funcoid);
> + proc = (Form_pg_proc) GETSTRUCT(tuple);
> +
> + 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")));
> + }
> + }
> +
> + /*
> + * 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 prepare plans */
> + PG_TRY();
> + {
> + if (!istrigger)
> + plpgsql_check_function(function, &fake_fcinfo);
> + else
> + plpgsql_check_trigger(function, &trigdata);
> + }
> + 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--;
> +
> + /*
> + * 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);
> +
> + PG_RETURN_VOID();
> + }
> *** ./src/pl/plpgsql/src/plpgsql.h.orig 2011-11-29 19:20:59.500116698 +0100
> --- ./src/pl/plpgsql/src/plpgsql.h 2011-11-29 20:22:19.423516596 +0100
> ***************
> *** 902,907 ****
> --- 902,908 ----
> 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 ****
> --- 912,918 ----
> 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 ****
> --- 930,939 ----
> 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);
> + extern void plpgsql_check_trigger(PLpgSQL_function *func,
> + TriggerData *trigdata);
>
> /* ----------
> * Functions for namespace handling in pl_funcs.c
> *** ./src/pl/plpgsql/src/plpgsql--unpackaged--1.0.sql.orig 2011-11-29 19:20:59.502116672 +0100
> --- ./src/pl/plpgsql/src/plpgsql--unpackaged--1.0.sql 2011-11-29 19:21:24.533804381 +0100
> ***************
> *** 5,7 ****
> --- 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);
> *** ./src/test/regress/expected/plpgsql.out.orig 2011-11-29 19:20:59.505116634 +0100
> --- ./src/test/regress/expected/plpgsql.out 2011-11-29 19:21:24.536804342 +0100
> ***************
> *** 302,307 ****
> --- 302,310 ----
> ' 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
> + check trigger tg_hslot_biu on HSlot;
> + NOTICE: checking function "tg_hslot_biu()"
> -- ************************************************************
> -- * BEFORE DELETE on HSlot
> -- * - prevent from manual manipulation
> ***************
> *** 635,640 ****
> --- 638,645 ----
> raise exception ''illegal backlink beginning with %'', mytype;
> end;
> ' language plpgsql;
> + -- check function should not fail
> + check function tg_backlink_set(bpchar, bpchar);
> -- ************************************************************
> -- * Support function to clear out the backlink field if
> -- * it still points to specific slot
> ***************
> *** 2802,2807 ****
> --- 2807,2840 ----
>
> (1 row)
>
> + -- check function should not fail
> + check function for_vect();
> + -- 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
> ***************
> *** 3283,3288 ****
> --- 3316,3323 ----
> return;
> end;
> $$ language plpgsql;
> + -- check function should not fail
> + check function forc01();
> select forc01();
> NOTICE: 5 from c
> NOTICE: 6 from c
> ***************
> *** 3716,3721 ****
> --- 3751,3758 ----
> end case;
> end;
> $$ language plpgsql immutable;
> + -- check function should not fail
> + check function case_test(bigint);
> select case_test(1);
> case_test
> -----------
> ***************
> *** 4571,4573 ****
> --- 4608,4942 ----
> 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
> + --
> + create table t1(a int, b int);
> + create function f1()
> + returns void as $$
> + begin
> + if false then
> + update t1 set c = 30;
> + end if;
> + end;
> + $$ language plpgsql;
> + select f1();
> + f1
> + ----
> +
> + (1 row)
> +
> + check function f1();
> + ERROR: column "c" of relation "t1" does not exist
> + LINE 1: update t1 set c = 30
> + ^
> + QUERY: update t1 set c = 30
> + CONTEXT: checking of PL/pgSQL function "f1" line 4 at SQL statement
> + 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)
> +
> + check function f1();
> + ERROR: record "r" has no field "c"
> + CONTEXT: SQL statement "SELECT r.c"
> + checking of PL/pgSQL function "f1" line 6 at RAISE
> + 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)
> +
> + check function f1();
> + ERROR: record "r" has no field "c"
> + CONTEXT: SQL statement "SELECT r.c"
> + checking of PL/pgSQL function "f1" line 6 at RAISE
> + 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)
> +
> + check function f1();
> + ERROR: record "r" has no field "c"
> + CONTEXT: checking of PL/pgSQL function "f1" line 6 at assignment
> + 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)
> +
> + check function f1();
> + ERROR: column "a" does not exist
> + LINE 1: SELECT a + b
> + ^
> + QUERY: SELECT a + b
> + CONTEXT: checking of PL/pgSQL function "f1" line 5 at assignment
> + 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)
> +
> + check function f1();
> + ERROR: too many parameters specified for RAISE
> + CONTEXT: checking of PL/pgSQL function "f1" line 4 at RAISE
> + 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)
> +
> + check function f1();
> + ERROR: too few parameters specified for RAISE
> + CONTEXT: checking of PL/pgSQL function "f1" line 4 at RAISE
> + 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)
> +
> + check function f1();
> + ERROR: column "c" does not exist
> + LINE 1: SELECT c+10
> + ^
> + QUERY: SELECT c+10
> + CONTEXT: checking of PL/pgSQL function "f1" line 5 at assignment
> + 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;
> + select f1();
> + f1
> + ----
> +
> + (1 row)
> +
> + check function f1();
> + ERROR: subscripted object is not an array
> + CONTEXT: checking of PL/pgSQL function "f1" line 5 at assignment
> + 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)
> +
> + check function f1();
> + ERROR: record "_exception" has no field "hint"
> + CONTEXT: checking of PL/pgSQL function "f1" line 7 at GET DIAGNOSTICS
> + 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);
> + check trigger t1_f1 on t1;
> + NOTICE: checking function "f1_trg()"
> + ERROR: record "new" has no field "c"
> + CONTEXT: SQL statement "SELECT new.c"
> + checking of PL/pgSQL function "f1_trg" line 5 at RAISE
> + 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
> + check trigger t1_f1 on t1;
> + NOTICE: checking function "f1_trg()"
> + ERROR: record "new" has no field "c"
> + CONTEXT: checking of PL/pgSQL function "f1_trg" line 5 at assignment
> + -- 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
> + check trigger t1_f1 on t1;
> + NOTICE: checking function "f1_trg()"
> + -- ok
> + insert into t1 values(6,30);
> + drop table t1;
> + drop type _exception_type;
> + drop function f1_trg();
> *** ./src/test/regress/sql/plpgsql.sql.orig 2011-11-29 19:20:59.508116598 +0100
> --- ./src/test/regress/sql/plpgsql.sql 2011-11-29 19:21:24.538804318 +0100
> ***************
> *** 366,371 ****
> --- 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
> + check trigger tg_hslot_biu on HSlot;
>
> -- ************************************************************
> -- * BEFORE DELETE on HSlot
> ***************
> *** 747,752 ****
> --- 749,757 ----
> end;
> ' language plpgsql;
>
> + -- check function should not fail
> + check function tg_backlink_set(bpchar, bpchar);
> +
>
> -- ************************************************************
> -- * Support function to clear out the backlink field if
> ***************
> *** 2335,2340 ****
> --- 2340,2352 ----
>
> select for_vect();
>
> + -- check function should not fail
> + check function for_vect();
> +
> + -- 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
> ***************
> *** 2714,2719 ****
> --- 2726,2734 ----
> end;
> $$ language plpgsql;
>
> + -- check function should not fail
> + check function forc01();
> +
> select forc01();
>
> -- try updating the cursor's current row
> ***************
> *** 3048,3053 ****
> --- 3063,3071 ----
> end;
> $$ language plpgsql immutable;
>
> + -- check function should not fail
> + check function case_test(bigint);
> +
> select case_test(1);
> select case_test(2);
> select case_test(3);
> ***************
> *** 3600,3602 ****
> --- 3618,3862 ----
>
> drop function arrayassign1();
> drop function testoa(x1 int, x2 int, x3 int);
> +
> + --
> + -- check function statement tests
> + --
> +
> + create table t1(a int, b int);
> +
> + create function f1()
> + returns void as $$
> + begin
> + if false then
> + update t1 set c = 30;
> + end if;
> + end;
> + $$ language plpgsql;
> +
> + select f1();
> + 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();
> + 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();
> + 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();
> + 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();
> + 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();
> + 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();
> + 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();
> + 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;
> +
> + select f1();
> + 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();
> + 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);
> + 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
> + 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
> + check trigger t1_f1 on t1;
> +
> + -- ok
> + insert into t1 values(6,30);
> +
> + drop table t1;
> + drop type _exception_type;
> +
> + drop function f1_trg();
> +
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +