*** a/doc/src/sgml/plpgsql.sgml --- b/doc/src/sgml/plpgsql.sgml *************** *** 4488,4493 **** $$ LANGUAGE plpgsql; --- 4488,4517 ---- + + Checking of embedded SQL + + The SQL statements inside PL/pgSQL functions are + checked by validator for semantic errors. These errors + can be found by plpgsql_check_function: + + postgres=# select * from plpgsql_check_function('fx(int)'); + -[ RECORD 1 ]-------------------------------------- + functionid | fx + lineno | 3 + statement | RETURN + sqlstate | 42703 + message | column "b" does not exist + detail | [null] + hint | [null] + level | error + position | 32 + query | SELECT (SELECT a FROM t1 WHERE b < $1) + context | [null] + + + + *** a/src/pl/plpgsql/src/Makefile --- b/src/pl/plpgsql/src/Makefile *************** *** 17,25 **** override CPPFLAGS := -I. -I$(srcdir) $(CPPFLAGS) SHLIB_LINK = $(filter -lintl, $(LIBS)) rpath = ! OBJS = pl_gram.o pl_handler.o pl_comp.o pl_exec.o pl_funcs.o pl_scanner.o - DATA = plpgsql.control plpgsql--1.0.sql plpgsql--unpackaged--1.0.sql all: all-lib --- 17,26 ---- SHLIB_LINK = $(filter -lintl, $(LIBS)) rpath = ! OBJS = pl_gram.o pl_handler.o pl_comp.o pl_exec.o pl_funcs.o pl_scanner.o pl_check.o ! ! DATA = plpgsql.control plpgsql--1.0.sql plpgsql--1.1.sql plpgsql--unpackaged--1.0.sql plpgsql--1.0--1.1.sql all: all-lib *************** *** 52,58 **** uninstall-headers: # Force these dependencies to be known even without dependency info built: ! pl_gram.o pl_handler.o pl_comp.o pl_exec.o pl_funcs.o pl_scanner.o: plpgsql.h pl_gram.h plerrcodes.h # See notes in src/backend/parser/Makefile about the following two rules pl_gram.h: pl_gram.c ; --- 53,59 ---- # Force these dependencies to be known even without dependency info built: ! pl_gram.o pl_handler.o pl_comp.o pl_exec.o pl_funcs.o pl_scanner.o pl_check.o : plpgsql.h pl_gram.h plerrcodes.h # See notes in src/backend/parser/Makefile about the following two rules pl_gram.h: pl_gram.c ; *** /dev/null --- b/src/pl/plpgsql/src/pl_check.c *************** *** 0 **** --- 1,1672 ---- + /*------------------------------------------------------------------------- + * + * pl_checker.c - Checker for the PL/pgSQL + * procedural language + * + * Portions Copyright (c) 1996-2012, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * + * + * IDENTIFICATION + * src/pl/plpgsql/src/pl_check.c + * + *------------------------------------------------------------------------- + */ + + #include "plpgsql.h" + + #include "funcapi.h" + #include "miscadmin.h" + + #include "access/htup_details.h" + #include "catalog/pg_proc.h" + #include "catalog/pg_type.h" + #include "executor/spi_priv.h" + #include "mb/pg_wchar.h" + #include "utils/builtins.h" + #include "utils/guc.h" + #include "utils/lsyscache.h" + #include "utils/syscache.h" + #include "utils/typcache.h" + #include "utils/rel.h" + #include "utils/xml.h" + + #define Natts_result 11 + + #define Anum_result_functionid 0 + #define Anum_result_lineno 1 + #define Anum_result_statement 2 + #define Anum_result_sqlstate 3 + #define Anum_result_message 4 + #define Anum_result_detail 5 + #define Anum_result_hint 6 + #define Anum_result_level 7 + #define Anum_result_position 8 + #define Anum_result_query 9 + #define Anum_result_context 10 + + + #define SET_RESULT_NULL(anum) \ + do { \ + values[(anum)] = (Datum) 0; \ + nulls[(anum)] = true; \ + } while (0) + #define SET_RESULT(anum, value) \ + do { \ + values[(anum)] = (value); \ + nulls[(anum)] = false; \ + } while(0) + #define SET_RESULT_TEXT(anum, str) SET_RESULT((anum), CStringGetTextDatum((str))) + #define SET_RESULT_INT32(anum, ival) SET_RESULT((anum), Int32GetDatum((ival))) + #define SET_RESULT_OID(anum, oid) SET_RESULT((anum), ObjectIdGetDatum((oid))) + + + static void check_row_or_rec(PLpgSQL_checkstate *cstate, PLpgSQL_row *row, PLpgSQL_rec *rec); + static void check_expr(PLpgSQL_checkstate *cstate, PLpgSQL_expr *expr); + static void assign_tupdesc_row_or_rec(PLpgSQL_checkstate *cstate, + PLpgSQL_row *row, PLpgSQL_rec *rec, + TupleDesc tupdesc); + static void assign_tupdesc_dno(PLpgSQL_checkstate *cstate, int varno, TupleDesc tupdesc); + static TupleDesc expr_get_desc(PLpgSQL_checkstate *cstate, + PLpgSQL_expr *query, + bool use_element_type, + bool expand_record, + bool is_expression); + static void init_datum(PLpgSQL_checkstate *cstate, int varno); + static void check_stmts(PLpgSQL_checkstate *cstate, List *stmts); + static void check_stmt(PLpgSQL_checkstate *cstate, PLpgSQL_stmt *stmt); + static void prepare_expr(PLpgSQL_checkstate *cstate, + PLpgSQL_expr *expr, int cursorOptions); + static void check_assignment(PLpgSQL_checkstate *cstate, PLpgSQL_expr *expr, + PLpgSQL_rec *targetrec, PLpgSQL_row *targetrow, + int targetdno); + static void check_element_assignment(PLpgSQL_checkstate *cstate, PLpgSQL_expr *expr, + PLpgSQL_rec *targetrec, PLpgSQL_row *targetrow, + int targetdno); + static void check_assignment_guts(PLpgSQL_checkstate *cstate, PLpgSQL_expr *expr, + PLpgSQL_rec *targetrec, PLpgSQL_row *targetrow, + int targetdno, bool use_element_type, bool is_expression); + static void checker_error_edata(PLpgSQL_checkstate *cstate, ErrorData *edata); + static void checker_error(PLpgSQL_checkstate *cstate, + int sqlerrcode, int lineno, + const char *message, const char *detail, + const char *hint, const char *level, + int position, const char *query, + const char *context); + static void cstate_setup(PLpgSQL_checkstate *cstate, + Oid fn_oid, TupleDesc tupdesc, Tuplestorestate *tupstore, + bool fatal_errors, bool perform_warnings); + static void function_check(PLpgSQL_function *func, FunctionCallInfo fcinfo, + PLpgSQL_execstate *estate, PLpgSQL_checkstate *cstate); + static void trigger_check(PLpgSQL_function *func, + Node *trigdata, + PLpgSQL_execstate *estate, PLpgSQL_checkstate *cstate); + static int load_configuration(HeapTuple procTuple, bool *reload_config); + + /* + * Top checker function + * + */ + void + plpgsql_function_check(HeapTuple procTuple, Oid relid, PLpgSQL_trigtype trigtype, + TupleDesc tupdesc, + Tuplestorestate *tupstore, + bool fatal_errors, bool perform_warnings) + { + PLpgSQL_checkstate cstate; + PLpgSQL_function *volatile function = NULL; + int save_nestlevel = 0; + bool reload_config; + Oid funcoid; + FunctionCallInfoData fake_fcinfo; + FmgrInfo flinfo; + TriggerData trigdata; + EventTriggerData etrigdata; + int rc; + ResourceOwner oldowner; + MemoryContext oldCxt; + PLpgSQL_execstate *cur_estate = NULL; + int numargs; + Oid *argtypes; + char **argnames; + char *argmodes; + int i; + + funcoid = HeapTupleGetOid(procTuple); + + /* + * Connect to SPI manager + */ + if ((rc = SPI_connect()) != SPI_OK_CONNECT) + elog(ERROR, "SPI_connect failed: %s", SPI_result_code_string(rc)); + + plpgsql_setup_fake_fcinfo(&flinfo, &fake_fcinfo, + &trigdata, &etrigdata, + funcoid, + trigtype); + cstate_setup(&cstate, funcoid, tupdesc, tupstore, fatal_errors, perform_warnings); + + if (OidIsValid(relid)) + trigdata.tg_relation = relation_open(relid, AccessShareLock); + + numargs = get_func_arg_info(procTuple, + &argtypes, &argnames, &argmodes); + + if (argnames != NULL) + { + for (i = 0; i < numargs; i++) + { + if (argnames[i][0] != '\0') + cstate.argnames = lappend(cstate.argnames, argnames[i]); + } + } + oldCxt = CurrentMemoryContext; + oldowner = CurrentResourceOwner; + + PG_TRY(); + { + PLpgSQL_execstate estate; + + BeginInternalSubTransaction(NULL); + MemoryContextSwitchTo(oldCxt); + + save_nestlevel = load_configuration(procTuple, &reload_config); + + /* Get a compiled function */ + function = plpgsql_compile(&fake_fcinfo, false); + + /* recheck trigtype */ + Assert(function->fn_is_trigger == trigtype); + + plpgsql_estate_setup(&estate, function, (ReturnSetInfo *) fake_fcinfo.resultinfo); + cstate.estate = &estate; + + /* Must save and restore prior value of cur_estate */ + cur_estate = function->cur_estate; + + /* + * Mark the function as busy, so it can't be deleted from under us + */ + function->use_count++; + + /* Create a fake runtime environment and process check */ + switch (trigtype) + { + case PLPGSQL_DML_TRIGGER: + trigger_check(function, (Node *) &trigdata, &estate, &cstate); + break; + + case PLPGSQL_EVENT_TRIGGER: + trigger_check(function, (Node *) &etrigdata, &estate, &cstate); + break; + + case PLPGSQL_NOT_TRIGGER: + function_check(function, &fake_fcinfo, &estate, &cstate); + break; + } + + /* + * reload back a GUC. XXX: isn't this done automatically by subxact + * rollback? + */ + if (reload_config) + AtEOXact_GUC(true, save_nestlevel); + + plpgsql_destroy_econtext(&estate); + + RollbackAndReleaseCurrentSubTransaction(); + MemoryContextSwitchTo(oldCxt); + CurrentResourceOwner = oldowner; + + SPI_restore_connection(); + } + PG_CATCH(); + { + ErrorData *edata; + + MemoryContextSwitchTo(oldCxt); + edata = CopyErrorData(); + FlushErrorState(); + + RollbackAndReleaseCurrentSubTransaction(); + MemoryContextSwitchTo(oldCxt); + CurrentResourceOwner = oldowner; + + checker_error_edata(&cstate, edata); + MemoryContextSwitchTo(oldCxt); + /* reconnect spi */ + SPI_restore_connection(); + } + PG_END_TRY(); + + if (function) + { + 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); + } + if (OidIsValid(relid)) + relation_close(trigdata.tg_relation, AccessShareLock); + + /* + * Disconnect from SPI manager + */ + if ((rc = SPI_finish()) != SPI_OK_FINISH) + elog(ERROR, "SPI_finish failed: %s", SPI_result_code_string(rc)); + } + + /* + * Check function - it prepare variables and starts a prepare plan walker + */ + static void + function_check(PLpgSQL_function *func, FunctionCallInfo fcinfo, + PLpgSQL_execstate *estate, PLpgSQL_checkstate *cstate) + { + int i; + + /* + * Make local execution copies of all the datums + */ + for (i = 0; i < cstate->estate->ndatums; i++) + cstate->estate->datums[i] = copy_plpgsql_datum(func->datums[i]); + + /* + * Store the actual call argument values (fake) into the appropriate + * variables + */ + for (i = 0; i < func->fn_nargs; i++) + { + init_datum(cstate, func->fn_argvarnos[i]); + } + + /* + * Now check the toplevel block of statements + */ + check_stmt(cstate, (PLpgSQL_stmt *) func->action); + } + + /* + * Check trigger - prepare fake environments for testing trigger + * + */ + static void + trigger_check(PLpgSQL_function *func, Node *tdata, + PLpgSQL_execstate *estate, PLpgSQL_checkstate *cstate) + { + PLpgSQL_rec *rec_new, + *rec_old; + int i; + + /* + * Make local execution copies of all the datums + */ + for (i = 0; i < cstate->estate->ndatums; i++) + cstate->estate->datums[i] = copy_plpgsql_datum(func->datums[i]); + + if (IsA(tdata, TriggerData)) + { + TriggerData *trigdata = (TriggerData *) tdata; + + /* + * Put the OLD and NEW tuples into record variables + * + * We make the tupdescs available in both records even though only one + * may have a value. This allows parsing of record references to + * succeed in functions that are used for multiple trigger types. For + * example, we might have a test like "if (TG_OP = 'INSERT' and + * NEW.foo = 'xyz')", which should parse regardless of the current + * trigger type. + */ + rec_new = (PLpgSQL_rec *) (cstate->estate->datums[func->new_varno]); + rec_new->freetup = false; + rec_new->freetupdesc = false; + assign_tupdesc_row_or_rec(cstate, NULL, rec_new, trigdata->tg_relation->rd_att); + + rec_old = (PLpgSQL_rec *) (cstate->estate->datums[func->old_varno]); + rec_old->freetup = false; + rec_old->freetupdesc = false; + assign_tupdesc_row_or_rec(cstate, NULL, rec_old, trigdata->tg_relation->rd_att); + + /* + * Assign the special tg_ variables + */ + init_datum(cstate, func->tg_op_varno); + init_datum(cstate, func->tg_name_varno); + init_datum(cstate, func->tg_when_varno); + init_datum(cstate, func->tg_level_varno); + init_datum(cstate, func->tg_relid_varno); + init_datum(cstate, func->tg_relname_varno); + init_datum(cstate, func->tg_table_name_varno); + init_datum(cstate, func->tg_table_schema_varno); + init_datum(cstate, func->tg_nargs_varno); + init_datum(cstate, func->tg_argv_varno); + } + else if (IsA(tdata, EventTriggerData)) + { + init_datum(cstate, func->tg_event_varno); + init_datum(cstate, func->tg_tag_varno); + } + else + elog(ERROR, "unexpected environment"); + + /* + * Now check the toplevel block of statements + */ + check_stmt(cstate, (PLpgSQL_stmt *) func->action); + } + + /* + * Verify lvalue It doesn't repeat a checks that are done. Checks a subscript + * expressions, verify a validity of record's fields, Returns true, when + * target is valid + */ + static void + check_target(PLpgSQL_checkstate *cstate, int varno) + { + PLpgSQL_datum *target = cstate->estate->datums[varno]; + + switch (target->dtype) + { + case PLPGSQL_DTYPE_VAR: + case PLPGSQL_DTYPE_REC: + /* nothing to check */ + break; + + case PLPGSQL_DTYPE_ROW: + check_row_or_rec(cstate, (PLpgSQL_row *) target, NULL); + break; + + case PLPGSQL_DTYPE_RECFIELD: + { + PLpgSQL_recfield *recfield = (PLpgSQL_recfield *) target; + PLpgSQL_rec *rec; + int fno; + + rec = (PLpgSQL_rec *) (cstate->estate->datums[recfield->recparentno]); + + /* + * Check that there is already a tuple in the record. We need + * that because records don't have any predefined field + * structure. + */ + if (!HeapTupleIsValid(rec->tup)) + ereport(ERROR, + (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("record \"%s\" is not assigned to tuple structure", + rec->refname))); + + /* + * Get the number of the records field to change and the + * number of attributes in the tuple. Note: disallow system + * column names because the code below won't cope. + */ + fno = SPI_fnumber(rec->tupdesc, recfield->fieldname); + if (fno <= 0) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_COLUMN), + errmsg("record \"%s\" has no field \"%s\"", + rec->refname, recfield->fieldname))); + } + break; + + case PLPGSQL_DTYPE_ARRAYELEM: + { + /* + * Target is an element of an array + */ + int nsubscripts; + Oid arrayelemtypeid; + Oid arraytypeid; + + /* + * To handle constructs like x[1][2] := something, we have to + * be prepared to deal with a chain of arrayelem datums. Chase + * back to find the base array datum, and save the subscript + * expressions as we go. (We are scanning right to left here, + * but want to evaluate the subscripts left-to-right to + * minimize surprises.) + */ + nsubscripts = 0; + do + { + PLpgSQL_arrayelem *arrayelem = (PLpgSQL_arrayelem *) target; + + if (nsubscripts++ >= MAXDIM) + ereport(ERROR, + (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED), + errmsg("number of array dimensions (%d) exceeds the maximum allowed (%d)", + nsubscripts + 1, MAXDIM))); + + /* Validate expression. */ + /* XXX is_expression */ + check_expr(cstate, arrayelem->subscript); + + target = cstate->estate->datums[arrayelem->arrayparentno]; + } while (target->dtype == PLPGSQL_DTYPE_ARRAYELEM); + + /* + * If target is domain over array, reduce to base type + */ + arraytypeid = exec_get_datum_type(cstate->estate, target); + arraytypeid = getBaseType(arraytypeid); + + arrayelemtypeid = get_element_type(arraytypeid); + + if (!OidIsValid(arrayelemtypeid)) + 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_checkstate *cstate, 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(cstate, row->varnos[fnum]); + } + } + } + + /* + * Generate a prepared plan - this is simplified copy from pl_exec.c Is not + * necessary to check simple plan, returns true, when expression is + * succesfully prepared. + */ + static void + prepare_expr(PLpgSQL_checkstate *cstate, + PLpgSQL_expr *expr, int cursorOptions) + { + SPIPlanPtr plan; + + if (expr->plan != NULL) + return; /* already checked */ + + /* + * The grammar can't conveniently set expr->func while building the parse + * tree, so make sure it's set before parser hooks need it. + */ + expr->func = cstate->estate->func; + + /* + * 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"))); + break; + + 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."))); + break; + + 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_checkstate *cstate, PLpgSQL_expr *expr) + { + if (expr) + check_assignment_guts(cstate, expr, NULL, NULL, -1, false, false); + } + + /* + * Verify an assignment of 'expr' to 'target' + */ + static void + check_assignment(PLpgSQL_checkstate *cstate, PLpgSQL_expr *expr, + PLpgSQL_rec *targetrec, PLpgSQL_row *targetrow, + int targetdno) + { + bool is_expression = (targetrec == NULL && targetrow == NULL); + + check_assignment_guts(cstate, expr, targetrec, targetrow, targetdno, false, + is_expression); + } + + static void + check_element_assignment(PLpgSQL_checkstate *cstate, PLpgSQL_expr *expr, + PLpgSQL_rec *targetrec, PLpgSQL_row *targetrow, + int targetdno) + { + bool is_expression = (targetrec == NULL && targetrow == NULL); + + check_assignment_guts(cstate, expr, targetrec, targetrow, targetdno, true, + is_expression); + } + + static void + check_assignment_guts(PLpgSQL_checkstate *cstate, PLpgSQL_expr *expr, + PLpgSQL_rec *targetrec, PLpgSQL_row *targetrow, + int targetdno, bool use_element_type, bool is_expression) + { + ResourceOwner oldowner; + MemoryContext oldCxt = CurrentMemoryContext; + TupleDesc tupdesc; + + oldowner = CurrentResourceOwner; + BeginInternalSubTransaction(NULL); + MemoryContextSwitchTo(oldCxt); + + PG_TRY(); + { + prepare_expr(cstate, expr, 0); + tupdesc = expr_get_desc(cstate, expr, use_element_type, true, is_expression); + if (tupdesc) + { + if (targetrow != NULL || targetrec != NULL) + assign_tupdesc_row_or_rec(cstate, targetrow, targetrec, tupdesc); + if (targetdno != -1) + assign_tupdesc_dno(cstate, targetdno, tupdesc); + + if (targetrow) + { + if (targetrow->nfields > tupdesc->natts) + checker_error(cstate, + 0, 0, + "too few attributies for target variables", + "There are more target variables than output columns in query.", + "Check target variables in SELECT INTO statement.", + "warning", + 0, NULL, NULL); + else if (targetrow->nfields < tupdesc->natts) + checker_error(cstate, + 0, 0, + "too many attributies for target variables", + "There are less target variables than output columns in query.", + "Check target variables in SELECT INTO statement", + "warning", + 0, NULL, NULL); + } + ReleaseTupleDesc(tupdesc); + } + RollbackAndReleaseCurrentSubTransaction(); + MemoryContextSwitchTo(oldCxt); + CurrentResourceOwner = oldowner; + + SPI_restore_connection(); + } + PG_CATCH(); + { + ErrorData *edata; + + MemoryContextSwitchTo(oldCxt); + edata = CopyErrorData(); + FlushErrorState(); + + RollbackAndReleaseCurrentSubTransaction(); + MemoryContextSwitchTo(oldCxt); + CurrentResourceOwner = oldowner; + + /* + * If fatal_errors is true, we just propagate the error up to the + * highest level. Otherwise the error is appended to our current list + * of errors, and we continue checking. + */ + if (cstate->fatal_errors) + ReThrowError(edata); + else + checker_error_edata(cstate, edata); + MemoryContextSwitchTo(oldCxt); + + /* reconnect spi */ + SPI_restore_connection(); + } + PG_END_TRY(); + } + + /* + * We have to assign TupleDesc to all used record variables step by step. We + * would to use a exec routines for query preprocessing, so we must to create + * a typed NULL value, and this value is assigned to record variable. + */ + static void + assign_tupdesc_row_or_rec(PLpgSQL_checkstate *cstate, + PLpgSQL_row *row, PLpgSQL_rec *rec, + TupleDesc tupdesc) + { + bool *nulls; + HeapTuple tup; + + if (tupdesc == NULL) + { + checker_error(cstate, + 0, 0, + "tuple descriptor is empty", NULL, NULL, + "warning", + 0, NULL, NULL); + return; + } + + /* + * row variable has assigned TupleDesc already, so don't be processed here + */ + if (rec != NULL) + { + PLpgSQL_rec *target = (PLpgSQL_rec *) (cstate->estate->datums[rec->dno]); + + if (target->freetup) + heap_freetuple(target->tup); + + if (rec->freetupdesc) + FreeTupleDesc(target->tupdesc); + + /* initialize rec by NULLs */ + nulls = (bool *) palloc(tupdesc->natts * sizeof(bool)); + memset(nulls, true, tupdesc->natts * sizeof(bool)); + + target->tupdesc = CreateTupleDescCopy(tupdesc); + target->freetupdesc = true; + + tup = heap_form_tuple(tupdesc, NULL, nulls); + if (HeapTupleIsValid(tup)) + { + target->tup = tup; + target->freetup = true; + } + else + elog(ERROR, "cannot to build valid composite value"); + } + } + + /* + * Assign a tuple descriptor to variable specified by dno + */ + static void + assign_tupdesc_dno(PLpgSQL_checkstate *cstate, int varno, TupleDesc tupdesc) + { + PLpgSQL_datum *target = cstate->estate->datums[varno]; + + /* check assign without IO casts */ + if (target->dtype == PLPGSQL_DTYPE_VAR) + { + PLpgSQL_var *var = (PLpgSQL_var *) target; + + if (type_is_rowtype(tupdesc->attrs[0]->atttypid)) + checker_error(cstate, + ERRCODE_DATATYPE_MISMATCH, 0, + "cannot assign composite value to a scalar variable", + NULL, + NULL, + "error", + 0, NULL, NULL); + + else if (var->datatype->typoid != tupdesc->attrs[0]->atttypid) + { + StringInfoData str; + + initStringInfo(&str); + appendStringInfo(&str, "assign \"%s\" value to \"%s\" variable", + format_type_be(tupdesc->attrs[0]->atttypid), + format_type_be(var->datatype->typoid)); + + checker_error(cstate, + ERRCODE_DATATYPE_MISMATCH, 0, + "target variable has different type then expression result", + str.data, + "Hidden casting can be a performance issue.", + "performance", + 0, NULL, NULL); + + pfree(str.data); + } + } + else if (target->dtype == PLPGSQL_DTYPE_REC) + assign_tupdesc_row_or_rec(cstate, NULL, (PLpgSQL_rec *) target, tupdesc); + } + + /* + * Returns a tuple descriptor based on existing plan, When error is detected + * returns null. + */ + static TupleDesc + expr_get_desc(PLpgSQL_checkstate *cstate, + PLpgSQL_expr *query, + bool use_element_type, + bool expand_record, + bool is_expression) + { + TupleDesc tupdesc = NULL; + CachedPlanSource *plansource = NULL; + + 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); + + if (!plansource->resultDesc) + { + if (is_expression) + elog(ERROR, "query returns no result"); + else + return NULL; + } + 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 (is_expression && tupdesc->natts != 1) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("query \"%s\" returned %d columns", + 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)))); + FreeTupleDesc(tupdesc); + } + /* 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 + /* XXX: should be a warning? */ + ereport(ERROR, + (errmsg("cannot to identify real type for record type variable"))); + } + if (is_expression && tupdesc->natts != 1) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("query \"%s\" returned %d columns", + 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(..) + * + * XXX: Why don't we always do that? + */ + 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) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("function does not return composite type, is not possible to identify composite type"))); + + FreeTupleDesc(tupdesc); + BlessTupleDesc(rd); + + tupdesc = rd; + } + } + } + ReleaseCachedPlan(cplan, true); + } + return tupdesc; + } + + /* + * Ensure check for all statements in list + */ + void + check_stmts(PLpgSQL_checkstate *cstate, List *stmts) + { + ListCell *lc; + + foreach(lc, stmts) + { + check_stmt(cstate, (PLpgSQL_stmt *) lfirst(lc)); + } + } + + /* + * returns refname of PLpgSQL_datum + */ + static char * + datum_get_refname(PLpgSQL_datum *d) + { + switch (d->dtype) + { + case PLPGSQL_DTYPE_VAR: + return ((PLpgSQL_var *) d)->refname; + + case PLPGSQL_DTYPE_ROW: + return ((PLpgSQL_row *) d)->refname; + + case PLPGSQL_DTYPE_REC: + return ((PLpgSQL_rec *) d)->refname; + + default: + return NULL; + } + } + + /* + * walk over all statements + */ + void + check_stmt(PLpgSQL_checkstate *cstate, PLpgSQL_stmt *stmt) + { + TupleDesc tupdesc = NULL; + PLpgSQL_function *func; + ListCell *l; + ResourceOwner oldowner; + MemoryContext oldCxt = CurrentMemoryContext; + + if (stmt == NULL) + return; + + cstate->estate->err_stmt = stmt; + func = cstate->estate->func; + + oldowner = CurrentResourceOwner; + BeginInternalSubTransaction(NULL); + MemoryContextSwitchTo(oldCxt); + + PG_TRY(); + { + 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++) + { + char *refname; + + d = func->datums[stmt_block->initvarnos[i]]; + + if (d->dtype == PLPGSQL_DTYPE_VAR) + { + PLpgSQL_var *var = (PLpgSQL_var *) d; + + check_expr(cstate, var->default_val); + } + refname = datum_get_refname(d); + if (refname != NULL) + { + ListCell *l; + + foreach(l, cstate->argnames) + { + char *argname = (char *) lfirst(l); + + if (strcmp(argname, refname) == 0) + { + StringInfoData str; + + initStringInfo(&str); + appendStringInfo(&str, "parameter \"%s\" is overlapped", + refname); + + checker_error(cstate, + 0, 0, + str.data, + "Local variable overlap function parameter.", + NULL, + "warning", + 0, NULL, NULL); + pfree(str.data); + } + } + } + } + + check_stmts(cstate, stmt_block->body); + + if (stmt_block->exceptions) + { + foreach(l, stmt_block->exceptions->exc_list) + { + check_stmts(cstate, ((PLpgSQL_exception *) lfirst(l))->action); + } + } + } + break; + + case PLPGSQL_STMT_ASSIGN: + { + PLpgSQL_stmt_assign *stmt_assign = (PLpgSQL_stmt_assign *) stmt; + + check_target(cstate, stmt_assign->varno); + + /* prepare plan if desn't exist yet */ + check_assignment(cstate, stmt_assign->expr, NULL, NULL, + stmt_assign->varno); + + /* + * XXX: i thínk I lost some args to prepare_expr here + */ + } + break; + + case PLPGSQL_STMT_IF: + { + PLpgSQL_stmt_if *stmt_if = (PLpgSQL_stmt_if *) stmt; + ListCell *l; + + check_expr(cstate, stmt_if->cond); + check_stmts(cstate, stmt_if->then_body); + foreach(l, stmt_if->elsif_list) + { + PLpgSQL_if_elsif *elif = (PLpgSQL_if_elsif *) lfirst(l); + + check_expr(cstate, elif->cond); + check_stmts(cstate, elif->stmts); + } + + check_stmts(cstate, 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 *) cstate->estate->datums[stmt_case->t_varno]; + + /* + * we need to set hidden variable type + */ + prepare_expr(cstate, stmt_case->t_expr, 0); + tupdesc = expr_get_desc(cstate, + stmt_case->t_expr, + false, /* no element type */ + true, /* 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, + cstate->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(cstate, cwt->expr); + check_stmts(cstate, cwt->stmts); + } + + check_stmts(cstate, stmt_case->else_stmts); + } + break; + + case PLPGSQL_STMT_LOOP: + check_stmts(cstate, ((PLpgSQL_stmt_loop *) stmt)->body); + break; + + case PLPGSQL_STMT_WHILE: + { + PLpgSQL_stmt_while *stmt_while = (PLpgSQL_stmt_while *) stmt; + + check_expr(cstate, stmt_while->cond); + check_stmts(cstate, stmt_while->body); + } + break; + + case PLPGSQL_STMT_FORI: + { + PLpgSQL_stmt_fori *stmt_fori = (PLpgSQL_stmt_fori *) stmt; + + check_expr(cstate, stmt_fori->lower); + check_expr(cstate, stmt_fori->upper); + check_expr(cstate, stmt_fori->step); + check_stmts(cstate, stmt_fori->body); + } + break; + + case PLPGSQL_STMT_FORS: + { + PLpgSQL_stmt_fors *stmt_fors = (PLpgSQL_stmt_fors *) stmt; + + check_row_or_rec(cstate, stmt_fors->row, stmt_fors->rec); + + /* we need to set hidden variable type */ + check_assignment(cstate, stmt_fors->query, + stmt_fors->rec, stmt_fors->row, -1); + + check_stmts(cstate, stmt_fors->body); + } + break; + + case PLPGSQL_STMT_FORC: + { + PLpgSQL_stmt_forc *stmt_forc = (PLpgSQL_stmt_forc *) stmt; + PLpgSQL_var *var = (PLpgSQL_var *) func->datums[stmt_forc->curvar]; + + check_row_or_rec(cstate, stmt_forc->row, stmt_forc->rec); + + check_expr(cstate, stmt_forc->argquery); + + if (var->cursor_explicit_expr != NULL) + check_assignment(cstate, var->cursor_explicit_expr, + stmt_forc->rec, stmt_forc->row, -1); + + check_stmts(cstate, stmt_forc->body); + } + break; + + case PLPGSQL_STMT_DYNFORS: + { + PLpgSQL_stmt_dynfors *stmt_dynfors = (PLpgSQL_stmt_dynfors *) stmt; + + if (stmt_dynfors->rec != NULL) + { + checker_error(cstate, + 0, 0, + "cannot determinate a result of dynamic SQL", + "Cannot to contine in check.", + "Don't use dynamic SQL and record type together, when you would check function.", + "warning", + 0, NULL, NULL); + + /* + * don't continue in checking. Behave should be + * indeterministic. + */ + break; + } + check_expr(cstate, stmt_dynfors->query); + + foreach(l, stmt_dynfors->params) + { + check_expr(cstate, (PLpgSQL_expr *) lfirst(l)); + } + + check_stmts(cstate, stmt_dynfors->body); + } + break; + + case PLPGSQL_STMT_FOREACH_A: + { + PLpgSQL_stmt_foreach_a *stmt_foreach_a = (PLpgSQL_stmt_foreach_a *) stmt; + + check_target(cstate, stmt_foreach_a->varno); + + check_element_assignment(cstate, stmt_foreach_a->expr, NULL, NULL, stmt_foreach_a->varno); + + check_stmts(cstate, stmt_foreach_a->body); + } + break; + + case PLPGSQL_STMT_EXIT: + check_expr(cstate, ((PLpgSQL_stmt_exit *) stmt)->cond); + break; + + case PLPGSQL_STMT_PERFORM: + check_expr(cstate, ((PLpgSQL_stmt_perform *) stmt)->expr); + break; + + case PLPGSQL_STMT_RETURN: + check_expr(cstate, ((PLpgSQL_stmt_return *) stmt)->expr); + break; + + case PLPGSQL_STMT_RETURN_NEXT: + check_expr(cstate, ((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(cstate, stmt_rq->dynquery); + + check_expr(cstate, stmt_rq->query); + + foreach(l, stmt_rq->params) + { + check_expr(cstate, (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(cstate, (PLpgSQL_expr *) lfirst(l)); + } + + foreach(l, stmt_raise->options) + { + PLpgSQL_raise_option *opt = (PLpgSQL_raise_option *) lfirst(l); + + check_expr(cstate, opt->expr); + } + + 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; + + if (stmt_execsql->into) + { + check_row_or_rec(cstate, stmt_execsql->row, stmt_execsql->rec); + check_assignment(cstate, stmt_execsql->sqlstmt, + stmt_execsql->rec, stmt_execsql->row, -1); + } + else + { + /* only statement */ + check_expr(cstate, stmt_execsql->sqlstmt); + } + } + break; + + case PLPGSQL_STMT_DYNEXECUTE: + { + PLpgSQL_stmt_dynexecute *stmt_dynexecute = (PLpgSQL_stmt_dynexecute *) stmt; + + check_expr(cstate, stmt_dynexecute->query); + + foreach(l, stmt_dynexecute->params) + { + check_expr(cstate, (PLpgSQL_expr *) lfirst(l)); + } + + if (stmt_dynexecute->into) + { + check_row_or_rec(cstate, stmt_dynexecute->row, stmt_dynexecute->rec); + + if (stmt_dynexecute->rec != NULL) + { + checker_error(cstate, + 0, 0, + "cannot determinate a result of dynamic SQL", + "Cannot to contine in check.", + "Don't use dynamic SQL and record type together, when you would check function.", + "warning", + 0, NULL, NULL); + + /* + * don't continue in checking. Behave should be + * indeterministic. + */ + break; + } + } + } + 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) + check_expr(cstate, var->cursor_explicit_expr); + + check_expr(cstate, stmt_open->query); + check_expr(cstate, stmt_open->argquery); + check_expr(cstate, stmt_open->dynquery); + foreach(l, stmt_open->params) + { + check_expr(cstate, (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(cstate, diag_item->target); + } + } + break; + + case PLPGSQL_STMT_FETCH: + { + PLpgSQL_stmt_fetch *stmt_fetch = (PLpgSQL_stmt_fetch *) stmt; + PLpgSQL_var *var = (PLpgSQL_var *) (cstate->estate->datums[stmt_fetch->curvar]); + + check_row_or_rec(cstate, stmt_fetch->row, stmt_fetch->rec); + + if (var != NULL && var->cursor_explicit_expr != NULL) + check_assignment(cstate, var->cursor_explicit_expr, + stmt_fetch->rec, stmt_fetch->row, -1); + } + break; + + case PLPGSQL_STMT_CLOSE: + break; + + default: + elog(ERROR, "unrecognized cmd_type: %d", stmt->cmd_type); + } + + RollbackAndReleaseCurrentSubTransaction(); + MemoryContextSwitchTo(oldCxt); + CurrentResourceOwner = oldowner; + + SPI_restore_connection(); + } + PG_CATCH(); + { + ErrorData *edata; + + MemoryContextSwitchTo(oldCxt); + edata = CopyErrorData(); + FlushErrorState(); + + RollbackAndReleaseCurrentSubTransaction(); + MemoryContextSwitchTo(oldCxt); + CurrentResourceOwner = oldowner; + + /* + * If fatal_errors is true, we just propagate the error up to the + * highest level. Otherwise the error is appended to our current list + * of errors, and we continue checking. + */ + if (cstate->fatal_errors) + ReThrowError(edata); + else + checker_error_edata(cstate, edata); + MemoryContextSwitchTo(oldCxt); + + /* reconnect spi */ + SPI_restore_connection(); + } + PG_END_TRY(); + } + + /* + * Initialize plpgsql datum to NULL. This routine is used only for function + * and trigger parameters so it should not support all dtypes. + */ + static void + init_datum(PLpgSQL_checkstate *cstate, int dno) + { + switch (cstate->estate->datums[dno]->dtype) + { + case PLPGSQL_DTYPE_VAR: + { + PLpgSQL_var *var = (PLpgSQL_var *) cstate->estate->datums[dno]; + + var->value = (Datum) 0; + var->isnull = true; + var->freeval = false; + } + break; + + case PLPGSQL_DTYPE_ROW: + { + PLpgSQL_row *row = (PLpgSQL_row *) cstate->estate->datums[dno]; + int fnum; + + for (fnum = 0; fnum < row->nfields; fnum++) + { + if (row->varnos[fnum] < 0) + continue; /* skip dropped column in row struct */ + + init_datum(cstate, row->varnos[fnum]); + } + } + break; + + default: + elog(ERROR, "unexpected dtype: %d", cstate->estate->datums[dno]->dtype); + } + } + + /* + * prepare PLpgSQL_checkstate structure + */ + static void + cstate_setup(PLpgSQL_checkstate *cstate, + Oid fn_oid, + TupleDesc tupdesc, + Tuplestorestate *tupstore, + bool fatal_errors, + bool perform_warnings) + { + cstate->fn_oid = fn_oid; + cstate->estate = NULL; + cstate->tupdesc = tupdesc; + cstate->tuple_store = tupstore; + cstate->fatal_errors = fatal_errors; + cstate->perform_warnings = perform_warnings; + cstate->argnames = NIL; + } + + /* + * forward edata out from checker + */ + static void + checker_error_edata(PLpgSQL_checkstate *cstate, + ErrorData *edata) + { + checker_error(cstate, + edata->sqlerrcode, + edata->lineno, + edata->message, + edata->detail, + edata->hint, + "error", + edata->internalpos, + edata->internalquery, + edata->context); + } + + /* + * checker_error formats and collects a identifided issues to output tuplestore + */ + static void + checker_error(PLpgSQL_checkstate *cstate, + int sqlerrcode, + int lineno, + const char *message, + const char *detail, + const char *hint, + const char *level, + int position, + const char *query, + const char *context) + { + Datum values[Natts_result]; + bool nulls[Natts_result]; + + Assert(message != NULL); + Assert(level != NULL); + + /* ignore performance warnings when it is requested */ + if (strcmp(level, "performance") == 0 && !cstate->perform_warnings) + return; + + SET_RESULT_OID(Anum_result_functionid, cstate->fn_oid); + + if (cstate->estate && cstate->estate->err_stmt != NULL) + { + /* use lineno based on err_stmt */ + SET_RESULT_INT32(Anum_result_lineno, cstate->estate->err_stmt->lineno); + SET_RESULT_TEXT(Anum_result_statement, plpgsql_stmt_typename(cstate->estate->err_stmt)); + } + else + { + /* lineno is based on edata */ + SET_RESULT_INT32(Anum_result_lineno, lineno); + SET_RESULT_NULL(Anum_result_statement); + } + + SET_RESULT_TEXT(Anum_result_sqlstate, unpack_sql_state(sqlerrcode)); + SET_RESULT_TEXT(Anum_result_message, message); + + if (detail != NULL) + SET_RESULT_TEXT(Anum_result_detail, detail); + else + SET_RESULT_NULL(Anum_result_detail); + + if (hint != NULL) + SET_RESULT_TEXT(Anum_result_hint, hint); + else + SET_RESULT_NULL(Anum_result_hint); + + SET_RESULT_TEXT(Anum_result_level, level); + SET_RESULT_INT32(Anum_result_position, position); + + if (query != NULL) + SET_RESULT_TEXT(Anum_result_query, query); + else + SET_RESULT_NULL(Anum_result_query); + + if (context != NULL) + SET_RESULT_TEXT(Anum_result_context, context); + else + SET_RESULT_NULL(Anum_result_context); + + tuplestore_putvalues(cstate->tuple_store, cstate->tupdesc, values, nulls); + } + + /* + * Loads function's configuration + * + * Before checking function we have to load configuration related to + * function. This is function manager job, but we don't use it for checking. + */ + static int + load_configuration(HeapTuple procTuple, bool *reload_config) + { + Datum datum; + bool isnull; + int new_nest_level; + + *reload_config = false; + new_nest_level = 0; + + datum = SysCacheGetAttr(PROCOID, procTuple, Anum_pg_proc_proconfig, &isnull); + if (!isnull) + { + ArrayType *set_items; + + /* Set per-function configuration parameters */ + set_items = DatumGetArrayTypeP(datum); + + if (set_items != NULL) + { /* Need a new GUC nesting level */ + new_nest_level = NewGUCNestLevel(); + *reload_config = true; + ProcessGUCArray(set_items, + (superuser() ? PGC_SUSET : PGC_USERSET), + PGC_S_SESSION, + GUC_ACTION_SAVE); + } + } + return new_nest_level; + } + + /* + * Set up a fake fcinfo with just enough info to satisfy plpgsql_compile(). + * + * There should be a different real argtypes for polymorphic params. + */ + void + plpgsql_setup_fake_fcinfo(FmgrInfo *flinfo, + FunctionCallInfoData *fcinfo, + TriggerData *trigdata, + EventTriggerData *etrigdata, + Oid funcoid, + PLpgSQL_trigtype trigtype) + { + /* clean structures */ + MemSet(fcinfo, 0, sizeof(FunctionCallInfoData)); + MemSet(flinfo, 0, sizeof(FmgrInfo)); + + fcinfo->flinfo = flinfo; + flinfo->fn_oid = funcoid; + flinfo->fn_mcxt = CurrentMemoryContext; + + if (trigtype == PLPGSQL_DML_TRIGGER) + { + Assert(trigdata != NULL); + + MemSet(trigdata, 0, sizeof(trigdata)); + trigdata->type = T_TriggerData; + fcinfo->context = (Node *) trigdata; + } + else if (trigtype == PLPGSQL_EVENT_TRIGGER) + { + MemSet(etrigdata, 0, sizeof(etrigdata)); + etrigdata->type = T_EventTriggerData; + fcinfo->context = (Node *) etrigdata; + } + } *** a/src/pl/plpgsql/src/pl_comp.c --- b/src/pl/plpgsql/src/pl_comp.c *************** *** 116,122 **** static PLpgSQL_function *plpgsql_HashTableLookup(PLpgSQL_func_hashkey *func_key) static void plpgsql_HashTableInsert(PLpgSQL_function *function, PLpgSQL_func_hashkey *func_key); static void plpgsql_HashTableDelete(PLpgSQL_function *function); - static void delete_function(PLpgSQL_function *func); /* ---------- * plpgsql_compile Make an execution tree for a PL/pgSQL function. --- 116,121 ---- *************** *** 176,182 **** recheck: * Nope, so remove it from hashtable and try to drop associated * storage (if not done already). */ ! delete_function(function); /* * If the function isn't in active use then we can overwrite the --- 175,181 ---- * 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 *************** *** 2471,2477 **** plpgsql_resolve_polymorphic_argtypes(int numargs, } /* ! * delete_function - clean up as much as possible of a stale function cache * * We can't release the PLpgSQL_function struct itself, because of the * possibility that there are fn_extra pointers to it. We can release --- 2470,2476 ---- } /* ! * 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 *************** *** 2484,2491 **** plpgsql_resolve_polymorphic_argtypes(int numargs, * pointers to the same function cache. Hence be careful not to do things * twice. */ ! static void ! delete_function(PLpgSQL_function *func) { /* remove function from hash table (might be done already) */ plpgsql_HashTableDelete(func); --- 2483,2490 ---- * pointers to the same function cache. Hence be careful not to do things * twice. */ ! void ! plpgsql_delete_function(PLpgSQL_function *func) { /* remove function from hash table (might be done already) */ plpgsql_HashTableDelete(func); *** a/src/pl/plpgsql/src/pl_exec.c --- b/src/pl/plpgsql/src/pl_exec.c *************** *** 81,87 **** static SimpleEcontextStackEntry *simple_econtext_stack = NULL; * Local function forward declarations ************************************************************/ static void plpgsql_exec_error_callback(void *arg); - static PLpgSQL_datum *copy_plpgsql_datum(PLpgSQL_datum *datum); static int exec_stmt_block(PLpgSQL_execstate *estate, PLpgSQL_stmt_block *block); --- 81,86 ---- *************** *** 134,142 **** static int exec_stmt_dynexecute(PLpgSQL_execstate *estate, static int exec_stmt_dynfors(PLpgSQL_execstate *estate, PLpgSQL_stmt_dynfors *stmt); - static void plpgsql_estate_setup(PLpgSQL_execstate *estate, - PLpgSQL_function *func, - ReturnSetInfo *rsi); static void exec_eval_cleanup(PLpgSQL_execstate *estate); static void exec_prepare_plan(PLpgSQL_execstate *estate, --- 133,138 ---- *************** *** 211,217 **** static Datum exec_simple_cast_value(PLpgSQL_execstate *estate, static void exec_init_tuple_store(PLpgSQL_execstate *estate); static void exec_set_found(PLpgSQL_execstate *estate, bool state); static void plpgsql_create_econtext(PLpgSQL_execstate *estate); - static void plpgsql_destroy_econtext(PLpgSQL_execstate *estate); static void free_var(PLpgSQL_var *var); static void assign_text_var(PLpgSQL_var *var, const char *str); static PreparedParamsData *exec_eval_using_params(PLpgSQL_execstate *estate, --- 207,212 ---- *************** *** 924,930 **** plpgsql_exec_error_callback(void *arg) * Support function for initializing local execution variables * ---------- */ ! static PLpgSQL_datum * copy_plpgsql_datum(PLpgSQL_datum *datum) { PLpgSQL_datum *result; --- 919,925 ---- * Support function for initializing local execution variables * ---------- */ ! PLpgSQL_datum * copy_plpgsql_datum(PLpgSQL_datum *datum) { PLpgSQL_datum *result; *************** *** 3079,3085 **** exec_stmt_raise(PLpgSQL_execstate *estate, PLpgSQL_stmt_raise *stmt) * Initialize a mostly empty execution state * ---------- */ ! static void plpgsql_estate_setup(PLpgSQL_execstate *estate, PLpgSQL_function *func, ReturnSetInfo *rsi) --- 3074,3080 ---- * Initialize a mostly empty execution state * ---------- */ ! void plpgsql_estate_setup(PLpgSQL_execstate *estate, PLpgSQL_function *func, ReturnSetInfo *rsi) *************** *** 6192,6198 **** plpgsql_create_econtext(PLpgSQL_execstate *estate) * We check that it matches the top stack entry, and destroy the stack * entry along with the context. */ ! static void plpgsql_destroy_econtext(PLpgSQL_execstate *estate) { SimpleEcontextStackEntry *next; --- 6187,6193 ---- * We check that it matches the top stack entry, and destroy the stack * entry along with the context. */ ! void plpgsql_destroy_econtext(PLpgSQL_execstate *estate) { SimpleEcontextStackEntry *next; *** a/src/pl/plpgsql/src/pl_handler.c --- b/src/pl/plpgsql/src/pl_handler.c *************** *** 15,20 **** --- 15,23 ---- #include "plpgsql.h" + #include "catalog/pg_enum.h" + #include "catalog/pg_language.h" + #include "access/htup_details.h" #include "catalog/pg_proc.h" #include "catalog/pg_type.h" *************** *** 40,45 **** int plpgsql_variable_conflict = PLPGSQL_RESOLVE_ERROR; --- 43,50 ---- /* Hook for plugins */ PLpgSQL_plugin **plugin_ptr = NULL; + static void precheck_conditions(HeapTuple procTuple, PLpgSQL_trigtype trigtype, Oid relid); + static PLpgSQL_trigtype get_trigtype(HeapTuple procTuple); /* * _PG_init() - library load-time initialization *************** *** 187,197 **** plpgsql_inline_handler(PG_FUNCTION_ARGS) * plpgsql_exec_function(). In particular note that this sets things up * with no arguments passed. */ ! MemSet(&fake_fcinfo, 0, sizeof(fake_fcinfo)); ! MemSet(&flinfo, 0, sizeof(flinfo)); ! fake_fcinfo.flinfo = &flinfo; ! flinfo.fn_oid = InvalidOid; ! flinfo.fn_mcxt = CurrentMemoryContext; retval = plpgsql_exec_function(func, &fake_fcinfo); --- 192,198 ---- * plpgsql_exec_function(). In particular note that this sets things up * with no arguments passed. */ ! plpgsql_setup_fake_fcinfo(&flinfo, &fake_fcinfo, NULL, NULL, InvalidOid, PLPGSQL_NOT_TRIGGER); retval = plpgsql_exec_function(func, &fake_fcinfo); *************** *** 225,266 **** plpgsql_validator(PG_FUNCTION_ARGS) { Oid funcoid = PG_GETARG_OID(0); HeapTuple tuple; - Form_pg_proc proc; - char functyptype; int numargs; Oid *argtypes; char **argnames; char *argmodes; - bool is_dml_trigger = false; - bool is_event_trigger = false; int i; /* Get the new function's pg_proc entry */ 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); ! ! /* Disallow pseudotype result */ ! /* except for TRIGGER, RECORD, VOID, or polymorphic */ ! if (functyptype == TYPTYPE_PSEUDO) ! { ! /* we assume OPAQUE with no arguments means a trigger */ ! if (proc->prorettype == TRIGGEROID || ! (proc->prorettype == OPAQUEOID && proc->pronargs == 0)) ! is_dml_trigger = true; ! else if (proc->prorettype == EVTTRIGGEROID) ! is_event_trigger = true; ! else if (proc->prorettype != RECORDOID && ! proc->prorettype != VOIDOID && ! !IsPolymorphicType(proc->prorettype)) ! ereport(ERROR, ! (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), ! errmsg("PL/pgSQL functions cannot return type %s", ! format_type_be(proc->prorettype)))); ! } /* Disallow pseudotypes in arguments (either IN or OUT) */ /* except for polymorphic */ --- 226,244 ---- { Oid funcoid = PG_GETARG_OID(0); HeapTuple tuple; int numargs; Oid *argtypes; char **argnames; char *argmodes; int i; + PLpgSQL_trigtype trigtype; /* Get the new function's pg_proc entry */ tuple = SearchSysCache1(PROCOID, ObjectIdGetDatum(funcoid)); if (!HeapTupleIsValid(tuple)) elog(ERROR, "cache lookup failed for function %u", funcoid); ! trigtype = get_trigtype(tuple); /* Disallow pseudotypes in arguments (either IN or OUT) */ /* except for polymorphic */ *************** *** 297,319 **** plpgsql_validator(PG_FUNCTION_ARGS) * Set up a fake fcinfo with just enough info to satisfy * plpgsql_compile(). */ ! 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 (is_dml_trigger) ! { ! MemSet(&trigdata, 0, sizeof(trigdata)); ! trigdata.type = T_TriggerData; ! fake_fcinfo.context = (Node *) &trigdata; ! } ! else if (is_event_trigger) ! { ! MemSet(&etrigdata, 0, sizeof(etrigdata)); ! etrigdata.type = T_EventTriggerData; ! fake_fcinfo.context = (Node *) &etrigdata; ! } /* Test-compile the function */ plpgsql_compile(&fake_fcinfo, true); --- 275,281 ---- * Set up a fake fcinfo with just enough info to satisfy * plpgsql_compile(). */ ! plpgsql_setup_fake_fcinfo(&flinfo, &fake_fcinfo, &trigdata, &etrigdata, funcoid, trigtype); /* Test-compile the function */ plpgsql_compile(&fake_fcinfo, true); *************** *** 329,331 **** plpgsql_validator(PG_FUNCTION_ARGS) --- 291,444 ---- PG_RETURN_VOID(); } + + /* + * ---------- + * plpgsql_check_function + * + * It ensure a detailed validation + * ---------- + */ + PG_FUNCTION_INFO_V1(plpgsql_check_function); + + Datum + plpgsql_check_function(PG_FUNCTION_ARGS) + { + Oid funcoid = PG_GETARG_OID(0); + Oid relid = PG_GETARG_OID(1); + bool fatal_errors = PG_GETARG_BOOL(2); + bool perform_warnings = PG_GETARG_BOOL(3); + TupleDesc tupdesc; + HeapTuple procTuple; + Tuplestorestate *tupstore; + MemoryContext per_query_ctx; + MemoryContext oldcontext; + ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo; + PLpgSQL_trigtype trigtype; + + /* check to see if caller supports us returning a tuplestore */ + if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo)) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("set-valued function called in context that cannot accept a set"))); + + if (!(rsinfo->allowedModes & SFRM_Materialize)) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("materialize mode required, but it is not allowed in this context"))); + + procTuple = SearchSysCache1(PROCOID, ObjectIdGetDatum(funcoid)); + if (!HeapTupleIsValid(procTuple)) + elog(ERROR, "cache lookup failed for function %u", funcoid); + + trigtype = get_trigtype(procTuple); + + precheck_conditions(procTuple, trigtype, relid); + + /* need to build tuplestore in query context */ + per_query_ctx = rsinfo->econtext->ecxt_per_query_memory; + oldcontext = MemoryContextSwitchTo(per_query_ctx); + + tupdesc = CreateTupleDescCopy(rsinfo->expectedDesc); + tupstore = tuplestore_begin_heap(false, false, work_mem); + MemoryContextSwitchTo(oldcontext); + + plpgsql_function_check(procTuple, relid, trigtype, + tupdesc, tupstore, + fatal_errors, perform_warnings); + + ReleaseSysCache(procTuple); + + /* clean up and return the tuplestore */ + tuplestore_donestoring(tupstore); + + rsinfo->returnMode = SFRM_Materialize; + rsinfo->setResult = tupstore; + rsinfo->setDesc = tupdesc; + + return (Datum) 0; + } + + /* + * Process necessary checking before code checking + */ + static void + precheck_conditions(HeapTuple procTuple, PLpgSQL_trigtype trigtype, Oid relid) + { + Form_pg_proc proc; + Form_pg_language languageStruct; + HeapTuple languageTuple; + char *funcname; + + proc = (Form_pg_proc) GETSTRUCT(procTuple); + funcname = format_procedure(HeapTupleGetOid(procTuple)); + + /* used language must be plpgsql */ + languageTuple = SearchSysCache1(LANGOID, ObjectIdGetDatum(proc->prolang)); + Assert(HeapTupleIsValid(languageTuple)); + + languageStruct = (Form_pg_language) GETSTRUCT(languageTuple); + if (strcmp(NameStr(languageStruct->lanname), "plpgsql") != 0) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("%s is not a plpgsql function", funcname))); + + ReleaseSysCache(languageTuple); + + /* dml trigger needs valid relid, others not */ + if (trigtype == PLPGSQL_DML_TRIGGER) + { + if (!OidIsValid(relid)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("missing trigger relation"), + errhint("Trigger relation oid must be valid"))); + } + else + { + if (OidIsValid(relid)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("function is not trigger"), + errhint("Trigger relation oid must not be valid for non dml trigger function."))); + } + + pfree(funcname); + } + + /* + * Returns PLpgSQL_trigtype based on prorettype + */ + static PLpgSQL_trigtype + get_trigtype(HeapTuple procTuple) + { + Form_pg_proc proc; + char functyptype; + + proc = (Form_pg_proc) GETSTRUCT(procTuple); + + functyptype = get_typtype(proc->prorettype); + + /* + * Disallow pseudotype result except for TRIGGER, RECORD, VOID, or + * polymorphic + */ + if (functyptype == TYPTYPE_PSEUDO) + { + /* we assume OPAQUE with no arguments means a trigger */ + if (proc->prorettype == TRIGGEROID || + (proc->prorettype == OPAQUEOID && proc->pronargs == 0)) + return PLPGSQL_DML_TRIGGER; + else if (proc->prorettype == EVTTRIGGEROID) + return PLPGSQL_EVENT_TRIGGER; + else if (proc->prorettype != RECORDOID && + proc->prorettype != VOIDOID && + !IsPolymorphicType(proc->prorettype)) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("PL/pgSQL functions cannot return type %s", + format_type_be(proc->prorettype)))); + } + + return PLPGSQL_NOT_TRIGGER; + } *** /dev/null --- b/src/pl/plpgsql/src/plpgsql--1.0--1.1.sql *************** *** 0 **** --- 1,18 ---- + /* src/pl/plpgsql/src/plpgsql--1.1.sql */ + + /* + * Currently, all the interesting stuff is done by CREATE LANGUAGE. + * Later we will probably "dumb down" that command and put more of the + * knowledge into this script. + */ + + CREATE TYPE plpgsql_check_function_format AS ENUM ('XML', 'PLAIN_TEXT'); + + CREATE FUNCTION plpgsql_check_function(funcoid regprocedure, + relid regclass = 0, + fatal_errors boolean = true, + perform_warnings boolean = false, + format plpgsql_check_function_format = 'PLAIN_TEXT') + RETURNS SETOF text AS 'MODULE_PATHNAME' + LANGUAGE C + RETURNS NULL ON NULL INPUT; *** /dev/null --- b/src/pl/plpgsql/src/plpgsql--1.1.sql *************** *** 0 **** --- 1,30 ---- + /* src/pl/plpgsql/src/plpgsql--1.1.sql */ + + /* + * Currently, all the interesting stuff is done by CREATE LANGUAGE. + * Later we will probably "dumb down" that command and put more of the + * knowledge into this script. + */ + + CREATE PROCEDURAL LANGUAGE plpgsql; + + COMMENT ON PROCEDURAL LANGUAGE plpgsql IS 'PL/pgSQL procedural language'; + + CREATE FUNCTION plpgsql_check_function(funcoid regprocedure, + relid regclass = 0, + fatal_errors boolean = true, + perform_warnings boolean = false) + RETURNS TABLE(functionid regproc, + lineno int, + statement text, + sqlstate text, + message text, + detail text, + hint text, + level text, + "position" int, + query text, + context text) + AS 'MODULE_PATHNAME' + LANGUAGE C + RETURNS NULL ON NULL INPUT; *** a/src/pl/plpgsql/src/plpgsql.control --- b/src/pl/plpgsql/src/plpgsql.control *************** *** 1,6 **** # plpgsql extension comment = 'PL/pgSQL procedural language' ! default_version = '1.0' module_pathname = '$libdir/plpgsql' relocatable = false schema = pg_catalog --- 1,6 ---- # plpgsql extension comment = 'PL/pgSQL procedural language' ! default_version = '1.1' module_pathname = '$libdir/plpgsql' relocatable = false schema = pg_catalog *** a/src/pl/plpgsql/src/plpgsql.h --- b/src/pl/plpgsql/src/plpgsql.h *************** *** 789,794 **** typedef struct PLpgSQL_execstate --- 789,810 ---- void *plugin_info; /* reserved for use by optional plugin */ } PLpgSQL_execstate; + enum + { + PLPGSQL_CHECK_FORMAT_PLAIN, + PLPGSQL_CHECK_FORMAT_XML + }; + + typedef struct PLpgSQL_checkstate + { + Oid fn_oid; /* oid of checked function */ + PLpgSQL_execstate *estate; /* check state is estate extension */ + Tuplestorestate *tuple_store; + TupleDesc tupdesc; + bool fatal_errors; /* stop on first error */ + bool perform_warnings; /* show performace warnings */ + List *argnames; + } PLpgSQL_checkstate; /* * A PLpgSQL_plugin structure represents an instrumentation plugin. *************** *** 924,929 **** extern PLpgSQL_condition *plpgsql_parse_err_condition(char *condname); --- 940,946 ---- 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 *************** *** 933,938 **** extern void _PG_init(void); --- 950,956 ---- 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_check_function(PG_FUNCTION_ARGS); /* ---------- * Functions in pl_exec.c *************** *** 952,957 **** extern Oid exec_get_datum_type(PLpgSQL_execstate *estate, --- 970,993 ---- extern void exec_get_datum_type_info(PLpgSQL_execstate *estate, PLpgSQL_datum *datum, Oid *typeid, int32 *typmod, Oid *collation); + extern PLpgSQL_datum *copy_plpgsql_datum(PLpgSQL_datum *datum); + extern void plpgsql_estate_setup(PLpgSQL_execstate *estate, + PLpgSQL_function *func, + ReturnSetInfo *rsi); + extern void plpgsql_destroy_econtext(PLpgSQL_execstate *estate); + + /* ---------- + * Functions for namespace handling in pl_check.c + * ---------- + */ + extern void plpgsql_function_check(HeapTuple procTuple, Oid relid, PLpgSQL_trigtype trigtype, + TupleDesc tupdesc, + Tuplestorestate *tupstore, + bool fatal_errors, + bool perform_warnings); + extern void plpgsql_setup_fake_fcinfo(FmgrInfo *flinfo, FunctionCallInfoData *fcinfo, + TriggerData *trigdata, EventTriggerData *etrigdata, + Oid funcoid, PLpgSQL_trigtype trigtype); /* ---------- * Functions for namespace handling in pl_funcs.c *** a/src/test/regress/expected/plpgsql.out --- b/src/test/regress/expected/plpgsql.out *************** *** 302,307 **** end; --- 302,313 ---- ' language plpgsql; create trigger tg_hslot_biu before insert or update on HSlot for each row execute procedure tg_hslot_biu(); + -- check trigger should not fail + select * from plpgsql_check_function('tg_hslot_biu()', 'HSlot'); + functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context + ------------+--------+-----------+----------+---------+--------+------+-------+----------+-------+--------- + (0 rows) + -- ************************************************************ -- * BEFORE DELETE on HSlot -- * - prevent from manual manipulation *************** *** 635,640 **** begin --- 641,652 ---- raise exception ''illegal backlink beginning with %'', mytype; end; ' language plpgsql; + -- check function should not fail + select * from plpgsql_check_function('tg_backlink_set(bpchar, bpchar)', 0); + functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context + ------------+--------+-----------+----------+---------+--------+------+-------+----------+-------+--------- + (0 rows) + -- ************************************************************ -- * Support function to clear out the backlink field if -- * it still points to specific slot *************** *** 2947,2952 **** NOTICE: 4 bb cc --- 2959,2996 ---- (1 row) + -- check function should not fail + select * from plpgsql_check_function('for_vect()'); + functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context + ------------+--------+-----------+----------+---------+--------+------+-------+----------+-------+--------- + (0 rows) + + -- 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 *************** *** 3428,3433 **** begin --- 3472,3483 ---- return; end; $$ language plpgsql; + -- check function should not fail + select * from plpgsql_check_function('forc01()'); + functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context + ------------+--------+-----------+----------+---------+--------+------+-------+----------+-------+--------- + (0 rows) + select forc01(); NOTICE: 5 from c NOTICE: 6 from c *************** *** 4027,4032 **** begin --- 4077,4088 ---- end case; end; $$ language plpgsql immutable; + -- check function should not fail + select * from plpgsql_check_function('case_test(bigint)'); + functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context + ------------+--------+-----------+----------+---------+--------+------+-------+----------+-------+--------- + (0 rows) + select case_test(1); case_test ----------- *************** *** 5085,5087 **** NOTICE: outer_func() done --- 5141,5664 ---- drop function outer_outer_func(int); drop function outer_func(int); drop function inner_func(int); + -- + -- check function statement tests + -- + --should fail - is not plpgsql + select * from plpgsql_check_function('session_user()'); + ERROR: "session_user"() is not a plpgsql function + create table t1(a int, b int); + create function f1() + returns void as $$ + begin + if false then + update t1 set c = 30; + end if; + if false then + raise notice '% %', r.c; + end if; + end; + $$ language plpgsql; + select f1(); + f1 + ---- + + (1 row) + + select * from plpgsql_check_function('f1()', fatal_errors := true); + functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context + ------------+--------+---------------+----------+--------------------------------------------+--------+------+-------+----------+----------------------+--------- + f1 | 4 | SQL statement | 42703 | column "c" of relation "t1" does not exist | | | error | 15 | update t1 set c = 30 | + (1 row) + + select * from plpgsql_check_function('f1()', fatal_errors := false); + functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context + ------------+--------+---------------+----------+--------------------------------------------+--------+------+-------+----------+----------------------+--------- + f1 | 4 | SQL statement | 42703 | column "c" of relation "t1" does not exist | | | error | 15 | update t1 set c = 30 | + f1 | 7 | RAISE | 42P01 | missing FROM-clause entry for table "r" | | | error | 8 | SELECT r.c | + f1 | 7 | RAISE | 42601 | too few parameters specified for RAISE | | | error | 0 | | + (3 rows) + + select * from plpgsql_check_function('f1()'); + functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context + ------------+--------+---------------+----------+--------------------------------------------+--------+------+-------+----------+----------------------+--------- + f1 | 4 | SQL statement | 42703 | column "c" of relation "t1" does not exist | | | error | 15 | update t1 set c = 30 | + (1 row) + + select f1(); + f1 + ---- + + (1 row) + + drop function f1(); + create function g1(out a int, out b int) + as $$ + select 10,20; + $$ language sql; + create function f1() + returns void as $$ + declare r record; + begin + r := g1(); + if false then + raise notice '%', r.c; + end if; + end; + $$ language plpgsql; + select f1(); + f1 + ---- + + (1 row) + + select * from plpgsql_check_function('f1()'); + functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context + ------------+--------+-----------+----------+-----------------------------+--------+------+-------+----------+-------+---------------------------- + f1 | 6 | RAISE | 42703 | record "r" has no field "c" | | | error | 0 | | SQL statement "SELECT r.c" + (1 row) + + select f1(); + f1 + ---- + + (1 row) + + drop function f1(); + drop function g1(); + create function g1(out a int, out b int) + returns setof record as $$ + select * from t1; + $$ language sql; + create function f1() + returns void as $$ + declare r record; + begin + for r in select * from g1() + loop + raise notice '%', r.c; + end loop; + end; + $$ language plpgsql; + select f1(); + f1 + ---- + + (1 row) + + select * from plpgsql_check_function('f1()'); + functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context + ------------+--------+-----------+----------+-----------------------------+--------+------+-------+----------+-------+---------------------------- + f1 | 6 | RAISE | 42703 | record "r" has no field "c" | | | error | 0 | | SQL statement "SELECT r.c" + (1 row) + + select f1(); + f1 + ---- + + (1 row) + + create or replace function f1() + returns void as $$ + declare r record; + begin + for r in select * from g1() + loop + r.c := 20; + end loop; + end; + $$ language plpgsql; + select f1(); + f1 + ---- + + (1 row) + + select * from plpgsql_check_function('f1()'); + functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context + ------------+--------+------------+----------+-----------------------------+--------+------+-------+----------+-------+--------- + f1 | 6 | assignment | 42703 | record "r" has no field "c" | | | error | 0 | | + (1 row) + + select f1(); + f1 + ---- + + (1 row) + + drop function f1(); + drop function g1(); + create function f1() + returns int as $$ + declare r int; + begin + if false then + r := a + b; + end if; + return r; + end; + $$ language plpgsql; + select f1(); + f1 + ---- + + (1 row) + + select * from plpgsql_check_function('f1()'); + functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context + ------------+--------+------------+----------+---------------------------+--------+------+-------+----------+--------------+--------- + f1 | 5 | assignment | 42703 | column "a" does not exist | | | error | 8 | SELECT a + b | + (1 row) + + select f1(); + f1 + ---- + + (1 row) + + drop function f1(); + create or replace function f1() + returns void as $$ + begin + if false then + raise notice '%', 1, 2; + end if; + end; + $$ language plpgsql; + select f1(); + f1 + ---- + + (1 row) + + select * from plpgsql_check_function('f1()'); + functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context + ------------+--------+-----------+----------+-----------------------------------------+--------+------+-------+----------+-------+--------- + f1 | 4 | RAISE | 42601 | too many parameters specified for RAISE | | | error | 0 | | + (1 row) + + select f1(); + f1 + ---- + + (1 row) + + drop function f1(); + create or replace function f1() + returns void as $$ + begin + if false then + raise notice '% %'; + end if; + end; + $$ language plpgsql; + select f1(); + f1 + ---- + + (1 row) + + select * from plpgsql_check_function('f1()'); + functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context + ------------+--------+-----------+----------+----------------------------------------+--------+------+-------+----------+-------+--------- + f1 | 4 | RAISE | 42601 | too few parameters specified for RAISE | | | error | 0 | | + (1 row) + + select f1(); + f1 + ---- + + (1 row) + + drop function f1(); + create or replace function f1() + returns void as $$ + declare r int[]; + begin + if false then + r[c+10] := 20; + end if; + end; + $$ language plpgsql; + select f1(); + f1 + ---- + + (1 row) + + select * from plpgsql_check_function('f1()'); + functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context + ------------+--------+------------+----------+---------------------------+--------+------+-------+----------+-------------+--------- + f1 | 5 | assignment | 42703 | column "c" does not exist | | | error | 8 | SELECT c+10 | + (1 row) + + select f1(); + f1 + ---- + + (1 row) + + drop function f1(); + create or replace function f1() + returns void as $$ + declare r int; + begin + if false then + r[10] := 20; + end if; + end; + $$ language plpgsql set search_path = public; + select f1(); + f1 + ---- + + (1 row) + + select * from plpgsql_check_function('f1()'); + functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context + ------------+--------+------------+----------+------------------------------------+--------+------+-------+----------+-------+--------- + f1 | 5 | assignment | 42804 | subscripted object is not an array | | | error | 0 | | + (1 row) + + select f1(); + f1 + ---- + + (1 row) + + drop function f1(); + create type _exception_type as ( + state text, + message text, + detail text); + create or replace function f1() + returns void as $$ + declare + _exception record; + begin + _exception := NULL::_exception_type; + exception when others then + get stacked diagnostics + _exception.state = RETURNED_SQLSTATE, + _exception.message = MESSAGE_TEXT, + _exception.detail = PG_EXCEPTION_DETAIL, + _exception.hint = PG_EXCEPTION_HINT; + end; + $$ language plpgsql; + select f1(); + f1 + ---- + + (1 row) + + select * from plpgsql_check_function('f1()'); + functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context + ------------+--------+-----------------+----------+-----------------------------------------+--------+------+-------+----------+-------+--------- + f1 | 7 | GET DIAGNOSTICS | 42703 | record "_exception" has no field "hint" | | | error | 0 | | + (1 row) + + drop function f1(); + create or replace function f1_trg() + returns trigger as $$ + begin + if new.a > 10 then + raise notice '%', new.b; + raise notice '%', new.c; + end if; + return new; + end; + $$ language plpgsql; + create trigger t1_f1 before insert on t1 + for each row + execute procedure f1_trg(); + insert into t1 values(6,30); + select * from plpgsql_check_function('f1_trg()','t1'); + functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context + ------------+--------+-----------+----------+-------------------------------+--------+------+-------+----------+-------+------------------------------ + f1_trg | 5 | RAISE | 42703 | record "new" has no field "c" | | | error | 0 | | SQL statement "SELECT new.c" + (1 row) + + insert into t1 values(6,30); + create or replace function f1_trg() + returns trigger as $$ + begin + new.a := new.a + 10; + new.b := new.b + 10; + new.c := 30; + return new; + end; + $$ language plpgsql; + -- should to fail + select * from plpgsql_check_function('f1_trg()','t1'); + functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context + ------------+--------+------------+----------+-------------------------------+--------+------+-------+----------+-------+--------- + f1_trg | 5 | assignment | 42703 | record "new" has no field "c" | | | error | 0 | | + (1 row) + + -- should to fail but not crash + insert into t1 values(6,30); + ERROR: record "new" has no field "c" + CONTEXT: PL/pgSQL function f1_trg() line 5 at assignment + create or replace function f1_trg() + returns trigger as $$ + begin + new.a := new.a + 10; + new.b := new.b + 10; + return new; + end; + $$ language plpgsql; + -- ok + select * from plpgsql_check_function('f1_trg()', 't1'); + functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context + ------------+--------+-----------+----------+---------+--------+------+-------+----------+-------+--------- + (0 rows) + + -- ok + insert into t1 values(6,30); + select * from t1; + a | b + ----+---- + 6 | 30 + 6 | 30 + 16 | 40 + (3 rows) + + drop trigger t1_f1 on t1; + drop function f1_trg(); + -- test of showing caret on correct place for multiline queries + create or replace function f1() + returns void as $$ + begin + select + var + from + foo; + end; + $$ language plpgsql; + select * from plpgsql_check_function('f1()'); + functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context + ------------+--------+---------------+----------+-----------------------------+--------+------+-------+----------+--------+--------- + f1 | 3 | SQL statement | 42703 | column "var" does not exist | | | error | 10 | select+| + | | | | | | | | | var +| + | | | | | | | | | from+| + | | | | | | | | | foo | + (1 row) + + drop function f1(); + create or replace function f1() + returns int as $$ + begin + return (select a + from t1 + where hh = 20); + end; + $$ language plpgsql; + select * from plpgsql_check_function('f1()'); + functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context + ------------+--------+-----------+----------+----------------------------+--------+------+-------+----------+----------------------------+--------- + f1 | 3 | RETURN | 42703 | column "hh" does not exist | | | error | 57 | SELECT (select a +| + | | | | | | | | | from t1 +| + | | | | | | | | | where hh = 20) | + (1 row) + + create or replace function f1() + returns int as $$ + begin + return (select a + from txxxxxxx + where hh = 20); + end; + $$ language plpgsql; + select * from plpgsql_check_function('f1()'); + functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context + ------------+--------+-----------+----------+------------------------------------+--------+------+-------+----------+----------------------------+--------- + f1 | 3 | RETURN | 42P01 | relation "txxxxxxx" does not exist | | | error | 36 | SELECT (select a +| + | | | | | | | | | from txxxxxxx+| + | | | | | | | | | where hh = 20) | + (1 row) + + drop function f1(); + drop table t1; + drop type _exception_type; + -- raise warnings when target row has different number of attributies in + -- SELECT INTO statement + create or replace function f1() + returns void as $$ + declare a1 int; a2 int; + begin + select 10,20 into a1,a2; + end; + $$ language plpgsql; + -- should be ok + select * from plpgsql_check_function('f1()'); + functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context + ------------+--------+-----------+----------+---------+--------+------+-------+----------+-------+--------- + (0 rows) + + create or replace function f1() + returns void as $$ + declare a1 int; a2 int; + begin + select 10,20 into a1; + end; + $$ language plpgsql; + -- raise warning + select * from plpgsql_check_function('f1()'); + functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context + ------------+--------+---------------+----------+-------------------------------------------+---------------------------------------------------------------+-------------------------------------------------+---------+----------+-------+--------- + f1 | 4 | SQL statement | 00000 | too many attributies for target variables | There are less target variables than output columns in query. | Check target variables in SELECT INTO statement | warning | 0 | | + (1 row) + + create or replace function f1() + returns void as $$ + declare a1 int; a2 int; + begin + select 10 into a1,a2; + end; + $$ language plpgsql; + -- raise warning + select * from plpgsql_check_function('f1()'); + functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context + ------------+--------+---------------+----------+------------------------------------------+---------------------------------------------------------------+--------------------------------------------------+---------+----------+-------+--------- + f1 | 4 | SQL statement | 00000 | too few attributies for target variables | There are more target variables than output columns in query. | Check target variables in SELECT INTO statement. | warning | 0 | | + (1 row) + + -- bogus code + set check_function_bodies to off; + create or replace function f1() + returns void as $$ + adasdfsadf + $$ language plpgsql; + select * from plpgsql_check_function('f1()'); + functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context + ------------+--------+-----------+----------+--------------------------------------+--------+------+-------+----------+------------+--------------------------------------------------- + f1 | 570 | | 42601 | syntax error at or near "adasdfsadf" | | | error | 2 | +| compilation of PL/pgSQL function "f1" near line 1 + | | | | | | | | | adasdfsadf+| + | | | | | | | | | | + (1 row) + + drop function f1(); + -- check event trigger function + create or replace function f1() returns event_trigger as $$ + BEGIN + RAISE NOTICE 'test_event_trigger: % %', tg_event, tg_tag; + END + $$ language plpgsql; + select * from plpgsql_check_function('f1()'); + functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context + ------------+--------+-----------+----------+---------+--------+------+-------+----------+-------+--------- + (0 rows) + + -- should fail + create or replace function f1() returns event_trigger as $$ + BEGIN + RAISE NOTICE 'test_event_trigger: % %', tg_event, tg_tagX; + END + $$ language plpgsql; + select * from plpgsql_check_function('f1()'); + functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context + ------------+--------+-----------+----------+---------------------------------+--------+------+-------+----------+----------------+--------- + f1 | 3 | RAISE | 42703 | column "tg_tagx" does not exist | | | error | 8 | SELECT tg_tagX | + (1 row) + + drop function f1(); *** a/src/test/regress/sql/plpgsql.sql --- b/src/test/regress/sql/plpgsql.sql *************** *** 366,371 **** end; --- 366,373 ---- create trigger tg_hslot_biu before insert or update on HSlot for each row execute procedure tg_hslot_biu(); + -- check trigger should not fail + select * from plpgsql_check_function('tg_hslot_biu()', 'HSlot'); -- ************************************************************ -- * BEFORE DELETE on HSlot *************** *** 747,752 **** begin --- 749,757 ---- end; ' language plpgsql; + -- check function should not fail + select * from plpgsql_check_function('tg_backlink_set(bpchar, bpchar)', 0); + -- ************************************************************ -- * Support function to clear out the backlink field if *************** *** 2458,2463 **** $proc$ language plpgsql; --- 2463,2475 ---- select for_vect(); + -- check function should not fail + select * from plpgsql_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 *************** *** 2837,2842 **** begin --- 2849,2857 ---- end; $$ language plpgsql; + -- check function should not fail + select * from plpgsql_check_function('forc01()'); + select forc01(); -- try updating the cursor's current row *************** *** 3315,3320 **** begin --- 3330,3339 ---- end; $$ language plpgsql immutable; + -- check function should not fail + select * from plpgsql_check_function('case_test(bigint)'); + + select case_test(1); select case_test(2); select case_test(3); *************** *** 3983,3985 **** drop function outer_outer_func(int); --- 4002,4386 ---- drop function outer_func(int); drop function inner_func(int); + + -- + -- check function statement tests + -- + + --should fail - is not plpgsql + select * from plpgsql_check_function('session_user()'); + + create table t1(a int, b int); + + create function f1() + returns void as $$ + begin + if false then + update t1 set c = 30; + end if; + if false then + raise notice '% %', r.c; + end if; + end; + $$ language plpgsql; + + select f1(); + select * from plpgsql_check_function('f1()', fatal_errors := true); + select * from plpgsql_check_function('f1()', fatal_errors := false); + + select * from plpgsql_check_function('f1()'); + + select f1(); + + drop function f1(); + + create function g1(out a int, out b int) + as $$ + select 10,20; + $$ language sql; + + create function f1() + returns void as $$ + declare r record; + begin + r := g1(); + if false then + raise notice '%', r.c; + end if; + end; + $$ language plpgsql; + + select f1(); + select * from plpgsql_check_function('f1()'); + + select f1(); + + drop function f1(); + drop function g1(); + + create function g1(out a int, out b int) + returns setof record as $$ + select * from t1; + $$ language sql; + + create function f1() + returns void as $$ + declare r record; + begin + for r in select * from g1() + loop + raise notice '%', r.c; + end loop; + end; + $$ language plpgsql; + + select f1(); + + select * from plpgsql_check_function('f1()'); + + select f1(); + + create or replace function f1() + returns void as $$ + declare r record; + begin + for r in select * from g1() + loop + r.c := 20; + end loop; + end; + $$ language plpgsql; + + select f1(); + + select * from plpgsql_check_function('f1()'); + + select f1(); + + drop function f1(); + drop function g1(); + + create function f1() + returns int as $$ + declare r int; + begin + if false then + r := a + b; + end if; + return r; + end; + $$ language plpgsql; + + select f1(); + + select * from plpgsql_check_function('f1()'); + + select f1(); + + drop function f1(); + + create or replace function f1() + returns void as $$ + begin + if false then + raise notice '%', 1, 2; + end if; + end; + $$ language plpgsql; + + select f1(); + + select * from plpgsql_check_function('f1()'); + + select f1(); + + drop function f1(); + + create or replace function f1() + returns void as $$ + begin + if false then + raise notice '% %'; + end if; + end; + $$ language plpgsql; + + select f1(); + + select * from plpgsql_check_function('f1()'); + + select f1(); + + drop function f1(); + + create or replace function f1() + returns void as $$ + declare r int[]; + begin + if false then + r[c+10] := 20; + end if; + end; + $$ language plpgsql; + + select f1(); + + select * from plpgsql_check_function('f1()'); + + select f1(); + + drop function f1(); + + create or replace function f1() + returns void as $$ + declare r int; + begin + if false then + r[10] := 20; + end if; + end; + $$ language plpgsql set search_path = public; + + select f1(); + + select * from plpgsql_check_function('f1()'); + + select f1(); + + drop function f1(); + + create type _exception_type as ( + state text, + message text, + detail text); + + create or replace function f1() + returns void as $$ + declare + _exception record; + begin + _exception := NULL::_exception_type; + exception when others then + get stacked diagnostics + _exception.state = RETURNED_SQLSTATE, + _exception.message = MESSAGE_TEXT, + _exception.detail = PG_EXCEPTION_DETAIL, + _exception.hint = PG_EXCEPTION_HINT; + end; + $$ language plpgsql; + + select f1(); + + select * from plpgsql_check_function('f1()'); + + drop function f1(); + + create or replace function f1_trg() + returns trigger as $$ + begin + if new.a > 10 then + raise notice '%', new.b; + raise notice '%', new.c; + end if; + return new; + end; + $$ language plpgsql; + + create trigger t1_f1 before insert on t1 + for each row + execute procedure f1_trg(); + + insert into t1 values(6,30); + + select * from plpgsql_check_function('f1_trg()','t1'); + + insert into t1 values(6,30); + + create or replace function f1_trg() + returns trigger as $$ + begin + new.a := new.a + 10; + new.b := new.b + 10; + new.c := 30; + return new; + end; + $$ language plpgsql; + + -- should to fail + + select * from plpgsql_check_function('f1_trg()','t1'); + + -- should to fail but not crash + insert into t1 values(6,30); + + create or replace function f1_trg() + returns trigger as $$ + begin + new.a := new.a + 10; + new.b := new.b + 10; + return new; + end; + $$ language plpgsql; + + -- ok + select * from plpgsql_check_function('f1_trg()', 't1'); + + -- ok + insert into t1 values(6,30); + + select * from t1; + + drop trigger t1_f1 on t1; + + drop function f1_trg(); + + -- test of showing caret on correct place for multiline queries + create or replace function f1() + returns void as $$ + begin + select + var + from + foo; + end; + $$ language plpgsql; + + select * from plpgsql_check_function('f1()'); + + drop function f1(); + + create or replace function f1() + returns int as $$ + begin + return (select a + from t1 + where hh = 20); + end; + $$ language plpgsql; + + select * from plpgsql_check_function('f1()'); + + create or replace function f1() + returns int as $$ + begin + return (select a + from txxxxxxx + where hh = 20); + end; + $$ language plpgsql; + + select * from plpgsql_check_function('f1()'); + + drop function f1(); + + drop table t1; + drop type _exception_type; + + -- raise warnings when target row has different number of attributies in + -- SELECT INTO statement + + create or replace function f1() + returns void as $$ + declare a1 int; a2 int; + begin + select 10,20 into a1,a2; + end; + $$ language plpgsql; + + -- should be ok + select * from plpgsql_check_function('f1()'); + + create or replace function f1() + returns void as $$ + declare a1 int; a2 int; + begin + select 10,20 into a1; + end; + $$ language plpgsql; + + -- raise warning + select * from plpgsql_check_function('f1()'); + + create or replace function f1() + returns void as $$ + declare a1 int; a2 int; + begin + select 10 into a1,a2; + end; + $$ language plpgsql; + + -- raise warning + select * from plpgsql_check_function('f1()'); + + -- bogus code + set check_function_bodies to off; + + create or replace function f1() + returns void as $$ + adasdfsadf + $$ language plpgsql; + + select * from plpgsql_check_function('f1()'); + + drop function f1(); + + -- check event trigger function + create or replace function f1() returns event_trigger as $$ + BEGIN + RAISE NOTICE 'test_event_trigger: % %', tg_event, tg_tag; + END + $$ language plpgsql; + + + select * from plpgsql_check_function('f1()'); + + -- should fail + create or replace function f1() returns event_trigger as $$ + BEGIN + RAISE NOTICE 'test_event_trigger: % %', tg_event, tg_tagX; + END + $$ language plpgsql; + + select * from plpgsql_check_function('f1()'); + + drop function f1();