Re: review: CHECK FUNCTION statement - Mailing list pgsql-hackers
| From | Pavel Stehule |
|---|---|
| Subject | Re: review: CHECK FUNCTION statement |
| Date | |
| Msg-id | CAFj8pRDtrsm2dgxtC=MvK6NSOGiQtzZpYQPr1-Z8mh0R+g9Trw@mail.gmail.com Whole thread Raw |
| In response to | Re: review: CHECK FUNCTION statement (Bruce Momjian <bruce@momjian.us>) |
| List | pgsql-hackers |
Hello it is in open commitfest http://archives.postgresql.org/message-id/CAFj8pRAYVTQYCL8_NF_hDQjc0m+JBvbwR6E_ZJ0SJfkKQ9m2kA@mail.gmail.com Regards Pavel 2012/8/17 Bruce Momjian <bruce@momjian.us>: > > 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 maximumallowed (%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 toidentify 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. +
pgsql-hackers by date: