*** a/src/pl/plpgsql/src/gram.y --- b/src/pl/plpgsql/src/gram.y *************** *** 293,298 **** static List *read_raise_options(void); --- 293,300 ---- %token K_NOT %token K_NOTICE %token K_NULL + %token K_ON_DEMAND + %token K_ON_START %token K_OPEN %token K_OPTION %token K_OR *************** *** 300,305 **** static List *read_raise_options(void); --- 302,308 ---- %token K_PG_EXCEPTION_CONTEXT %token K_PG_EXCEPTION_DETAIL %token K_PG_EXCEPTION_HINT + %token K_PREPARE_PLANS %token K_PRIOR %token K_QUERY %token K_RAISE *************** *** 354,359 **** comp_option : '#' K_OPTION K_DUMP --- 357,370 ---- { plpgsql_curr_compile->resolve_option = PLPGSQL_RESOLVE_COLUMN; } + | '#' K_PREPARE_PLANS K_ON_DEMAND + { + plpgsql_curr_compile->prepare_plans_option = PLPGSQL_PREPARE_PLANS_ON_DEMAND; + } + | '#' K_PREPARE_PLANS K_ON_START + { + plpgsql_curr_compile->prepare_plans_option = PLPGSQL_PREPARE_PLANS_ON_START; + } ; opt_semi : *************** *** 2232,2241 **** unreserved_keyword : --- 2243,2255 ---- | K_NEXT | K_NO | K_NOTICE + | K_ON_DEMAND + | K_ON_START | K_OPTION | K_PG_EXCEPTION_CONTEXT | K_PG_EXCEPTION_DETAIL | K_PG_EXCEPTION_HINT + | K_PREPARE_PLANS | K_PRIOR | K_QUERY | K_RELATIVE *** a/src/pl/plpgsql/src/pl_comp.c --- b/src/pl/plpgsql/src/pl_comp.c *************** *** 354,359 **** do_compile(FunctionCallInfo fcinfo, --- 354,360 ---- function->fn_cxt = func_cxt; function->out_param_varno = -1; /* set up for no OUT param */ function->resolve_option = plpgsql_variable_conflict; + function->prepare_plans_option = PLPGSQL_PREPARE_PLANS_UNDEF; /* * Initialize the compiler, particularly the namespace stack. The *** a/src/pl/plpgsql/src/pl_exec.c --- b/src/pl/plpgsql/src/pl_exec.c *************** *** 210,215 **** static Portal exec_dynquery_with_params(PLpgSQL_execstate *estate, --- 210,217 ---- PLpgSQL_expr *dynquery, List *params, const char *portalname, int cursorOptions); + static bool expr_prepare_plan(PLpgSQL_stmt *stmt, + PLpgSQL_expr *expr, void *context); /* ---------- * plpgsql_exec_function Called by the call handler for *************** *** 310,315 **** plpgsql_exec_function(PLpgSQL_function *func, FunctionCallInfo fcinfo) --- 312,375 ---- exec_set_found(&estate, false); /* + * Prepare plans for all embeded SQL when it's requested + */ + if ((func->prepare_plans_option == PLPGSQL_PREPARE_PLANS_ON_START) || + (func->prepare_plans_option == PLPGSQL_PREPARE_PLANS_UNDEF && + plpgsql_prepare_plans == PLPGSQL_PREPARE_PLANS_ON_START)) + { + int i; + PLpgSQL_rec *recvars; + + /* + * walker can chanhe a type to REC variable. This can be a unwanted + * change of behave - so we save all REC variables before walker's run + * and later we revert to back all values. + */ + recvars = palloc(sizeof(PLpgSQL_rec) * estate.ndatums); + for (i = 0; i < estate.ndatums; i++) + { + if (estate.datums[i]->dtype == PLPGSQL_DTYPE_REC) + { + PLpgSQL_rec *rec = (PLpgSQL_rec *) estate.datums[i]; + + recvars[i].tup = rec->tup; + recvars[i].tupdesc = rec->tupdesc; + recvars[i].freetup = rec->freetup; + recvars[i].freetupdesc = rec->freetupdesc; + + /* don't release a original record */ + rec->freetup = false; + rec->freetupdesc = false; + } + } + + plpgsql_expr_walker(func, (PLpgSQL_stmt *) func->action, + expr_prepare_plan, + &estate); + for (i = 0; i < estate.ndatums; i++) + { + if (estate.datums[i]->dtype == PLPGSQL_DTYPE_REC) + { + PLpgSQL_rec *rec = (PLpgSQL_rec *) estate.datums[i]; + + if (rec->freetup) + heap_freetuple(rec->tup); + + if (rec->freetupdesc) + FreeTupleDesc(rec->tupdesc); + + rec->tup = recvars[i].tup; + rec->tupdesc = recvars[i].tupdesc; + rec->freetup = recvars[i].freetup; + rec->freetupdesc = recvars[i].freetupdesc; + } + } + + pfree(recvars); + } + + /* * Let the instrumentation plugin peek at this function */ if (*plugin_ptr && (*plugin_ptr)->func_beg) *************** *** 676,681 **** plpgsql_exec_trigger(PLpgSQL_function *func, --- 736,800 ---- exec_set_found(&estate, false); /* + * Prepare plans for all embeded SQL when it's requested + */ + if ((func->prepare_plans_option == PLPGSQL_PREPARE_PLANS_ON_START) || + (func->prepare_plans_option == PLPGSQL_PREPARE_PLANS_UNDEF && + plpgsql_prepare_plans == PLPGSQL_PREPARE_PLANS_ON_START)) + { + int i; + PLpgSQL_rec *recvars; + + /* + * walker can chanhe a type to REC variable. This can be a unwanted + * change of behave - so we save all REC variables before walker's run + * and later we revert to back all values. + */ + recvars = palloc(sizeof(PLpgSQL_rec) * estate.ndatums); + for (i = 0; i < estate.ndatums; i++) + { + if (estate.datums[i]->dtype == PLPGSQL_DTYPE_REC) + { + PLpgSQL_rec *rec = (PLpgSQL_rec *) estate.datums[i]; + + recvars[i].tup = rec->tup; + recvars[i].tupdesc = rec->tupdesc; + recvars[i].freetup = rec->freetup; + recvars[i].freetupdesc = rec->freetupdesc; + + /* don't release a original record */ + rec->freetup = false; + rec->freetupdesc = false; + } + } + + plpgsql_expr_walker(func, (PLpgSQL_stmt *) func->action, + expr_prepare_plan, + &estate); + for (i = 0; i < estate.ndatums; i++) + { + if (estate.datums[i]->dtype == PLPGSQL_DTYPE_REC) + { + PLpgSQL_rec *rec = (PLpgSQL_rec *) estate.datums[i]; + + if (rec->freetup) + heap_freetuple(rec->tup); + + if (rec->freetupdesc) + FreeTupleDesc(rec->tupdesc); + + rec->tup = recvars[i].tup; + rec->tupdesc = recvars[i].tupdesc; + rec->freetup = recvars[i].freetup; + rec->freetupdesc = recvars[i].freetupdesc; + } + } + + pfree(recvars); + + } + + /* * Let the instrumentation plugin peek at this function */ if (*plugin_ptr && (*plugin_ptr)->func_beg) *************** *** 6028,6030 **** exec_dynquery_with_params(PLpgSQL_execstate *estate, --- 6147,6610 ---- return portal; } + + /* + * Sometime we must initialize a unknown record variable with NULL + * of type that is derived from some plan. This is necessary for later + * using a rec variable. Last parameter 'use_element_type' is true, when + * we would to assign a element type of result array. + * + */ + static void + set_rectype_for_query(PLpgSQL_execstate *estate, + PLpgSQL_stmt *stmt, + PLpgSQL_rec *rec, + PLpgSQL_expr *query, + bool use_element_type) + { + bool *nulls; + HeapTuple tup; + CachedPlanSource *plansource = NULL; + const char *err_text = estate->err_text; + + estate->err_text = NULL; + estate->err_stmt = stmt; + + if (rec->freetup) + heap_freetuple(rec->tup); + + if (rec->freetupdesc) + FreeTupleDesc(rec->tupdesc); + + 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); + + rec->tupdesc = CreateTupleDescCopy(plansource->resultDesc); + rec->freetupdesc = true; + } + 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 tupdesc; + + /* result should be a array */ + if (rec->tupdesc->natts != 1) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg_plural("query \"%s\" returned %d column", + "query \"%s\" returned %d columns", + rec->tupdesc->natts, + query->query, + rec->tupdesc->natts))); + + /* check the type of the expression - must be an array */ + elemtype = get_element_type(rec->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(rec->tupdesc->attrs[0]->atttypid)))); + + /* we can't know typmod now */ + tupdesc = lookup_rowtype_tupdesc_noerror(elemtype, -1, true); + if (tupdesc != NULL) + { + if (rec->freetupdesc) + FreeTupleDesc(rec->tupdesc); + rec->tupdesc = CreateTupleDescCopy(tupdesc); + rec->freetupdesc = true; + ReleaseTupleDesc(tupdesc); + } + else + elog(ERROR, "cannot to identify real type for record type variable"); + } + + /* + * When record is assigned to composite type, then + * we should to unpack composite type. + */ + if (rec->tupdesc->tdtypeid == RECORDOID && + rec->tupdesc->tdtypmod == -1 && + rec->tupdesc->natts == 1 && + stmt->cmd_type == PLPGSQL_STMT_ASSIGN) + { + TupleDesc tupdesc; + + tupdesc = lookup_rowtype_tupdesc_noerror(rec->tupdesc->attrs[0]->atttypid, + rec->tupdesc->attrs[0]->atttypmod, + true); + if (tupdesc != NULL) + { + if (rec->freetupdesc) + FreeTupleDesc(rec->tupdesc); + rec->tupdesc = CreateTupleDescCopy(tupdesc); + rec->freetupdesc = true; + ReleaseTupleDesc(tupdesc); + } + } + + /* + * 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 (rec->tupdesc->tdtypeid == RECORDOID && + rec->tupdesc->tdtypmod == -1 && + rec->tupdesc->natts == 1 && + rec->tupdesc->attrs[0]->atttypid == RECORDOID && + rec->tupdesc->attrs[0]->atttypmod == -1) + { + PlannedStmt *_stmt; + Plan *_plan; + TargetEntry *tle; + + /* + * 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. + */ + _stmt = (PlannedStmt *) linitial(plansource->plan->stmt_list); + + if (IsA(_stmt, PlannedStmt) && _stmt->commandType == CMD_SELECT) + { + _plan = _stmt->planTree; + if (IsA(_plan, Result) && list_length(_plan->targetlist) == 1) + { + tle = (TargetEntry *) linitial(_plan->targetlist); + if (((Node *) tle->expr)->type == T_FuncExpr) + { + FuncExpr *fn = (FuncExpr *) tle->expr; + FmgrInfo flinfo; + FunctionCallInfoData fcinfo; + TupleDesc rd; + Oid rt; + + fmgr_info(fn->funcid, &flinfo); + flinfo.fn_expr = (Node *) fn; + fcinfo.flinfo = &flinfo; + + get_call_result_type(&fcinfo, &rt, &rd); + if (rd == NULL) + elog(ERROR, "function does not return composite type is not possible to identify composite type"); + + FreeTupleDesc(rec->tupdesc); + BlessTupleDesc(rd); + + rec->tupdesc = rd; + } + } + } + } + + /* last recheck */ + if (rec->tupdesc->tdtypeid == RECORDOID && + rec->tupdesc->tdtypmod == -1 && + rec->tupdesc->natts == 1 && + rec->tupdesc->attrs[0]->atttypid == RECORDOID && + rec->tupdesc->attrs[0]->atttypmod == -1) + elog(ERROR, "cannot to identify real type for record type variable"); + + /* initialize rec by NULLs - variable should be valid */ + nulls = (bool *) palloc(rec->tupdesc->natts * sizeof(bool)); + memset(nulls, true, rec->tupdesc->natts * sizeof(bool)); + + tup = heap_form_tuple(rec->tupdesc, NULL, nulls); + if (HeapTupleIsValid(tup)) + { + rec->tup = tup; + rec->freetup = true; + } + else + { + rec->tup = NULL; + rec->freetup = false; + } + + estate->err_text = err_text; + } + + static bool + expr_prepare_plan(PLpgSQL_stmt *stmt, PLpgSQL_expr *expr, void *context) + { + PLpgSQL_execstate *estate = (PLpgSQL_execstate *) context; + int cursorOptions = 0; + const char *err_text = estate->err_text; + + /* overwrite a estate variables */ + estate->err_text = NULL; + estate->err_stmt = stmt; + + if (expr == NULL) + return false; + + switch (stmt->cmd_type) + { + case PLPGSQL_STMT_OPEN: + { + PLpgSQL_stmt_open *stmt_open = (PLpgSQL_stmt_open *) stmt; + PLpgSQL_var *curvar = (PLpgSQL_var *) estate->datums[stmt_open->curvar]; + + cursorOptions = curvar->cursor_options; + } + break; + + case PLPGSQL_STMT_FORC: + { + PLpgSQL_stmt_forc *stmt_forc = (PLpgSQL_stmt_forc *) stmt; + PLpgSQL_var *curvar = (PLpgSQL_var *) estate->datums[stmt_forc->curvar]; + + /* + * change a cursorOption only whenn this call is related to + * curvar->cursor_explicit_expr + */ + if (curvar->cursor_explicit_expr == expr) + cursorOptions = curvar->cursor_options; + } + break; + } + + /* + * If first time through, create a plan for this expression. + */ + if (expr->plan == NULL) + { + exec_prepare_plan(estate, expr, cursorOptions); + } + + /* + * very common practic in PLpgSQL is is using a record type. But any using of + * untyped record breaks a check. A solution is an prediction of record type based + * on plans - a following switch covers all PLpgSQL statements where a record + * variable can be assigned. + * + * when record is target of dynamic SQL statement, then raise exception + * + */ + switch (stmt->cmd_type) + { + case PLPGSQL_STMT_ASSIGN: + { + PLpgSQL_stmt_assign *stmt_assign = (PLpgSQL_stmt_assign *) stmt; + PLpgSQL_datum *target = (estate->datums[stmt_assign->varno]); + + if (target->dtype == PLPGSQL_DTYPE_REC) + { + set_rectype_for_query(estate, stmt, + (PLpgSQL_rec *) target, + stmt_assign->expr, + false); + } + } + break; + + case PLPGSQL_STMT_EXECSQL: + { + PLpgSQL_stmt_execsql *stmt_execsql = (PLpgSQL_stmt_execsql *) stmt; + ListCell *l; + + stmt_execsql->mod_stmt = false; + foreach(l, expr->plan->plancache_list) + { + CachedPlanSource *plansource = (CachedPlanSource *) lfirst(l); + ListCell *l2; + + foreach(l2, plansource->plan->stmt_list) + { + PlannedStmt *p = (PlannedStmt *) lfirst(l2); + + if (IsA(p, PlannedStmt) && + p->canSetTag) + { + if (p->commandType == CMD_INSERT || + p->commandType == CMD_UPDATE || + p->commandType == CMD_DELETE) + stmt_execsql->mod_stmt = true; + } + } + } + + if (stmt_execsql->rec != NULL) + { + set_rectype_for_query(estate, stmt, + (PLpgSQL_rec *) (estate->datums[stmt_execsql->rec->dno]), + stmt_execsql->sqlstmt, + false); + } + } + break; + + case PLPGSQL_STMT_FETCH: + { + PLpgSQL_stmt_fetch *stmt_fetch = (PLpgSQL_stmt_fetch *) stmt; + + /* fetch can not determinate a record datatype for refcursors */ + if (stmt_fetch->rec != NULL) + { + PLpgSQL_var *curvar = (PLpgSQL_var *)( estate->datums[stmt_fetch->curvar]); + PLpgSQL_rec *rec = (PLpgSQL_rec *) (estate->datums[stmt_fetch->rec->dno]); + + if (curvar->cursor_explicit_expr == NULL) + elog(ERROR, "cannot to determinate record type for refcursor"); + + set_rectype_for_query(estate, stmt, + rec, + curvar->cursor_explicit_expr, + false); + } + } + break; + + case PLPGSQL_STMT_FORS: + { + PLpgSQL_stmt_fors *stmt_fors = (PLpgSQL_stmt_fors *) stmt; + + if (stmt_fors->rec != NULL) + { + set_rectype_for_query(estate, stmt, + (PLpgSQL_rec *) (estate->datums[stmt_fors->rec->dno]), + stmt_fors->query, + false); + } + } + break; + + case PLPGSQL_STMT_FORC: + { + PLpgSQL_stmt_forc *stmt_forc = (PLpgSQL_stmt_forc *) stmt; + PLpgSQL_var *curvar = (PLpgSQL_var *) (estate->datums[stmt_forc->curvar]); + + if (stmt_forc->rec != NULL && curvar->cursor_explicit_expr == expr) + { + PLpgSQL_rec *rec = (PLpgSQL_rec *) (estate->datums[stmt_forc->rec->dno]); + + set_rectype_for_query(estate, stmt, + rec, + curvar->cursor_explicit_expr, + false); + } + } + break; + + case PLPGSQL_STMT_FOREACH_A: + { + PLpgSQL_stmt_foreach_a *stmt_foreach_a = (PLpgSQL_stmt_foreach_a *) stmt; + PLpgSQL_datum *loop_var = estate->datums[stmt_foreach_a->varno]; + + if (loop_var->dtype == PLPGSQL_DTYPE_REC) + { + set_rectype_for_query(estate, stmt, + (PLpgSQL_rec *) loop_var, + stmt_foreach_a->expr, + true); + } + } + break; + + case PLPGSQL_STMT_CASE: + { + PLpgSQL_stmt_case *stmt_case = (PLpgSQL_stmt_case *) stmt; + TupleDesc tupdesc; + Oid result_oid; + + /* + * this is special case - a result type of expression should to + * overwrite a expected int datatype. + */ + if (stmt_case->t_expr == expr) + { + CachedPlanSource *plansource = NULL; + const char *err_text = estate->err_text; + + estate->err_text = NULL; + estate->err_stmt = stmt; + + + if (expr->plan != NULL) + { + SPIPlanPtr plan = expr->plan; + PLpgSQL_var *t_var = (PLpgSQL_var *) estate->datums[stmt_case->t_varno]; + + 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); + + if (tupdesc->natts != 1) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg_plural("query \"%s\" returned %d column", + "query \"%s\" returned %d columns", + tupdesc->natts, + expr->query, + tupdesc->natts))); + + 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); + FreeTupleDesc(tupdesc); + } + else + elog(ERROR, "there are no plan for query: \"%s\"", + expr->query); + + estate->err_text = err_text; + } + } + break; + + case PLPGSQL_STMT_DYNEXECUTE: + { + PLpgSQL_stmt_dynexecute *stmt_dynexecute = (PLpgSQL_stmt_dynexecute *) stmt; + + if (stmt_dynexecute->into && stmt_dynexecute->rec != NULL) + elog(ERROR, "cannot to determine a result of dynamic SQL"); + } + break; + + case PLPGSQL_STMT_DYNFORS: + { + PLpgSQL_stmt_dynfors *stmt_dynfors = (PLpgSQL_stmt_dynfors *) stmt; + + if (stmt_dynfors->rec != NULL) + elog(ERROR, "cannot to determinate a result of dynamic SQL"); + } + break; + } + + estate->err_text = err_text; + + return false; + } *** a/src/pl/plpgsql/src/pl_funcs.c --- b/src/pl/plpgsql/src/pl_funcs.c *************** *** 292,681 **** plpgsql_getdiag_kindname(int kind) return "unknown"; } ! ! /********************************************************************** ! * Release memory when a PL/pgSQL function is no longer needed * ! * The code for recursing through the function tree is really only ! * needed to locate PLpgSQL_expr nodes, which may contain references ! * to saved SPI Plans that must be freed. The function tree itself, ! * along with subsidiary data, is freed in one swoop by freeing the ! * function's permanent memory context. ! **********************************************************************/ ! static void free_stmt(PLpgSQL_stmt *stmt); ! static void free_block(PLpgSQL_stmt_block *block); ! static void free_assign(PLpgSQL_stmt_assign *stmt); ! static void free_if(PLpgSQL_stmt_if *stmt); ! static void free_case(PLpgSQL_stmt_case *stmt); ! static void free_loop(PLpgSQL_stmt_loop *stmt); ! static void free_while(PLpgSQL_stmt_while *stmt); ! static void free_fori(PLpgSQL_stmt_fori *stmt); ! static void free_fors(PLpgSQL_stmt_fors *stmt); ! static void free_forc(PLpgSQL_stmt_forc *stmt); ! static void free_foreach_a(PLpgSQL_stmt_foreach_a *stmt); ! static void free_exit(PLpgSQL_stmt_exit *stmt); ! static void free_return(PLpgSQL_stmt_return *stmt); ! static void free_return_next(PLpgSQL_stmt_return_next *stmt); ! static void free_return_query(PLpgSQL_stmt_return_query *stmt); ! static void free_raise(PLpgSQL_stmt_raise *stmt); ! static void free_execsql(PLpgSQL_stmt_execsql *stmt); ! static void free_dynexecute(PLpgSQL_stmt_dynexecute *stmt); ! static void free_dynfors(PLpgSQL_stmt_dynfors *stmt); ! static void free_getdiag(PLpgSQL_stmt_getdiag *stmt); ! static void free_open(PLpgSQL_stmt_open *stmt); ! static void free_fetch(PLpgSQL_stmt_fetch *stmt); ! static void free_close(PLpgSQL_stmt_close *stmt); ! static void free_perform(PLpgSQL_stmt_perform *stmt); ! static void free_expr(PLpgSQL_expr *expr); ! static void ! free_stmt(PLpgSQL_stmt *stmt) { switch ((enum PLpgSQL_stmt_types) stmt->cmd_type) { case PLPGSQL_STMT_BLOCK: ! free_block((PLpgSQL_stmt_block *) stmt); ! break; case PLPGSQL_STMT_ASSIGN: ! free_assign((PLpgSQL_stmt_assign *) stmt); ! break; case PLPGSQL_STMT_IF: ! free_if((PLpgSQL_stmt_if *) stmt); ! break; case PLPGSQL_STMT_CASE: ! free_case((PLpgSQL_stmt_case *) stmt); ! break; case PLPGSQL_STMT_LOOP: ! free_loop((PLpgSQL_stmt_loop *) stmt); ! break; case PLPGSQL_STMT_WHILE: ! free_while((PLpgSQL_stmt_while *) stmt); ! break; case PLPGSQL_STMT_FORI: ! free_fori((PLpgSQL_stmt_fori *) stmt); ! break; case PLPGSQL_STMT_FORS: ! free_fors((PLpgSQL_stmt_fors *) stmt); ! break; case PLPGSQL_STMT_FORC: ! free_forc((PLpgSQL_stmt_forc *) stmt); ! break; ! case PLPGSQL_STMT_FOREACH_A: ! free_foreach_a((PLpgSQL_stmt_foreach_a *) stmt); ! break; ! case PLPGSQL_STMT_EXIT: ! free_exit((PLpgSQL_stmt_exit *) stmt); ! break; ! case PLPGSQL_STMT_RETURN: ! free_return((PLpgSQL_stmt_return *) stmt); ! break; ! case PLPGSQL_STMT_RETURN_NEXT: ! free_return_next((PLpgSQL_stmt_return_next *) stmt); ! break; ! case PLPGSQL_STMT_RETURN_QUERY: ! free_return_query((PLpgSQL_stmt_return_query *) stmt); ! break; ! case PLPGSQL_STMT_RAISE: ! free_raise((PLpgSQL_stmt_raise *) stmt); ! break; ! case PLPGSQL_STMT_EXECSQL: ! free_execsql((PLpgSQL_stmt_execsql *) stmt); ! break; ! case PLPGSQL_STMT_DYNEXECUTE: ! free_dynexecute((PLpgSQL_stmt_dynexecute *) stmt); ! break; case PLPGSQL_STMT_DYNFORS: ! free_dynfors((PLpgSQL_stmt_dynfors *) stmt); ! break; ! case PLPGSQL_STMT_GETDIAG: ! free_getdiag((PLpgSQL_stmt_getdiag *) stmt); ! break; ! case PLPGSQL_STMT_OPEN: ! free_open((PLpgSQL_stmt_open *) stmt); ! break; ! case PLPGSQL_STMT_FETCH: ! free_fetch((PLpgSQL_stmt_fetch *) stmt); ! break; ! case PLPGSQL_STMT_CLOSE: ! free_close((PLpgSQL_stmt_close *) stmt); ! break; ! case PLPGSQL_STMT_PERFORM: ! free_perform((PLpgSQL_stmt_perform *) stmt); ! break; ! default: ! elog(ERROR, "unrecognized cmd_type: %d", stmt->cmd_type); ! break; ! } ! } ! static void ! free_stmts(List *stmts) ! { ! ListCell *s; ! foreach(s, stmts) ! { ! free_stmt((PLpgSQL_stmt *) lfirst(s)); ! } ! } ! static void ! free_block(PLpgSQL_stmt_block *block) ! { ! free_stmts(block->body); ! if (block->exceptions) ! { ! ListCell *e; ! foreach(e, block->exceptions->exc_list) ! { ! PLpgSQL_exception *exc = (PLpgSQL_exception *) lfirst(e); ! free_stmts(exc->action); ! } ! } ! } ! static void ! free_assign(PLpgSQL_stmt_assign *stmt) ! { ! free_expr(stmt->expr); ! } ! static void ! free_if(PLpgSQL_stmt_if *stmt) ! { ! free_expr(stmt->cond); ! free_stmts(stmt->true_body); ! free_stmts(stmt->false_body); ! } ! static void ! free_case(PLpgSQL_stmt_case *stmt) ! { ! ListCell *l; ! free_expr(stmt->t_expr); ! foreach(l, stmt->case_when_list) ! { ! PLpgSQL_case_when *cwt = (PLpgSQL_case_when *) lfirst(l); ! free_expr(cwt->expr); ! free_stmts(cwt->stmts); ! } ! free_stmts(stmt->else_stmts); ! } ! static void ! free_loop(PLpgSQL_stmt_loop *stmt) ! { ! free_stmts(stmt->body); ! } ! static void ! free_while(PLpgSQL_stmt_while *stmt) ! { ! free_expr(stmt->cond); ! free_stmts(stmt->body); ! } ! static void ! free_fori(PLpgSQL_stmt_fori *stmt) ! { ! free_expr(stmt->lower); ! free_expr(stmt->upper); ! free_expr(stmt->step); ! free_stmts(stmt->body); ! } ! static void ! free_fors(PLpgSQL_stmt_fors *stmt) ! { ! free_stmts(stmt->body); ! free_expr(stmt->query); ! } ! static void ! free_forc(PLpgSQL_stmt_forc *stmt) ! { ! free_stmts(stmt->body); ! free_expr(stmt->argquery); ! } ! static void ! free_foreach_a(PLpgSQL_stmt_foreach_a *stmt) ! { ! free_expr(stmt->expr); ! free_stmts(stmt->body); ! } ! static void ! free_open(PLpgSQL_stmt_open *stmt) ! { ! ListCell *lc; ! free_expr(stmt->argquery); ! free_expr(stmt->query); ! free_expr(stmt->dynquery); ! foreach(lc, stmt->params) ! { ! free_expr((PLpgSQL_expr *) lfirst(lc)); ! } ! } ! static void ! free_fetch(PLpgSQL_stmt_fetch *stmt) ! { ! free_expr(stmt->expr); ! } ! static void ! free_close(PLpgSQL_stmt_close *stmt) ! { ! } ! static void ! free_perform(PLpgSQL_stmt_perform *stmt) ! { ! free_expr(stmt->expr); ! } ! static void ! free_exit(PLpgSQL_stmt_exit *stmt) ! { ! free_expr(stmt->cond); ! } ! static void ! free_return(PLpgSQL_stmt_return *stmt) ! { ! free_expr(stmt->expr); ! } ! static void ! free_return_next(PLpgSQL_stmt_return_next *stmt) ! { ! free_expr(stmt->expr); ! } ! static void ! free_return_query(PLpgSQL_stmt_return_query *stmt) ! { ! ListCell *lc; ! free_expr(stmt->query); ! free_expr(stmt->dynquery); ! foreach(lc, stmt->params) ! { ! free_expr((PLpgSQL_expr *) lfirst(lc)); ! } ! } ! static void ! free_raise(PLpgSQL_stmt_raise *stmt) ! { ! ListCell *lc; ! foreach(lc, stmt->params) ! { ! free_expr((PLpgSQL_expr *) lfirst(lc)); ! } ! foreach(lc, stmt->options) ! { ! PLpgSQL_raise_option *opt = (PLpgSQL_raise_option *) lfirst(lc); ! free_expr(opt->expr); ! } ! } ! static void ! free_execsql(PLpgSQL_stmt_execsql *stmt) ! { ! free_expr(stmt->sqlstmt); ! } ! static void ! free_dynexecute(PLpgSQL_stmt_dynexecute *stmt) ! { ! ListCell *lc; ! free_expr(stmt->query); ! foreach(lc, stmt->params) ! { ! free_expr((PLpgSQL_expr *) lfirst(lc)); ! } ! } ! static void ! free_dynfors(PLpgSQL_stmt_dynfors *stmt) ! { ! ListCell *lc; ! free_stmts(stmt->body); ! free_expr(stmt->query); ! foreach(lc, stmt->params) ! { ! free_expr((PLpgSQL_expr *) lfirst(lc)); } } ! static void ! free_getdiag(PLpgSQL_stmt_getdiag *stmt) ! { ! } ! static void ! free_expr(PLpgSQL_expr *expr) { if (expr && expr->plan) { SPI_freeplan(expr->plan); expr->plan = NULL; } } void plpgsql_free_function_memory(PLpgSQL_function *func) { - int i; - /* Better not call this on an in-use function */ Assert(func->use_count == 0); ! /* Release plans associated with variable declarations */ ! for (i = 0; i < func->ndatums; i++) ! { ! PLpgSQL_datum *d = func->datums[i]; ! ! switch (d->dtype) ! { ! case PLPGSQL_DTYPE_VAR: ! { ! PLpgSQL_var *var = (PLpgSQL_var *) d; - free_expr(var->default_val); - free_expr(var->cursor_explicit_expr); - } - break; - case PLPGSQL_DTYPE_ROW: - break; - case PLPGSQL_DTYPE_REC: - break; - case PLPGSQL_DTYPE_RECFIELD: - break; - case PLPGSQL_DTYPE_ARRAYELEM: - free_expr(((PLpgSQL_arrayelem *) d)->subscript); - break; - default: - elog(ERROR, "unrecognized data type: %d", d->dtype); - } - } func->ndatums = 0; - - /* Release plans in statement tree */ - if (func->action) - free_block(func->action); func->action = NULL; /* --- 292,653 ---- return "unknown"; } ! /* ! * call a plpgsql_expr_walker for any statement in list * ! */ ! static bool ! plpgsql_expr_walker_list(PLpgSQL_function *func, List *stmts, ! bool (*expr_walker)(), ! void *context) ! { ! ListCell *lc; + foreach(lc, stmts) + { + PLpgSQL_stmt *stmt = (PLpgSQL_stmt *) lfirst(lc); ! if (plpgsql_expr_walker(func, stmt, expr_walker, context)) ! return true; ! } ! return false; ! } ! ! /* ! * walk over all expressions inside statements tree ! * ! * stmt_walker is function called for every stmt and should be NULL ! * ! */ ! bool ! plpgsql_expr_walker(PLpgSQL_function *func, ! PLpgSQL_stmt *stmt, ! bool (*expr_walker)(), ! void *context) { + ListCell *l; + + if (stmt == NULL) + return false; + 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]]; ! ! switch(d->dtype) ! { ! case PLPGSQL_DTYPE_VAR: ! { ! PLpgSQL_var *var = (PLpgSQL_var *) d; ! ! if (expr_walker(stmt, var->default_val, context)) ! return true; ! ! /* ! * theoretically there is place for walk over var->cursor_explicit_expr, ! * but we would not to call process too early. In this moment a some ! * record parameters should be unknown. So we will wait on better moment ! * ! * if (expr_walker(stmt, var->cursor_explicit_expr, context)) ! * return true; ! */ ! } ! break; ! case PLPGSQL_DTYPE_ROW: ! case PLPGSQL_DTYPE_REC: ! case PLPGSQL_DTYPE_RECFIELD: ! break; ! case PLPGSQL_DTYPE_ARRAYELEM: ! if (expr_walker(stmt, ((PLpgSQL_arrayelem *) d)->subscript, context)) ! return true; ! default: ! elog(ERROR, "unrecognized data type: %d", d->dtype); ! } ! } ! ! if (plpgsql_expr_walker_list(func, stmt_block->body, expr_walker, context)) ! return true; ! ! if (stmt_block->exceptions) ! { ! foreach(l, stmt_block->exceptions->exc_list) ! { ! if (plpgsql_expr_walker_list(func, ((PLpgSQL_exception *) lfirst(l))->action, ! expr_walker, ! context)) ! return true; ! } ! } ! ! return false; ! } ! case PLPGSQL_STMT_ASSIGN: ! return expr_walker(stmt, ((PLpgSQL_stmt_assign *) stmt)->expr, context); ! case PLPGSQL_STMT_IF: ! { ! PLpgSQL_stmt_if *stmt_if = (PLpgSQL_stmt_if *) stmt; ! ! if (expr_walker(stmt, stmt_if->cond, context)) ! return true; ! ! if (plpgsql_expr_walker_list(func, stmt_if->true_body, expr_walker, context)) ! return true; ! ! return plpgsql_expr_walker_list(func, stmt_if->false_body, expr_walker, context); ! } ! case PLPGSQL_STMT_CASE: ! { ! PLpgSQL_stmt_case *stmt_case = (PLpgSQL_stmt_case *) stmt; ! ! if (expr_walker(stmt, stmt_case->t_expr, context)) ! return true; ! ! foreach(l, stmt_case->case_when_list) ! { ! PLpgSQL_case_when *cwt = (PLpgSQL_case_when *) lfirst(l); ! ! if (expr_walker(stmt, cwt->expr, context)) ! return true; ! ! if (plpgsql_expr_walker_list(func, cwt->stmts, expr_walker, context)) ! return true; ! } ! ! return plpgsql_expr_walker_list(func, stmt_case->else_stmts, expr_walker, context); ! } ! case PLPGSQL_STMT_LOOP: ! return plpgsql_expr_walker_list(func, ((PLpgSQL_stmt_loop *) stmt)->body, expr_walker, context); ! case PLPGSQL_STMT_WHILE: ! { ! PLpgSQL_stmt_while *stmt_while = (PLpgSQL_stmt_while *) stmt; ! ! if (expr_walker(stmt, stmt_while->cond, context)) ! return true; ! ! return plpgsql_expr_walker_list(func, stmt_while->body, expr_walker, context); ! } ! case PLPGSQL_STMT_FORI: ! { ! PLpgSQL_stmt_fori *stmt_fori = (PLpgSQL_stmt_fori *) stmt; ! ! if (expr_walker(stmt, stmt_fori->lower, context)) ! return true; ! ! if (expr_walker(stmt, stmt_fori->upper, context)) ! return true; ! ! if (expr_walker(stmt, stmt_fori->step, context)) ! return true; ! ! return plpgsql_expr_walker_list(func, stmt_fori->body, expr_walker, context); ! } ! case PLPGSQL_STMT_FORS: ! { ! PLpgSQL_stmt_fors *stmt_fors = (PLpgSQL_stmt_fors *) stmt; ! ! if (expr_walker(stmt, stmt_fors->query, context)) ! return true; ! ! return plpgsql_expr_walker_list(func, stmt_fors->body, expr_walker, context); ! } ! case PLPGSQL_STMT_FORC: ! { ! PLpgSQL_stmt_forc *stmt_forc = (PLpgSQL_stmt_forc *) stmt; ! PLpgSQL_var *var = (PLpgSQL_var *) func->datums[stmt_forc->curvar]; ! ! if (expr_walker(stmt, stmt_forc->argquery, context)) ! return true; ! ! if (expr_walker(stmt, var->cursor_explicit_expr, context)) ! return true; ! ! return plpgsql_expr_walker_list(func, stmt_forc->body, expr_walker, context); ! } ! case PLPGSQL_STMT_DYNFORS: ! { ! PLpgSQL_stmt_dynfors * stmt_dynfors = (PLpgSQL_stmt_dynfors *) stmt; ! if (expr_walker(stmt, stmt_dynfors->query, context)) ! return true; ! foreach(l, stmt_dynfors->params) ! { ! if (expr_walker(stmt, (PLpgSQL_expr *) lfirst(l), context)) ! return true; ! } ! return plpgsql_expr_walker_list(func, stmt_dynfors->body, expr_walker, context); ! } ! case PLPGSQL_STMT_FOREACH_A: ! { ! PLpgSQL_stmt_foreach_a *stmt_foreach_a = (PLpgSQL_stmt_foreach_a *) stmt; ! if (expr_walker(stmt, stmt_foreach_a->expr, context)) ! return true; ! return plpgsql_expr_walker_list(func, stmt_foreach_a->body, expr_walker, context); ! } ! case PLPGSQL_STMT_EXIT: ! return expr_walker(stmt, ((PLpgSQL_stmt_exit *) stmt)->cond, context); ! case PLPGSQL_STMT_PERFORM: ! return expr_walker(stmt, ((PLpgSQL_stmt_perform *) stmt)->expr, context); ! case PLPGSQL_STMT_RETURN: ! return expr_walker(stmt, ((PLpgSQL_stmt_return *) stmt)->expr, context); ! case PLPGSQL_STMT_RETURN_NEXT: ! return expr_walker(stmt, ((PLpgSQL_stmt_return_next *) stmt)->expr, context); ! case PLPGSQL_STMT_RETURN_QUERY: ! { ! PLpgSQL_stmt_return_query *stmt_rq = (PLpgSQL_stmt_return_query *) stmt; ! if (expr_walker(stmt, stmt_rq->query, context)) ! return true; ! if (expr_walker(stmt, stmt_rq->dynquery, context)) ! return true; ! foreach(l, stmt_rq->params) ! { ! if (expr_walker(stmt, (PLpgSQL_expr *) lfirst(l), context)) ! return true; ! } ! return false; ! } ! case PLPGSQL_STMT_RAISE: ! { ! PLpgSQL_stmt_raise *stmt_raise = (PLpgSQL_stmt_raise *) stmt; ! foreach(l, stmt_raise->params) ! { ! if (expr_walker(stmt, (PLpgSQL_expr *) lfirst(l), context)) ! return true; ! } ! foreach(l, stmt_raise->options) ! { ! if (expr_walker(stmt, ((PLpgSQL_raise_option *) lfirst(l))->expr, context)) ! return true; ! } ! return false; ! } ! case PLPGSQL_STMT_EXECSQL: ! return expr_walker(stmt, ((PLpgSQL_stmt_execsql *) stmt)->sqlstmt, context); ! case PLPGSQL_STMT_DYNEXECUTE: ! { ! PLpgSQL_stmt_dynexecute *stmt_dynexecute = (PLpgSQL_stmt_dynexecute *) stmt; ! if (expr_walker(stmt, stmt_dynexecute->query, context)) ! return true; ! foreach(l, stmt_dynexecute->params) ! { ! if (expr_walker(stmt, (PLpgSQL_expr *) lfirst(l), context)) ! return true; ! } ! return false; ! } ! break; ! case PLPGSQL_STMT_GETDIAG: ! return false; ! case PLPGSQL_STMT_OPEN: ! { ! PLpgSQL_stmt_open *stmt_open = (PLpgSQL_stmt_open *) stmt; ! PLpgSQL_var *var = (PLpgSQL_var *) func->datums[stmt_open->curvar]; ! if (expr_walker(stmt, var->cursor_explicit_expr, context)) ! return true; ! if (expr_walker(stmt, stmt_open->query, context)) ! return true; ! if (expr_walker(stmt, stmt_open->dynquery, context)) ! return true; ! if (expr_walker(stmt, stmt_open->argquery, context)) ! return true; ! foreach(l, stmt_open->params) ! { ! if (expr_walker(stmt, (PLpgSQL_expr *) lfirst(l), context)) ! return true; ! } ! return false; ! } ! case PLPGSQL_STMT_FETCH: ! case PLPGSQL_STMT_CLOSE: ! return false; ! default: ! elog(ERROR, "unrecognized cmd_type: %d", stmt->cmd_type); ! return false; /* be compiler quite */ } } ! /********************************************************************** ! * Release memory when a PL/pgSQL function is no longer needed ! * ! * The code for recursing through the function tree is really only ! * needed to locate PLpgSQL_expr nodes, which may contain references ! * to saved SPI Plans that must be freed. The function tree itself, ! * along with subsidiary data, is freed in one swoop by freeing the ! * function's permanent memory context. ! **********************************************************************/ ! static bool ! free_expression(PLpgSQL_stmt *stmt, PLpgSQL_expr *expr, void *context) { if (expr && expr->plan) { SPI_freeplan(expr->plan); expr->plan = NULL; } + return false; } void plpgsql_free_function_memory(PLpgSQL_function *func) { /* Better not call this on an in-use function */ Assert(func->use_count == 0); ! /* Release plans in statement tree */ ! plpgsql_expr_walker(func, (PLpgSQL_stmt *) func->action, ! free_expression, ! NULL); func->ndatums = 0; func->action = NULL; /* *** a/src/pl/plpgsql/src/pl_handler.c --- b/src/pl/plpgsql/src/pl_handler.c *************** *** 34,40 **** static const struct config_enum_entry variable_conflict_options[] = { --- 34,47 ---- {NULL, 0, false} }; + static const struct config_enum_entry prepare_plans_options[] = { + {"on_demand", PLPGSQL_PREPARE_PLANS_ON_DEMAND, false}, + {"on_start", PLPGSQL_PREPARE_PLANS_ON_START, false}, + {NULL, 0, false} + }; + int plpgsql_variable_conflict = PLPGSQL_RESOLVE_ERROR; + int plpgsql_prepare_plans = PLPGSQL_PREPARE_PLANS_ON_DEMAND; /* Hook for plugins */ PLpgSQL_plugin **plugin_ptr = NULL; *************** *** 65,70 **** _PG_init(void) --- 72,87 ---- PGC_SUSET, 0, NULL, NULL, NULL); + DefineCustomEnumVariable("plpgsql.prepare_plans", + gettext_noop("Sets timing of preparation plans of embeded SQL."), + NULL, + &plpgsql_prepare_plans, + PLPGSQL_PREPARE_PLANS_ON_DEMAND, + prepare_plans_options, + PGC_SUSET, 0, + NULL, NULL, NULL); + + EmitWarningsOnPlaceholders("plpgsql"); plpgsql_HashTableInit(); *** a/src/pl/plpgsql/src/pl_scanner.c --- b/src/pl/plpgsql/src/pl_scanner.c *************** *** 129,138 **** static const ScanKeyword unreserved_keywords[] = { --- 129,141 ---- PG_KEYWORD("next", K_NEXT, UNRESERVED_KEYWORD) PG_KEYWORD("no", K_NO, UNRESERVED_KEYWORD) PG_KEYWORD("notice", K_NOTICE, UNRESERVED_KEYWORD) + PG_KEYWORD("on_demand", K_ON_DEMAND, UNRESERVED_KEYWORD) + PG_KEYWORD("on_start", K_ON_START, UNRESERVED_KEYWORD) PG_KEYWORD("option", K_OPTION, UNRESERVED_KEYWORD) PG_KEYWORD("pg_exception_context", K_PG_EXCEPTION_CONTEXT, UNRESERVED_KEYWORD) PG_KEYWORD("pg_exception_detail", K_PG_EXCEPTION_DETAIL, UNRESERVED_KEYWORD) PG_KEYWORD("pg_exception_hint", K_PG_EXCEPTION_HINT, UNRESERVED_KEYWORD) + PG_KEYWORD("prepare_plans", K_PREPARE_PLANS, UNRESERVED_KEYWORD) PG_KEYWORD("prior", K_PRIOR, UNRESERVED_KEYWORD) PG_KEYWORD("query", K_QUERY, UNRESERVED_KEYWORD) PG_KEYWORD("relative", K_RELATIVE, UNRESERVED_KEYWORD) *** a/src/pl/plpgsql/src/plpgsql.h --- b/src/pl/plpgsql/src/plpgsql.h *************** *** 157,162 **** typedef enum --- 157,173 ---- PLPGSQL_RESOLVE_COLUMN /* prefer table column to plpgsql var */ } PLpgSQL_resolve_option; + /* -------- + * Behavioral modes for plpgsql execution plan timing + * -------- + */ + typedef enum + { + PLPGSQL_PREPARE_PLANS_UNDEF, + PLPGSQL_PREPARE_PLANS_ON_DEMAND, /* prepare plan when it is necessary (default) */ + PLPGSQL_PREPARE_PLANS_ON_START /* prepare all plans on start */ + } PLpgSQL_prepare_plans_option; + /********************************************************************** * Node and structure definitions *************** *** 700,705 **** typedef struct PLpgSQL_function --- 711,717 ---- int tg_argv_varno; PLpgSQL_resolve_option resolve_option; + PLpgSQL_prepare_plans_option prepare_plans_option; int ndatums; PLpgSQL_datum **datums; *************** *** 836,841 **** typedef enum --- 848,854 ---- extern IdentifierLookup plpgsql_IdentifierLookup; extern int plpgsql_variable_conflict; + extern int plpgsql_prepare_plans; extern bool plpgsql_check_syntax; extern bool plpgsql_DumpExecTree; *************** *** 930,935 **** extern PLpgSQL_nsitem *plpgsql_ns_lookup(PLpgSQL_nsitem *ns_cur, bool localmode, --- 943,953 ---- extern PLpgSQL_nsitem *plpgsql_ns_lookup_label(PLpgSQL_nsitem *ns_cur, const char *name); + extern bool plpgsql_expr_walker(PLpgSQL_function *func, + PLpgSQL_stmt *stmt, + bool (*expr_walker)(), + void *context); + /* ---------- * Other functions in pl_funcs.c * ---------- *** a/src/test/regress/expected/plpgsql.out --- b/src/test/regress/expected/plpgsql.out *************** *** 1,3 **** --- 1,5 ---- + load 'plpgsql'; + set plpgsql.prepare_plans to on_start; -- -- PLPGSQL -- *************** *** 2410,2415 **** drop function missing_return_expr(); --- 2412,2418 ---- create table eifoo (i integer, y integer); create type eitype as (i integer, y integer); create or replace function execute_into_test(varchar) returns record as $$ + #prepare_plans on_demand declare _r record; _rt eifoo%rowtype; *************** *** 2469,2475 **** LINE 1: SELECT sqlstate --- 2472,2486 ---- ^ QUERY: SELECT sqlstate CONTEXT: PL/pgSQL function "excpt_test2" line 5 at RAISE + /* + * this example has blocked a plan preparation on start + * because we would to demonstrate a nested trapping errors. + * When we use a plans generated on start, then we raise + * exception before we start a protected section, because + * exception is raised when plan is generated. + */ create function excpt_test3() returns void as $$ + #prepare_plans on_demand begin begin raise exception 'user exception'; *************** *** 2527,2532 **** insert into conttesttbl(v) values(20); --- 2538,2544 ---- insert into conttesttbl(v) values(30); insert into conttesttbl(v) values(40); create function continue_test1() returns void as $$ + #prepare_plans on_demand declare _i integer = 0; _r record; begin raise notice '---1---'; *************** *** 2852,2857 **** select footest(); --- 2864,2870 ---- ERROR: query returned more than one row CONTEXT: PL/pgSQL function "footest" line 5 at SQL statement create or replace function footest() returns void as $$ + #prepare_plans on_demand declare x record; begin -- should work *************** *** 2866,2871 **** NOTICE: x.f1 = 5, x.f2 = 6 --- 2879,2885 ---- (1 row) create or replace function footest() returns void as $$ + #prepare_plans on_demand declare x record; begin -- this should work since EXECUTE isn't as picky *************** *** 2925,2930 **** select footest(); --- 2939,2945 ---- ERROR: query returned more than one row CONTEXT: PL/pgSQL function "footest" line 5 at SQL statement create or replace function footest() returns void as $$ + #prepare_plans on_demand declare x record; begin -- should work *************** *** 2939,2944 **** NOTICE: x.f1 = 3, x.f2 = 4 --- 2954,2960 ---- (1 row) create or replace function footest() returns void as $$ + #prepare_plans on_demand declare x record; begin -- should fail, no rows *************** *** 2947,2954 **** begin end$$ language plpgsql; select footest(); ERROR: query returned no rows ! CONTEXT: PL/pgSQL function "footest" line 5 at EXECUTE statement create or replace function footest() returns void as $$ declare x record; begin -- should fail, too many rows --- 2963,2971 ---- end$$ language plpgsql; select footest(); ERROR: query returned no rows ! CONTEXT: PL/pgSQL function "footest" line 6 at EXECUTE statement create or replace function footest() returns void as $$ + #prepare_plans on_demand declare x record; begin -- should fail, too many rows *************** *** 2957,2963 **** begin end$$ language plpgsql; select footest(); ERROR: query returned more than one row ! CONTEXT: PL/pgSQL function "footest" line 5 at EXECUTE statement drop function footest(); -- test scrollable cursor support create function sc_test() returns setof integer as $$ --- 2974,2980 ---- end$$ language plpgsql; select footest(); ERROR: query returned more than one row ! CONTEXT: PL/pgSQL function "footest" line 6 at EXECUTE statement drop function footest(); -- test scrollable cursor support create function sc_test() returns setof integer as $$ *************** *** 3027,3032 **** select * from sc_test(); --- 3044,3050 ---- (5 rows) create or replace function sc_test() returns setof integer as $$ + #prepare_plans on_demand declare c refcursor; x integer; *************** *** 3049,3054 **** select * from sc_test(); --- 3067,3073 ---- (3 rows) create or replace function sc_test() returns setof integer as $$ + #prepare_plans on_demand declare c refcursor; x integer; *************** *** 3220,3225 **** NOTICE: 6 --- 3239,3245 ---- drop function exc_using(int, text); create or replace function exc_using(int) returns void as $$ + #prepare_plans on_demand declare c refcursor; i int; *************** *** 3352,3357 **** select * from forc_test; --- 3372,3378 ---- -- same, with a cursor whose portal name doesn't match variable name create or replace function forc01() returns void as $$ + #prepare_plans on_demand declare c refcursor := 'fooled_ya'; r record; *************** *** 3685,3690 **** drop function stacked_diagnostics_test(); --- 3706,3712 ---- -- check cases where implicit SQLSTATE variable could be confused with -- SQLSTATE as a keyword, cf bug #5524 create or replace function raise_test() returns void as $$ + #prepare_plans on_demand begin perform 1/0; exception *************** *** 3772,3778 **** select case_test(13); -- fails --- 3794,3807 ---- ERROR: case not found HINT: CASE statement is missing ELSE part. CONTEXT: PL/pgSQL function "case_test" line 5 at CASE + /* + * should to have blocked early plan preparation, because + * a case_test(6) is evaluated too early - it should be + * replaced by constant, and then it cannot to demonstrate + * a expected efect. + */ create or replace function catch() returns void as $$ + #prepare_plans on_demand begin raise notice '%', case_test(6); exception *************** *** 4131,4143 **** LINE 4: return 'foo\\bar\041baz'; ^ HINT: Use the escape string syntax for backslashes, e.g., E'\\'. select strtest(); - NOTICE: foo\bar!baz WARNING: nonstandard use of \\ in a string literal LINE 1: SELECT 'foo\\bar\041baz' ^ HINT: Use the escape string syntax for backslashes, e.g., E'\\'. QUERY: SELECT 'foo\\bar\041baz' CONTEXT: PL/pgSQL function "strtest" line 4 at RETURN strtest ------------- foo\bar!baz --- 4160,4172 ---- ^ HINT: Use the escape string syntax for backslashes, e.g., E'\\'. select strtest(); WARNING: nonstandard use of \\ in a string literal LINE 1: SELECT 'foo\\bar\041baz' ^ HINT: Use the escape string syntax for backslashes, e.g., E'\\'. QUERY: SELECT 'foo\\bar\041baz' CONTEXT: PL/pgSQL function "strtest" line 4 at RETURN + NOTICE: foo\bar!baz strtest ------------- foo\bar!baz *** a/src/test/regress/sql/plpgsql.sql --- b/src/test/regress/sql/plpgsql.sql *************** *** 1,3 **** --- 1,6 ---- + load 'plpgsql'; + set plpgsql.prepare_plans to on_start; + -- -- PLPGSQL -- *************** *** 2052,2057 **** create table eifoo (i integer, y integer); --- 2055,2061 ---- create type eitype as (i integer, y integer); create or replace function execute_into_test(varchar) returns record as $$ + #prepare_plans on_demand declare _r record; _rt eifoo%rowtype; *************** *** 2099,2105 **** end; $$ language plpgsql; --- 2103,2117 ---- -- should fail select excpt_test2(); + /* + * this example has blocked a plan preparation on start + * because we would to demonstrate a nested trapping errors. + * When we use a plans generated on start, then we raise + * exception before we start a protected section, because + * exception is raised when plan is generated. + */ create function excpt_test3() returns void as $$ + #prepare_plans on_demand begin begin raise exception 'user exception'; *************** *** 2146,2151 **** insert into conttesttbl(v) values(30); --- 2158,2164 ---- insert into conttesttbl(v) values(40); create function continue_test1() returns void as $$ + #prepare_plans on_demand declare _i integer = 0; _r record; begin raise notice '---1---'; *************** *** 2381,2386 **** end$$ language plpgsql; --- 2394,2400 ---- select footest(); create or replace function footest() returns void as $$ + #prepare_plans on_demand declare x record; begin -- should work *************** *** 2391,2396 **** end$$ language plpgsql; --- 2405,2411 ---- select footest(); create or replace function footest() returns void as $$ + #prepare_plans on_demand declare x record; begin -- this should work since EXECUTE isn't as picky *************** *** 2433,2438 **** end$$ language plpgsql; --- 2448,2454 ---- select footest(); create or replace function footest() returns void as $$ + #prepare_plans on_demand declare x record; begin -- should work *************** *** 2443,2448 **** end$$ language plpgsql; --- 2459,2465 ---- select footest(); create or replace function footest() returns void as $$ + #prepare_plans on_demand declare x record; begin -- should fail, no rows *************** *** 2453,2458 **** end$$ language plpgsql; --- 2470,2476 ---- select footest(); create or replace function footest() returns void as $$ + #prepare_plans on_demand declare x record; begin -- should fail, too many rows *************** *** 2518,2523 **** $$ language plpgsql; --- 2536,2542 ---- select * from sc_test(); create or replace function sc_test() returns setof integer as $$ + #prepare_plans on_demand declare c refcursor; x integer; *************** *** 2535,2540 **** $$ language plpgsql; --- 2554,2560 ---- select * from sc_test(); create or replace function sc_test() returns setof integer as $$ + #prepare_plans on_demand declare c refcursor; x integer; *************** *** 2657,2662 **** select exc_using(5, 'foobar'); --- 2677,2683 ---- drop function exc_using(int, text); create or replace function exc_using(int) returns void as $$ + #prepare_plans on_demand declare c refcursor; i int; *************** *** 2738,2743 **** select * from forc_test; --- 2759,2765 ---- -- same, with a cursor whose portal name doesn't match variable name create or replace function forc01() returns void as $$ + #prepare_plans on_demand declare c refcursor := 'fooled_ya'; r record; *************** *** 3014,3019 **** drop function stacked_diagnostics_test(); --- 3036,3042 ---- -- check cases where implicit SQLSTATE variable could be confused with -- SQLSTATE as a keyword, cf bug #5524 create or replace function raise_test() returns void as $$ + #prepare_plans on_demand begin perform 1/0; exception *************** *** 3059,3065 **** select case_test(11); --- 3082,3095 ---- select case_test(12); select case_test(13); -- fails + /* + * should to have blocked early plan preparation, because + * a case_test(6) is evaluated too early - it should be + * replaced by constant, and then it cannot to demonstrate + * a expected efect. + */ create or replace function catch() returns void as $$ + #prepare_plans on_demand begin raise notice '%', case_test(6); exception