Hello
2009/5/21 Tom Lane <[email protected]>:
> Steve Prentice <[email protected]> writes:
>> This patch is intended to supplement Pavel's patch for named and mixed
>> notation support in 8.5. This patch makes it so a plpgsql function can
>> call another function with the same parameter names using the named
>> parameters notation.
>
> Well, plpgsql's parsing has always been a kluge, but I think this is
> really taking the kluge level a step too far. It's only because AS
> is used in so few contexts that this can even pretend to work --- but
> there are still an awful lot of contexts where AS is used, and will
> likely be more in the future. So I think it's pretty un-future-proof;
> and it certainly won't scale to any other contexts where we might wish
> that plpsql variables don't get substituted.
>
> It's probably time to bite the bullet and redo the parser as has been
> suggested in the past, ie fix things so that the main parser is used.
> Ideally I'd like to switch the name resolution priority to be more
> Oracle-like, but even if we don't do that it would be a great
> improvement to have actual syntactic knowledge behind the lookups.
I have fast hack, that is based on callback function from
transformExpr - it replace unknown ColumnRef node to Param node. It
works well, but I have two notes:
a) when we use main parser for identification, then valid_sql flag has
different sense. We have to valid all SQL statements - so we have to
change some logic or we have to develop levels of validations. This
have one big advantage - we are able to do complete validation.
Disadvantage - check is necessary before every first run.
b) Change priority (sql identifiers >> plpgsql identifiers) will have
two impacts:
** buggy applications will have different behave
** some an table's alters should change function's behave - so minimum
is reset plpgsql cache.
postgres=# create or replace function test(a int) returns int as $$
declare x int; y int; begin return a+20 as a; end; $$ language
plpgsql;
CREATE FUNCTION
Time: 2,485 ms
postgres=# select test(20);
test
------
40
(1 row)
Time: 1,473 ms
postgres=# create or replace function test(a int) returns int as $$
declare x int; y int; begin return a+20 as a from a; end; $$ language
plpgsql;
ERROR: relation "a" does not exist
LINE 1: SELECT a+20 as a from a
^ --< pointer is correct, look on it
some non proportional font
QUERY: SELECT a+20 as a from a
CONTEXT: SQL statement in PL/PgSQL function "test" near line 1
Attached patch is VERY UGLY, but it should to show possible direction.
I thing, so some similar should by in 8.5
??? Notes, Comments
Regards
Pavel Stehule
>
> Just for the record, you'd have to put the same kluge into the T_RECORD
> and T_ROW cases if we wanted to do it like this.
>
> regards, tom lane
>
> --
> Sent via pgsql-hackers mailing list ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>
*** ./src/backend/parser/parse_expr.c.orig 2009-05-22 17:12:52.000000000 +0200
--- ./src/backend/parser/parse_expr.c 2009-05-22 17:44:54.000000000 +0200
***************
*** 458,466 ****
if (refnameRangeTblEntry(pstate, NULL, name1,
cref->location,
&levels_up) != NULL)
node = transformWholeRowRef(pstate, NULL, name1,
cref->location);
! else
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_COLUMN),
errmsg("column \"%s\" does not exist",
--- 458,473 ----
if (refnameRangeTblEntry(pstate, NULL, name1,
cref->location,
&levels_up) != NULL)
+ {
node = transformWholeRowRef(pstate, NULL, name1,
cref->location);
! break;
! }
!
! if (pstate->p_external_var_hook)
! node = (pstate->p_external_var_hook)(pstate, name1, cref->location);
!
! if (node == NULL)
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_COLUMN),
errmsg("column \"%s\" does not exist",
*** ./src/include/parser/parse_node.h.orig 2009-05-22 17:33:37.000000000 +0200
--- ./src/include/parser/parse_node.h 2009-05-22 17:48:12.000000000 +0200
***************
*** 17,22 ****
--- 17,25 ----
#include "nodes/parsenodes.h"
#include "utils/relcache.h"
+ /* Hook for PL variable detection and substitution */
+ typedef Node *(*ParseExprExternalVariables_hook_type) (void *pstate, char *name, int location);
+
/*
* State information used during parse analysis
*
***************
*** 102,107 ****
--- 105,111 ----
bool p_is_update;
Relation p_target_relation;
RangeTblEntry *p_target_rangetblentry;
+ ParseExprExternalVariables_hook_type p_external_var_hook;
} ParseState;
/* Support for parser_errposition_callback function */
*** ./src/pl/plpgsql/src/gram.y.orig 2009-05-22 17:53:12.000000000 +0200
--- ./src/pl/plpgsql/src/gram.y 2009-05-22 21:14:03.000000000 +0200
***************
*** 18,23 ****
--- 18,25 ----
#include "catalog/pg_type.h"
#include "parser/parser.h"
+ #include "parser/parse_node.h"
+ #include "parser/parse_expr.h"
/*
***************
*** 31,36 ****
--- 33,42 ----
#define YYMALLOC palloc
#define YYFREE pfree
+ static int nparams = 0;
+ static int params[200];
+
+ static MemoryContext oldCxt;
static PLpgSQL_expr *read_sql_construct(int until,
int until2,
***************
*** 71,76 ****
--- 77,89 ----
int until, const char *expected);
static List *read_raise_options(void);
+ typedef struct {
+ int location;
+ char *token;
+ } ParserToken;
+
+ List *param_tokens;
+
%}
%expect 0
***************
*** 1953,1958 ****
--- 1966,1983 ----
sqlstart, false, true, NULL);
}
+ static List *
+ push_token(List *tokens, char *tokentxt, int location)
+ {
+
+ ParserToken *pt = palloc(sizeof(ParserToken));
+ pt->token = pstrdup(tokentxt);
+ pt->location = location;
+
+ return lappend(tokens, pt);
+ }
+
+
/*
* Read a SQL construct and build a PLpgSQL_expr for it.
*
***************
*** 1980,1994 ****
int lno;
PLpgSQL_dstring ds;
int parenlevel = 0;
- int nparams = 0;
- int params[MAX_EXPR_PARAMS];
char buf[32];
PLpgSQL_expr *expr;
lno = plpgsql_scanner_lineno();
plpgsql_dstring_init(&ds);
plpgsql_dstring_append(&ds, sqlstart);
for (;;)
{
tok = yylex();
--- 2005,2020 ----
int lno;
PLpgSQL_dstring ds;
int parenlevel = 0;
char buf[32];
PLpgSQL_expr *expr;
+ List *stacked_tokens = NIL;
lno = plpgsql_scanner_lineno();
plpgsql_dstring_init(&ds);
plpgsql_dstring_append(&ds, sqlstart);
+ stacked_tokens = push_token(stacked_tokens, sqlstart, 0);
+
for (;;)
{
tok = yylex();
***************
*** 2031,2036 ****
--- 2057,2064 ----
if (plpgsql_SpaceScanned)
plpgsql_dstring_append(&ds, " ");
+ stacked_tokens = push_token(stacked_tokens, yytext, ds.used - 1);
+ /*
switch (tok)
{
case T_SCALAR:
***************
*** 2058,2068 ****
--- 2086,2135 ----
plpgsql_dstring_append(&ds, yytext);
break;
}
+ */
+ plpgsql_dstring_append(&ds, yytext);
}
if (endtoken)
*endtoken = tok;
+ check_sql_expr(plpgsql_dstring_get(&ds));
+
+ if (param_tokens != NIL)
+ {
+ ListCell *l;
+ ListCell *l2;
+ char *token;
+ bool first = true;
+
+ /* generate parametrized query string */
+ plpgsql_dstring_init(&ds);
+
+ foreach(l, stacked_tokens)
+ {
+ ParserToken *pt = (ParserToken *) lfirst(l);
+ token = pt->token;
+
+ foreach(l2, param_tokens)
+ {
+ ParserToken *pt2 = (ParserToken *) lfirst(l2);
+
+ if (pt2->location == pt->location)
+ {
+ token = pt2->token;
+ break;
+ }
+ }
+
+ if (!first)
+ plpgsql_dstring_append_char(&ds, ' ');
+ else
+ first = false;
+
+ plpgsql_dstring_append(&ds, token);
+ }
+ }
+
expr = palloc(sizeof(PLpgSQL_expr) + sizeof(int) * nparams - sizeof(int));
expr->dtype = PLPGSQL_DTYPE_EXPR;
expr->query = pstrdup(plpgsql_dstring_get(&ds));
***************
*** 2072,2079 ****
expr->params[nparams] = params[nparams];
plpgsql_dstring_free(&ds);
- if (valid_sql)
- check_sql_expr(expr->query);
return expr;
}
--- 2139,2144 ----
***************
*** 2689,2694 ****
--- 2754,2797 ----
return row;
}
+ Node *local_variables_finder(ParseState *pstate, char *name, int location)
+ {
+ PLpgSQL_nsitem *var;
+ Node *result = NULL;
+ MemoryContext cxt;
+
+ var = plpgsql_ns_lookup(name, NULL, NULL, NULL);
+ if (var != NULL)
+ {
+ PLpgSQL_type *dtype = plpgsql_get_dtype(var->itemno);
+ Param *p = makeNode(Param);
+ ParserToken *pt = palloc(sizeof(ParserToken));
+ char buf[32];
+
+ p->paramid = assign_expr_param(var->itemno,
+ params, &nparams);
+
+ p->paramtype = dtype->typoid;
+ p->paramtypmod = dtype->atttypmod;
+ p->location = location;
+
+ cxt = MemoryContextSwitchTo(oldCxt);
+
+ snprintf(buf, sizeof(buf), "$%d ", p->paramid);
+
+ pt->location = location;
+ pt->token = pstrdup(buf);
+
+ param_tokens = lappend(param_tokens, pt);
+
+ MemoryContextSwitchTo(cxt);
+
+ return (Node *) p;
+
+ }
+ return result;
+ }
+
/*
* When the PL/PgSQL parser expects to see a SQL statement, it is very
* liberal in what it accepts; for example, we often assume an
***************
*** 2711,2717 ****
{
ErrorContextCallback syntax_errcontext;
ErrorContextCallback *previous_errcontext;
! MemoryContext oldCxt;
if (!plpgsql_check_syntax)
return;
--- 2814,2831 ----
{
ErrorContextCallback syntax_errcontext;
ErrorContextCallback *previous_errcontext;
!
! ParseState *pstate = make_parsestate(NULL);
! pstate->p_external_var_hook = local_variables_finder;
! ListCell *l;
! List *list;
!
! pstate->p_sourcetext = stmt;
! pstate->p_paramtypes = NULL;
! pstate->p_variableparams = true;
!
!
!
if (!plpgsql_check_syntax)
return;
***************
*** 2732,2738 ****
error_context_stack = &syntax_errcontext;
oldCxt = MemoryContextSwitchTo(compile_tmp_cxt);
! (void) raw_parser(stmt);
MemoryContextSwitchTo(oldCxt);
/* Restore former ereport callback */
--- 2846,2862 ----
error_context_stack = &syntax_errcontext;
oldCxt = MemoryContextSwitchTo(compile_tmp_cxt);
!
! list = raw_parser(stmt);
!
! nparams = 0;
! param_tokens = NIL;
!
! foreach(l, list)
! {
! transformStmt(pstate, lfirst(l));
! }
!
MemoryContextSwitchTo(oldCxt);
/* Restore former ereport callback */
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
