Hello
I enhanced DO statement syntax to allowing a parameters. Syntax is
relative simple:
do ([varname] vartype := value, ...) $$ ... $$
It allows to pass a content of psql variables to inline code block to
allows more easy scripting
\set schema 'public'
do(text := :'schema') $$
declare r record;
begin
for r in
select * from information_schema.tables where table_schema = $1
loop
raise notice '>>> table %', r.table_name;
end loop;
end $$;
NOTICE: >>> table t
NOTICE: >>> table t1
DO
ToDo:
* doesn't allows SubLinks :(
pa...@postgres:5432=# do(text := (SELECT :'schema')) $$ declare r
record; begin for r in select * from information_schema.tables where
table_schema = $1 loop raise notice '>>> table %', r.table_name; end
loop; end $$;
ERROR: XX000: unrecognized node type: 315
LOCATION: ExecInitExpr, execQual.c:4868
ideas, notes, comments??
Regards
Pavel Stehule
*** ./src/backend/commands/functioncmds.c.orig 2010-02-26 03:00:39.000000000 +0100
--- ./src/backend/commands/functioncmds.c 2010-07-04 07:50:16.175265641 +0200
***************
*** 47,53 ****
--- 47,55 ----
#include "catalog/pg_type_fn.h"
#include "commands/defrem.h"
#include "commands/proclang.h"
+ #include "executor/executor.h"
#include "miscadmin.h"
+ #include "optimizer/planmain.h"
#include "optimizer/var.h"
#include "parser/parse_coerce.h"
#include "parser/parse_expr.h"
***************
*** 55,60 ****
--- 57,63 ----
#include "parser/parse_type.h"
#include "utils/acl.h"
#include "utils/builtins.h"
+ #include "utils/datum.h"
#include "utils/fmgroids.h"
#include "utils/guc.h"
#include "utils/lsyscache.h"
***************
*** 1928,1934 ****
* Execute inline procedural-language code
*/
void
! ExecuteDoStmt(DoStmt *stmt)
{
InlineCodeBlock *codeblock = makeNode(InlineCodeBlock);
ListCell *arg;
--- 1931,1937 ----
* Execute inline procedural-language code
*/
void
! ExecuteDoStmt(DoStmt *stmt, const char *queryString)
{
InlineCodeBlock *codeblock = makeNode(InlineCodeBlock);
ListCell *arg;
***************
*** 1939,1944 ****
--- 1942,1948 ----
Oid laninline;
HeapTuple languageTuple;
Form_pg_language languageStruct;
+ ParseState *pstate;
/* Process options we got from gram.y */
foreach(arg, stmt->args)
***************
*** 1973,1978 ****
--- 1977,2088 ----
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("no inline code specified")));
+ /* Transform parameters - when are used */
+ if (stmt->params != NIL)
+ {
+ ListCell *param;
+ EState *estate;
+ int nparams;
+ int i = 0;
+
+ nparams = list_length(stmt->params);
+
+ codeblock->nparams = nparams;
+ codeblock->dvalues = (Datum *) palloc(nparams * sizeof(Datum));
+ codeblock->nulls = (bool *) palloc(nparams * sizeof(bool));
+ codeblock->names = (char **) palloc(nparams * sizeof(char *));
+ codeblock->typoids = (Oid *) palloc(nparams * sizeof(Oid));
+
+ /* prepare pstate for parse analysis of param exprs */
+ pstate = make_parsestate(NULL);
+ pstate->p_sourcetext = queryString;
+
+ foreach(param, stmt->params)
+ {
+ FunctionParameter *p = (FunctionParameter *) lfirst(param);
+ Oid toid;
+ Type typtup;
+ TypeName *t = p->argType;
+ MemoryContext oldcontext;
+ ExprState *exprstate;
+ Node *expr;
+ Datum const_val;
+ bool const_is_null;
+ int16 resultTypLen;
+ bool resultTypByVal;
+
+ codeblock->names[i] = p->name;
+
+ if (t->setof)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_FUNCTION_DEFINITION),
+ errmsg("inline procedural code cannot accept set arguments")));
+
+ typtup = LookupTypeName(NULL, t, NULL);
+ if (typtup)
+ {
+ if (!((Form_pg_type) GETSTRUCT(typtup))->typisdefined)
+ ereport(NOTICE,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("argument type %s is only a shell",
+ TypeNameToString(t))));
+
+ toid = typeTypeId(typtup);
+ ReleaseSysCache(typtup);
+ codeblock->typoids[i] = toid;
+ }
+ else
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("type %s does not exist",
+ TypeNameToString(t))));
+ toid = InvalidOid; /* keep compiler quiet */
+ }
+
+ estate = CreateExecutorState();
+
+ expr = transformExpr(pstate, p->defexpr);
+ expr = coerce_to_specific_type(pstate, expr,
+ toid, "DEFAULT");
+
+ oldcontext = MemoryContextSwitchTo(estate->es_query_cxt);
+
+ fix_opfuncids(expr);
+
+ /* Prepare expr for execution */
+ exprstate = ExecPrepareExpr((Expr *) expr, estate);
+
+ /* And evaluaye it */
+ const_val = ExecEvalExprSwitchContext(exprstate,
+ GetPerTupleExprContext(estate),
+ &const_is_null, NULL);
+ /* Get info needed about result datatype */
+ get_typlenbyval(toid, &resultTypLen, &resultTypByVal);
+
+ /* Get back outer memory context */
+ MemoryContextSwitchTo(oldcontext);
+
+ if (!const_is_null)
+ const_val = datumCopy(const_val, resultTypByVal, resultTypLen);
+
+ codeblock->nulls[i] = const_is_null;
+ codeblock->dvalues[i] = const_val;
+
+ FreeExecutorState(estate);
+ i += 1;
+ }
+ }
+ else
+ {
+ codeblock->nparams = 0;
+ codeblock->dvalues = NULL;
+ codeblock->nulls = NULL;
+ codeblock->names = NULL;
+ codeblock->typoids = NULL;
+ }
+
+
/* if LANGUAGE option wasn't specified, use the default */
if (language_item)
language = strVal(language_item->arg);
*** ./src/backend/nodes/copyfuncs.c.orig 2010-02-26 03:00:43.000000000 +0100
--- ./src/backend/nodes/copyfuncs.c 2010-06-27 13:45:22.340830586 +0200
***************
*** 2680,2685 ****
--- 2680,2686 ----
DoStmt *newnode = makeNode(DoStmt);
COPY_NODE_FIELD(args);
+ COPY_NODE_FIELD(params);
return newnode;
}
*** ./src/backend/nodes/equalfuncs.c.orig 2010-02-26 03:00:43.000000000 +0100
--- ./src/backend/nodes/equalfuncs.c 2010-06-27 13:46:01.856829383 +0200
***************
*** 1242,1247 ****
--- 1242,1248 ----
_equalDoStmt(DoStmt *a, DoStmt *b)
{
COMPARE_NODE_FIELD(args);
+ COMPARE_NODE_FIELD(params);
return true;
}
*** ./src/backend/parser/gram.y.orig 2010-06-13 19:43:12.000000000 +0200
--- ./src/backend/parser/gram.y 2010-07-04 07:55:13.124266470 +0200
***************
*** 437,442 ****
--- 437,444 ----
opt_frame_clause frame_extent frame_bound
%type <str> opt_existing_window_name
+ %type <node> do_arg
+ %type <list> do_args
/*
* Non-keyword token types. These are hard-wired into the "flex" lexer.
***************
*** 5445,5454 ****
*
*****************************************************************************/
! DoStmt: DO dostmt_opt_list
{
DoStmt *n = makeNode(DoStmt);
n->args = $2;
$$ = (Node *)n;
}
;
--- 5447,5465 ----
*
*****************************************************************************/
! DoStmt:
! DO '(' do_args ')' dostmt_opt_list
! {
! DoStmt *n = makeNode(DoStmt);
! n->args = $5;
! n->params = $3;
! $$ = (Node *)n;
! }
! | DO dostmt_opt_list
{
DoStmt *n = makeNode(DoStmt);
n->args = $2;
+ n->params = NIL;
$$ = (Node *)n;
}
;
***************
*** 5469,5474 ****
--- 5480,5519 ----
}
;
+ do_args:
+ do_arg
+ {
+ $$ = list_make1($1);
+ }
+ | do_args ',' do_arg
+ {
+ $$ = lappend($1, $3);
+ }
+ ;
+
+ do_arg:
+ func_type COLON_EQUALS a_expr
+ {
+ FunctionParameter *n = makeNode(FunctionParameter);
+ n->name = NULL;
+ n->argType = $1;
+ n->mode = FUNC_PARAM_IN;
+ n->defexpr = $3;
+ $$ = (Node *) n;
+ }
+ | param_name func_type COLON_EQUALS a_expr
+ {
+ FunctionParameter *n = makeNode(FunctionParameter);
+ n->name = $1;
+ n->argType = $2;
+ n->mode = FUNC_PARAM_IN;
+ n->defexpr = $4;
+ $$ = (Node *) n;
+ }
+ ;
+
+
+
/*****************************************************************************
*
* CREATE CAST / DROP CAST
*** ./src/backend/tcop/utility.c.orig 2010-02-26 03:01:04.000000000 +0100
--- ./src/backend/tcop/utility.c 2010-06-27 16:20:12.075291309 +0200
***************
*** 930,936 ****
break;
case T_DoStmt:
! ExecuteDoStmt((DoStmt *) parsetree);
break;
case T_CreatedbStmt:
--- 930,936 ----
break;
case T_DoStmt:
! ExecuteDoStmt((DoStmt *) parsetree, queryString);
break;
case T_CreatedbStmt:
*** ./src/include/commands/defrem.h.orig 2010-02-26 03:01:24.000000000 +0100
--- ./src/include/commands/defrem.h 2010-06-27 16:21:19.119051968 +0200
***************
*** 66,72 ****
extern void DropCastById(Oid castOid);
extern void AlterFunctionNamespace(List *name, List *argtypes, bool isagg,
const char *newschema);
! extern void ExecuteDoStmt(DoStmt *stmt);
/* commands/operatorcmds.c */
extern void DefineOperator(List *names, List *parameters);
--- 66,72 ----
extern void DropCastById(Oid castOid);
extern void AlterFunctionNamespace(List *name, List *argtypes, bool isagg,
const char *newschema);
! extern void ExecuteDoStmt(DoStmt *stmt, const char *queryString);
/* commands/operatorcmds.c */
extern void DefineOperator(List *names, List *parameters);
*** ./src/include/nodes/parsenodes.h.orig 2010-02-26 03:01:25.000000000 +0100
--- ./src/include/nodes/parsenodes.h 2010-07-03 20:56:28.828265897 +0200
***************
*** 1991,1996 ****
--- 1991,1997 ----
{
NodeTag type;
List *args; /* List of DefElem nodes */
+ List *params; /* List of Function parameters */
} DoStmt;
typedef struct InlineCodeBlock
***************
*** 1999,2004 ****
--- 2000,2010 ----
char *source_text; /* source text of anonymous code block */
Oid langOid; /* OID of selected language */
bool langIsTrusted; /* trusted property of the language */
+ int nparams;
+ Datum *dvalues; /* Values of parameters if they are */
+ bool *nulls; /* nulls of parameters if they are */
+ char **names; /* used names for parameters if they are */
+ Oid *typoids; /* array of parameter types if they are */
} InlineCodeBlock;
/* ----------------------
*** ./src/pl/plpgsql/src/pl_comp.c.orig 2010-02-26 03:01:34.000000000 +0100
--- ./src/pl/plpgsql/src/pl_comp.c 2010-07-04 07:58:19.441266296 +0200
***************
*** 727,733 ****
* ----------
*/
PLpgSQL_function *
! plpgsql_compile_inline(char *proc_source)
{
char *func_name = "inline_code_block";
PLpgSQL_function *function;
--- 727,733 ----
* ----------
*/
PLpgSQL_function *
! plpgsql_compile_inline(InlineCodeBlock *codeblock)
{
char *func_name = "inline_code_block";
PLpgSQL_function *function;
***************
*** 737,742 ****
--- 737,744 ----
int parse_rc;
MemoryContext func_cxt;
int i;
+ char *proc_source = codeblock->source_text;
+ int *in_arg_varnos = NULL;
/*
* Setup the scanner input and error info. We assume that this function
***************
*** 812,818 ****
plpgsql_build_datatype(BOOLOID, -1),
true);
function->found_varno = var->dno;
!
/*
* Now parse the function's text
*/
--- 814,868 ----
plpgsql_build_datatype(BOOLOID, -1),
true);
function->found_varno = var->dno;
!
! /*
! * Complete the function's info
! */
! function->fn_nargs = codeblock->nparams;
! in_arg_varnos = (int *) palloc(codeblock->nparams * sizeof(int));
!
! /*
! * Create variables for inline outer parameters
! */
! for(i = 0; i < codeblock->nparams; i++)
! {
! char buf[32];
! PLpgSQL_type *argtype;
! PLpgSQL_variable *argvariable;
! int argitemtype;
!
! /* Create $n name for variable */
! snprintf(buf, sizeof(buf), "$%d", i + 1);
!
! /* Create datatype info */
! argtype = plpgsql_build_datatype(codeblock->typoids[i], -1);
!
! /* Disallow pseudotype argument */
! if (argtype->ttype != PLPGSQL_TTYPE_SCALAR &&
! argtype->ttype != PLPGSQL_TTYPE_ROW)
! ereport(ERROR,
! (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! errmsg("PL/pgSQL inline code cannot accept type %s",
! format_type_be(codeblock->typoids[i]))));
!
! /* Build variable and add to datum list */
! argvariable = plpgsql_build_variable(buf, 0,
! argtype, false);
! if (argvariable->dtype == PLPGSQL_DTYPE_ROW)
! argitemtype = PLPGSQL_NSTYPE_VAR;
! else
! argitemtype = PLPGSQL_NSTYPE_ROW;
!
! in_arg_varnos[i] = argvariable->dno;
!
! /* Add to namespace */
! plpgsql_ns_additem(argitemtype, argvariable->dno, buf);
!
! if (codeblock->names[i] != NULL)
! plpgsql_ns_additem(argitemtype, argvariable->dno,
! codeblock->names[i]);
! }
!
/*
* Now parse the function's text
*/
***************
*** 830,839 ****
if (function->fn_rettype == VOIDOID)
add_dummy_return(function);
! /*
! * Complete the function's info
! */
! function->fn_nargs = 0;
function->ndatums = plpgsql_nDatums;
function->datums = palloc(sizeof(PLpgSQL_datum *) * plpgsql_nDatums);
for (i = 0; i < plpgsql_nDatums; i++)
--- 880,888 ----
if (function->fn_rettype == VOIDOID)
add_dummy_return(function);
! for (i = 0; i < function->fn_nargs; i++)
! function->fn_argvarnos[i] = in_arg_varnos[i];
!
function->ndatums = plpgsql_nDatums;
function->datums = palloc(sizeof(PLpgSQL_datum *) * plpgsql_nDatums);
for (i = 0; i < plpgsql_nDatums; i++)
***************
*** 849,854 ****
--- 898,904 ----
MemoryContextSwitchTo(compile_tmp_cxt);
compile_tmp_cxt = NULL;
+
return function;
}
*** ./src/pl/plpgsql/src/pl_handler.c.orig 2010-02-26 03:01:35.000000000 +0100
--- ./src/pl/plpgsql/src/pl_handler.c 2010-07-04 07:27:03.842263509 +0200
***************
*** 160,165 ****
--- 160,166 ----
FmgrInfo flinfo;
Datum retval;
int rc;
+ int i;
Assert(IsA(codeblock, InlineCodeBlock));
***************
*** 170,183 ****
elog(ERROR, "SPI_connect failed: %s", SPI_result_code_string(rc));
/* Compile the anonymous code block */
! func = plpgsql_compile_inline(codeblock->source_text);
/*
* Set up a fake fcinfo with just enough info to satisfy
* 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;
--- 171,191 ----
elog(ERROR, "SPI_connect failed: %s", SPI_result_code_string(rc));
/* Compile the anonymous code block */
! func = plpgsql_compile_inline(codeblock);
/*
* Set up a fake fcinfo with just enough info to satisfy
* plpgsql_exec_function(). In particular note that this sets things up
* with no arguments passed.
*/
! InitFunctionCallInfoData(fake_fcinfo, &flinfo, codeblock->nparams, NULL, NULL);
!
! for (i = 0; i < codeblock->nparams; i++)
! {
! fake_fcinfo.arg[i] = codeblock->dvalues[i];
! fake_fcinfo.argnull[i] = codeblock->nulls[i];
! }
!
MemSet(&flinfo, 0, sizeof(flinfo));
fake_fcinfo.flinfo = &flinfo;
flinfo.fn_oid = InvalidOid;
*** ./src/pl/plpgsql/src/plpgsql.h.orig 2010-02-26 03:01:35.000000000 +0100
--- ./src/pl/plpgsql/src/plpgsql.h 2010-07-03 20:49:40.992266524 +0200
***************
*** 832,838 ****
*/
extern PLpgSQL_function *plpgsql_compile(FunctionCallInfo fcinfo,
bool forValidator);
! extern PLpgSQL_function *plpgsql_compile_inline(char *proc_source);
extern void plpgsql_parser_setup(struct ParseState *pstate,
PLpgSQL_expr *expr);
extern bool plpgsql_parse_word(char *word1, const char *yytxt,
--- 832,838 ----
*/
extern PLpgSQL_function *plpgsql_compile(FunctionCallInfo fcinfo,
bool forValidator);
! extern PLpgSQL_function *plpgsql_compile_inline(InlineCodeBlock *codeblock);
extern void plpgsql_parser_setup(struct ParseState *pstate,
PLpgSQL_expr *expr);
extern bool plpgsql_parse_word(char *word1, const char *yytxt,
*** ./src/test/regress/expected/plpgsql.out.orig 2010-06-25 18:40:13.000000000 +0200
--- ./src/test/regress/expected/plpgsql.out 2010-07-04 08:21:55.000000000 +0200
***************
*** 4121,4123 ****
--- 4121,4141 ----
(1 row)
drop function unreserved_test();
+ -- Inline code parametrization
+ do (int := 11, int := 22) $$
+ begin
+ raise notice '%', $1 + $2;
+ end $$;
+ NOTICE: 33
+ do (a int := 11, b int := 22) $$
+ begin
+ raise notice '%', a + b;
+ end $$;
+ NOTICE: 33
+ \set myvar1 'Hello'
+ \set myvar2 'World'
+ do (msg1 text := :'myvar1', msg2 text := :'myvar2') $$
+ begin
+ raise notice '% %', msg1, msg2;
+ end $$;
+ NOTICE: Hello World
*** ./src/test/regress/sql/plpgsql.sql.orig 2010-06-25 18:40:13.000000000 +0200
--- ./src/test/regress/sql/plpgsql.sql 2010-07-04 08:20:42.244266106 +0200
***************
*** 3268,3270 ****
--- 3268,3289 ----
select unreserved_test();
drop function unreserved_test();
+
+ -- Inline code parametrization
+ do (int := 11, int := 22) $$
+ begin
+ raise notice '%', $1 + $2;
+ end $$;
+
+ do (a int := 11, b int := 22) $$
+ begin
+ raise notice '%', a + b;
+ end $$;
+
+ \set myvar1 'Hello'
+ \set myvar2 'World'
+ do (msg1 text := :'myvar1', msg2 text := :'myvar2') $$
+ begin
+ raise notice '% %', msg1, msg2;
+ end $$;
+
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
