Re: plpgsql + named parameters

On May 20, 2009, at 10:24 AM, Pavel Stehule wrote:
> this problem is little bit deeper and is related to plpgsql method for
> SQL query processing.
>
> I thing so there are two solutions:
>
> a) use dynamic SQL
> b) use double quotes for identifier - identifiers have to be lower
>
> t := fun1(a as "a");

plpgsql substitutes an expression parameter for the double-quoted
identifier as well and I'm less than thrilled about using dynamic SQL
to make all my function calls. I was hoping we could modify the
grammar so that identifiers after the AS keyword are passed through.

Something like this patch:

diff --git a/src/pl/plpgsql/src/gram.y b/src/pl/plpgsql/src/gram.y
index 06704cf..66d12d8 100644
--- a/src/pl/plpgsql/src/gram.y
+++ b/src/pl/plpgsql/src/gram.y
@@ -177,6 +177,7 @@ static List *read_raise_options(void);
* Keyword tokens
*/
%token K_ALIAS
+%token K_AS
%token K_ASSIGN
%token K_BEGIN
%token K_BY
@@ -1977,6 +1978,7 @@ read_sql_construct(int until,
int *endtoken)
{
int tok;
+ int prevtok = 0;
int lno;
PLpgSQL_dstring ds;
int parenlevel = 0;
@@ -1989,7 +1991,7 @@ read_sql_construct(int until,
plpgsql_dstring_init(&ds);
plpgsql_dstring_append(&ds, sqlstart);

- for (;;)
+ for (;;prevtok = tok)
{
tok = yylex();
if (tok == until && parenlevel == 0)
@@ -2034,10 +2036,22 @@ read_sql_construct(int until,
switch (tok)
{
case T_SCALAR:
- snprintf(buf, sizeof(buf), " $%d ",
- assign_expr_param(yylval.scalar->dno,
- params, &nparams));
- plpgsql_dstring_append(&ds, buf);
+ /*
+ * If the previous token is AS, then we pass the scalar
+ * through as a label. Otherwise, make the scalar an
+ * expression parameter.
+ */
+ if (prevtok == K_AS)
+ {
+ plpgsql_dstring_append(&ds, yytext);
+ }
+ else
+ {
+ snprintf(buf, sizeof(buf), " $%d ",
+ assign_expr_param(yylval.scalar->dno,
+ params, &nparams));
+ plpgsql_dstring_append(&ds, buf);
+ }
break;

case T_ROW:
diff --git a/src/pl/plpgsql/src/scan.l b/src/pl/plpgsql/src/scan.l
index 1917eef..e3a5c45 100644
--- a/src/pl/plpgsql/src/scan.l
+++ b/src/pl/plpgsql/src/scan.l
@@ -149,6 +149,7 @@ param \${digit}+
= { return K_ASSIGN; }
\.\. { return K_DOTDOT; }
alias { return K_ALIAS; }
+as { return K_AS; }
begin { return K_BEGIN; }
by { return K_BY; }
case { return K_CASE; }