diff options
Diffstat (limited to 'src')
| -rw-r--r-- | src/backend/executor/spi.c | 208 | ||||
| -rw-r--r-- | src/include/executor/spi.h | 11 |
2 files changed, 179 insertions, 40 deletions
diff --git a/src/backend/executor/spi.c b/src/backend/executor/spi.c index 3cd2e574100..42187fe43ad 100644 --- a/src/backend/executor/spi.c +++ b/src/backend/executor/spi.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/executor/spi.c,v 1.191 2008/03/26 18:48:59 alvherre Exp $ + * $PostgreSQL: pgsql/src/backend/executor/spi.c,v 1.192 2008/04/01 03:09:30 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -35,13 +35,17 @@ static int _SPI_stack_depth = 0; /* allocated size of _SPI_stack */ static int _SPI_connected = -1; static int _SPI_curid = -1; -static void _SPI_prepare_plan(const char *src, SPIPlanPtr plan); +static void _SPI_prepare_plan(const char *src, SPIPlanPtr plan, + ParamListInfo boundParams); -static int _SPI_execute_plan(SPIPlanPtr plan, - Datum *Values, const char *Nulls, +static int _SPI_execute_plan(SPIPlanPtr plan, ParamListInfo paramLI, Snapshot snapshot, Snapshot crosscheck_snapshot, bool read_only, bool fire_triggers, long tcount); +static ParamListInfo _SPI_convert_params(int nargs, Oid *argtypes, + Datum *Values, const char *Nulls, + int pflags); + static int _SPI_pquery(QueryDesc *queryDesc, bool fire_triggers, long tcount); static void _SPI_error_callback(void *arg); @@ -313,9 +317,9 @@ SPI_execute(const char *src, bool read_only, long tcount) plan.magic = _SPI_PLAN_MAGIC; plan.cursor_options = 0; - _SPI_prepare_plan(src, &plan); + _SPI_prepare_plan(src, &plan, NULL); - res = _SPI_execute_plan(&plan, NULL, NULL, + res = _SPI_execute_plan(&plan, NULL, InvalidSnapshot, InvalidSnapshot, read_only, true, tcount); @@ -348,7 +352,9 @@ SPI_execute_plan(SPIPlanPtr plan, Datum *Values, const char *Nulls, return res; res = _SPI_execute_plan(plan, - Values, Nulls, + _SPI_convert_params(plan->nargs, plan->argtypes, + Values, Nulls, + 0), InvalidSnapshot, InvalidSnapshot, read_only, true, tcount); @@ -394,7 +400,9 @@ SPI_execute_snapshot(SPIPlanPtr plan, return res; res = _SPI_execute_plan(plan, - Values, Nulls, + _SPI_convert_params(plan->nargs, plan->argtypes, + Values, Nulls, + 0), snapshot, crosscheck_snapshot, read_only, fire_triggers, tcount); @@ -402,6 +410,57 @@ SPI_execute_snapshot(SPIPlanPtr plan, return res; } +/* + * SPI_execute_with_args -- plan and execute a query with supplied arguments + * + * This is functionally comparable to SPI_prepare followed by + * SPI_execute_plan, except that since we know the plan will be used only + * once, we can tell the planner to rely on the parameter values as constants. + * This eliminates potential performance disadvantages compared to + * inserting the parameter values directly into the query text. + */ +int +SPI_execute_with_args(const char *src, + int nargs, Oid *argtypes, + Datum *Values, const char *Nulls, + bool read_only, long tcount) +{ + int res; + _SPI_plan plan; + ParamListInfo paramLI; + + if (src == NULL || nargs < 0 || tcount < 0) + return SPI_ERROR_ARGUMENT; + + if (nargs > 0 && (argtypes == NULL || Values == NULL)) + return SPI_ERROR_PARAM; + + res = _SPI_begin_call(true); + if (res < 0) + return res; + + memset(&plan, 0, sizeof(_SPI_plan)); + plan.magic = _SPI_PLAN_MAGIC; + plan.cursor_options = 0; + plan.nargs = nargs; + plan.argtypes = argtypes; + + paramLI = _SPI_convert_params(nargs, argtypes, + Values, Nulls, + PARAM_FLAG_CONST); + + _SPI_prepare_plan(src, &plan, paramLI); + + /* We don't need to copy the plan since it will be thrown away anyway */ + + res = _SPI_execute_plan(&plan, paramLI, + InvalidSnapshot, InvalidSnapshot, + read_only, true, tcount); + + _SPI_end_call(true); + return res; +} + SPIPlanPtr SPI_prepare(const char *src, int nargs, Oid *argtypes) { @@ -431,7 +490,7 @@ SPI_prepare_cursor(const char *src, int nargs, Oid *argtypes, plan.nargs = nargs; plan.argtypes = argtypes; - _SPI_prepare_plan(src, &plan); + _SPI_prepare_plan(src, &plan, NULL); /* copy plan to procedure context */ result = _SPI_copy_plan(&plan, _SPI_current->procCxt); @@ -1055,6 +1114,64 @@ SPI_cursor_open(const char *name, SPIPlanPtr plan, /* + * SPI_cursor_open_with_args() + * + * Parse and plan a query and open it as a portal. Like SPI_execute_with_args, + * we can tell the planner to rely on the parameter values as constants, + * because the plan will only be used once. + */ +Portal +SPI_cursor_open_with_args(const char *name, + const char *src, + int nargs, Oid *argtypes, + Datum *Values, const char *Nulls, + bool read_only, int cursorOptions) +{ + Portal result; + _SPI_plan plan; + ParamListInfo paramLI; + + if (src == NULL || nargs < 0) + elog(ERROR, "SPI_cursor_open_with_args called with invalid arguments"); + + if (nargs > 0 && (argtypes == NULL || Values == NULL)) + elog(ERROR, "SPI_cursor_open_with_args called with missing parameters"); + + SPI_result = _SPI_begin_call(true); + if (SPI_result < 0) + elog(ERROR, "SPI_cursor_open_with_args called while not connected"); + + memset(&plan, 0, sizeof(_SPI_plan)); + plan.magic = _SPI_PLAN_MAGIC; + plan.cursor_options = cursorOptions; + plan.nargs = nargs; + plan.argtypes = argtypes; + + paramLI = _SPI_convert_params(nargs, argtypes, + Values, Nulls, + PARAM_FLAG_CONST); + + _SPI_prepare_plan(src, &plan, paramLI); + + /* We needn't copy the plan; SPI_cursor_open will do so */ + + /* Adjust stack so that SPI_cursor_open doesn't complain */ + _SPI_curid--; + + /* SPI_cursor_open expects to be called in procedure memory context */ + _SPI_procmem(); + + result = SPI_cursor_open(name, &plan, Values, Nulls, read_only); + + /* And clean up */ + _SPI_curid++; + _SPI_end_call(true); + + return result; +} + + +/* * SPI_cursor_find() * * Find the portal of an existing open cursor @@ -1376,14 +1493,17 @@ spi_printtup(TupleTableSlot *slot, DestReceiver *self) * Parse and plan a querystring. * * At entry, plan->argtypes, plan->nargs, and plan->cursor_options must be - * valid. + * valid. If boundParams isn't NULL then it represents parameter values + * that are made available to the planner (as either estimates or hard values + * depending on their PARAM_FLAG_CONST marking). The boundParams had better + * match the param types embedded in the plan! * * Results are stored into *plan (specifically, plan->plancache_list). * Note however that the result trees are all in CurrentMemoryContext * and need to be copied somewhere to survive. */ static void -_SPI_prepare_plan(const char *src, SPIPlanPtr plan) +_SPI_prepare_plan(const char *src, SPIPlanPtr plan, ParamListInfo boundParams) { List *raw_parsetree_list; List *plancache_list; @@ -1422,7 +1542,8 @@ _SPI_prepare_plan(const char *src, SPIPlanPtr plan) /* Need a copyObject here to keep parser from modifying raw tree */ stmt_list = pg_analyze_and_rewrite(copyObject(parsetree), src, argtypes, nargs); - stmt_list = pg_plan_queries(stmt_list, cursor_options, NULL, false); + stmt_list = pg_plan_queries(stmt_list, cursor_options, + boundParams, false); plansource = (CachedPlanSource *) palloc0(sizeof(CachedPlanSource)); cplan = (CachedPlan *) palloc0(sizeof(CachedPlan)); @@ -1465,7 +1586,7 @@ _SPI_prepare_plan(const char *src, SPIPlanPtr plan) * tcount: execution tuple-count limit, or 0 for none */ static int -_SPI_execute_plan(SPIPlanPtr plan, Datum *Values, const char *Nulls, +_SPI_execute_plan(SPIPlanPtr plan, ParamListInfo paramLI, Snapshot snapshot, Snapshot crosscheck_snapshot, bool read_only, bool fire_triggers, long tcount) { @@ -1480,34 +1601,9 @@ _SPI_execute_plan(SPIPlanPtr plan, Datum *Values, const char *Nulls, saveActiveSnapshot = ActiveSnapshot; PG_TRY(); { - ListCell *lc1; ErrorContextCallback spierrcontext; - int nargs = plan->nargs; - ParamListInfo paramLI; CachedPlan *cplan = NULL; - - /* Convert parameters to form wanted by executor */ - if (nargs > 0) - { - int k; - - /* sizeof(ParamListInfoData) includes the first array element */ - paramLI = (ParamListInfo) palloc(sizeof(ParamListInfoData) + - (nargs - 1) *sizeof(ParamExternData)); - paramLI->numParams = nargs; - - for (k = 0; k < nargs; k++) - { - ParamExternData *prm = ¶mLI->params[k]; - - prm->value = Values[k]; - prm->isnull = (Nulls && Nulls[k] == 'n'); - prm->pflags = 0; - prm->ptype = plan->argtypes[k]; - } - } - else - paramLI = NULL; + ListCell *lc1; /* * Setup error traceback support for ereport() @@ -1723,6 +1819,40 @@ fail: return my_res; } +/* + * Convert query parameters to form wanted by planner and executor + */ +static ParamListInfo +_SPI_convert_params(int nargs, Oid *argtypes, + Datum *Values, const char *Nulls, + int pflags) +{ + ParamListInfo paramLI; + + if (nargs > 0) + { + int i; + + /* sizeof(ParamListInfoData) includes the first array element */ + paramLI = (ParamListInfo) palloc(sizeof(ParamListInfoData) + + (nargs - 1) *sizeof(ParamExternData)); + paramLI->numParams = nargs; + + for (i = 0; i < nargs; i++) + { + ParamExternData *prm = ¶mLI->params[i]; + + prm->value = Values[i]; + prm->isnull = (Nulls && Nulls[i] == 'n'); + prm->pflags = pflags; + prm->ptype = argtypes[i]; + } + } + else + paramLI = NULL; + return paramLI; +} + static int _SPI_pquery(QueryDesc *queryDesc, bool fire_triggers, long tcount) { diff --git a/src/include/executor/spi.h b/src/include/executor/spi.h index e64fc39cba5..9d3f65b8cf0 100644 --- a/src/include/executor/spi.h +++ b/src/include/executor/spi.h @@ -6,7 +6,7 @@ * Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/executor/spi.h,v 1.65 2008/01/01 19:45:57 momjian Exp $ + * $PostgreSQL: pgsql/src/include/executor/spi.h,v 1.66 2008/04/01 03:09:30 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -105,6 +105,10 @@ extern int SPI_execute_snapshot(SPIPlanPtr plan, Snapshot snapshot, Snapshot crosscheck_snapshot, bool read_only, bool fire_triggers, long tcount); +extern int SPI_execute_with_args(const char *src, + int nargs, Oid *argtypes, + Datum *Values, const char *Nulls, + bool read_only, long tcount); extern SPIPlanPtr SPI_prepare(const char *src, int nargs, Oid *argtypes); extern SPIPlanPtr SPI_prepare_cursor(const char *src, int nargs, Oid *argtypes, int cursorOptions); @@ -136,6 +140,11 @@ extern void SPI_freetuptable(SPITupleTable *tuptable); extern Portal SPI_cursor_open(const char *name, SPIPlanPtr plan, Datum *Values, const char *Nulls, bool read_only); +extern Portal SPI_cursor_open_with_args(const char *name, + const char *src, + int nargs, Oid *argtypes, + Datum *Values, const char *Nulls, + bool read_only, int cursorOptions); extern Portal SPI_cursor_find(const char *name); extern void SPI_cursor_fetch(Portal portal, bool forward, long count); extern void SPI_cursor_move(Portal portal, bool forward, long count); |