Hello list,
The following patch implements cursor calling with named parameters in
addition to the standard positional argument lists.
c1 cursor (param1 int, param2 int) for select * from rc_test where a >
param1 and b > param2;
open c1($1, $2); -- this is currently possible
open c1(param2 := $2, param1 := $1); -- this is the new feature
Especially for cursors with a lot of arguments, this increases
readability of code. This was discussed previously in
http://archives.postgresql.org/pgsql-hackers/2010-09/msg01433.php. We
actually made two patches: one with => and then one with := notation.
Attached is the patch with := notation.
Is it ok to add it to the next commitfest?
regards,
Yeb Havinga, Willem Dijkstra
--
Yeb Havinga
http://www.mgrid.net/
Mastering Medical Data
diff --git a/src/pl/plpgsql/src/gram.y b/src/pl/plpgsql/src/gram.y
new file mode 100644
index 92b54dd..192f278
*** a/src/pl/plpgsql/src/gram.y
--- b/src/pl/plpgsql/src/gram.y
*** read_sql_expression(int until, const cha
*** 2335,2340
--- 2335,2352
"SELECT ", true, true, NULL, NULL);
}
+ /*
+ * Convenience routine to read a single unchecked expression with two possible
+ * terminators, returning an expression with an empty sql prefix.
+ */
+ static PLpgSQL_expr *
+ read_sql_one_expression(int until, int until2, const char *expected,
+ int *endtoken)
+ {
+ return read_sql_construct(until, until2, 0, expected,
+ "", true, false, NULL, endtoken);
+ }
+
/* Convenience routine to read an expression with two possible terminators */
static PLpgSQL_expr *
read_sql_expression2(int until, int until2, const char *expected,
*** check_labels(const char *start_label, co
*** 3384,3399
/*
* Read the arguments (if any) for a cursor, followed by the until token
*
! * If cursor has no args, just swallow the until token and return NULL.
! * If it does have args, we expect to see "( expr [, expr ...] )" followed
! * by the until token. Consume all that and return a SELECT query that
! * evaluates the expression(s) (without the outer parens).
*/
static PLpgSQL_expr *
read_cursor_args(PLpgSQL_var *cursor, int until, const char *expected)
{
PLpgSQL_expr *expr;
! int tok;
tok = yylex();
if (cursor->cursor_explicit_argrow < 0)
--- 3396,3418
/*
* Read the arguments (if any) for a cursor, followed by the until token
*
! * If cursor has no args, just swallow the until token and return NULL. If it
! * does have args, we expect to see "( expr [, expr ...] )" followed by the
! * until token, where expr may be a plain expression, or a named parameter
! * assignment of the form IDENT := expr. Consume all that and return a SELECT
! * query that evaluates the expression(s) (without the outer parens).
*/
static PLpgSQL_expr *
read_cursor_args(PLpgSQL_var *cursor, int until, const char *expected)
{
PLpgSQL_expr *expr;
! PLpgSQL_row *row;
! int tok;
! int argc = 0;
! char **argv;
! StringInfoData ds;
! char *sqlstart = "SELECT ";
! int startlocation = yylloc;
tok = yylex();
if (cursor->cursor_explicit_argrow < 0)
*** read_cursor_args(PLpgSQL_var *cursor, in
*** 3412,3417
--- 3431,3439
return NULL;
}
+ row = (PLpgSQL_row *) plpgsql_Datums[cursor->cursor_explicit_argrow];
+ argv = (char **) palloc0(sizeof(char *) * row->nfields);
+
/* Else better provide arguments */
if (tok != '(')
ereport(ERROR,
*** read_cursor_args(PLpgSQL_var *cursor, in
*** 3420,3429
cursor->refname),
parser_errposition(yylloc)));
! /*
! * Read expressions until the matching ')'.
! */
! expr = read_sql_expression(')', ")");
/* Next we'd better find the until token */
tok = yylex();
--- 3442,3527
cursor->refname),
parser_errposition(yylloc)));
! for (argc = 0; argc < row->nfields; argc++)
! {
! int argpos;
! int endtoken;
! PLpgSQL_expr *item;
!
! if (plpgsql_isidentassign())
! {
! /* Named parameter assignment */
! for (argpos = 0; argpos < row->nfields; argpos++)
! if (strncmp(row->fieldnames[argpos], yylval.str, strlen(row->fieldnames[argpos])) == 0)
! break;
!
! if (argpos == row->nfields)
! ereport(ERROR,
! (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("cursor \"%s\" has no argument named \"%s\"",
! cursor->refname, yylval.str),
! parser_errposition(yylloc)));
! }
! else
! {
! /* Positional parameter assignment */
! argpos = argc;
! }
!
! /*
! * Read one expression at a time until the matching endtoken. Checking
! * the expressions is postponed until the positional argument list is
! * made.
! */
! item = read_sql_one_expression(',', ')', ",\" or \")", &endtoken);
!
! if (endtoken == ')' && !(argc == row->nfields - 1))
! ereport(ERROR,
! (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("not enough
