On 2011-10-15 07:41, Tom Lane wrote:
Yeb Havinga<yebhavi...@gmail.com> writes:
Hello Royce,
Thanks again for testing.
I looked this patch over but concluded that it's not ready to apply,
mainly because there are too many weird behaviors around error
reporting.
Thanks again for the review and comments. Attached is v3 of the patch
that addresses all of the points made by Tom. In the regression test I
added a section under --- START ADDITIONAL TESTS that might speedup testing.
On the documentation front, the patch includes a hunk that changes the
description of DECLARE to claim that the argument names are optional,
something I see no support for in the code. It also fails to document
that this patch affects the behavior of cursor FOR loops as well as OPEN,
since both of those places use read_cursor_args().
The declare section was removed. The cursor for loop section was changed
to include a reference to named parameters, however I was unsure about
OPEN as I was under the impression that was already altered.
regards,
Yeb Havinga
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
new file mode 100644
index f33cef5..6a77b75
*** a/doc/src/sgml/plpgsql.sgml
--- b/doc/src/sgml/plpgsql.sgml
*************** OPEN curs1 FOR EXECUTE 'SELECT * FROM '
*** 2823,2833 ****
</para>
</sect3>
! <sect3>
<title>Opening a Bound Cursor</title>
<synopsis>
! OPEN <replaceable>bound_cursorvar</replaceable> <optional> ( <replaceable>argument_values</replaceable> ) </optional>;
</synopsis>
<para>
--- 2823,2833 ----
</para>
</sect3>
! <sect3 id="plpgsql-open-bound-cursor">
<title>Opening a Bound Cursor</title>
<synopsis>
! OPEN <replaceable>bound_cursorvar</replaceable> <optional> ( <optional> <replaceable>argname</replaceable> := </optional> <replaceable>argument_value</replaceable> <optional>, ...</optional> ) </optional>;
</synopsis>
<para>
*************** OPEN <replaceable>bound_cursorvar</repla
*** 2847,2856 ****
--- 2847,2868 ----
</para>
<para>
+ Cursors that have named parameters may be opened using either
+ <firstterm>named</firstterm> or <firstterm>positional</firstterm>
+ notation. In contrast with calling functions, described in <xref
+ linkend="sql-syntax-calling-funcs">, it is not allowed to mix
+ positional and named notation. In positional notation, all arguments
+ are specified in order. In named notation, each argument's name is
+ specified using <literal>:=</literal> to separate it from the
+ argument expression.
+ </para>
+
+ <para>
Examples (these use the cursor declaration examples above):
<programlisting>
OPEN curs2;
OPEN curs3(42);
+ OPEN curs3(key := 42);
</programlisting>
</para>
*************** COMMIT;
*** 3169,3175 ****
<synopsis>
<optional> <<<replaceable>label</replaceable>>> </optional>
! FOR <replaceable>recordvar</replaceable> IN <replaceable>bound_cursorvar</replaceable> <optional> ( <replaceable>argument_values</replaceable> ) </optional> LOOP
<replaceable>statements</replaceable>
END LOOP <optional> <replaceable>label</replaceable> </optional>;
</synopsis>
--- 3181,3187 ----
<synopsis>
<optional> <<<replaceable>label</replaceable>>> </optional>
! FOR <replaceable>recordvar</replaceable> IN <replaceable>bound_cursorvar</replaceable> <optional> ( <optional> <replaceable>argname</replaceable> := </optional> <replaceable>argument_value</replaceable> <optional>, ...</optional> ) </optional> LOOP
<replaceable>statements</replaceable>
END LOOP <optional> <replaceable>label</replaceable> </optional>;
</synopsis>
*************** END LOOP <optional> <replaceable>label</
*** 3187,3192 ****
--- 3199,3209 ----
Each row returned by the cursor is successively assigned to this
record variable and the loop body is executed.
</para>
+
+ <para>
+ See <xref linkend="plpgsql-open-bound-cursor"> on calling cursors with
+ named notation.
+ </para>
</sect2>
</sect1>
diff --git a/src/pl/plpgsql/src/gram.y b/src/pl/plpgsql/src/gram.y
new file mode 100644
index 8c4c2f7..5271ab5
*** a/src/pl/plpgsql/src/gram.y
--- b/src/pl/plpgsql/src/gram.y
*************** static PLpgSQL_expr *read_sql_construct(
*** 67,72 ****
--- 67,73 ----
const char *sqlstart,
bool isexpression,
bool valid_sql,
+ bool trim,
int *startloc,
int *endtoken);
static PLpgSQL_expr *read_sql_expression(int until,
*************** for_control : for_variable K_IN
*** 1313,1318 ****
--- 1314,1320 ----
"SELECT ",
true,
false,
+ true,
&expr1loc,
&tok);
*************** stmt_raise : K_RAISE
*** 1692,1698 ****
expr = read_sql_construct(',', ';', K_USING,
", or ; or USING",
"SELECT ",
! true, true,
NULL, &tok);
new->params = lappend(new->params, expr);
}
--- 1694,1700 ----
expr = read_sql_construct(',', ';', K_USING,
", or ; or USING",
"SELECT ",
! true, true, true,
NULL, &tok);
new->params = lappend(new->params, expr);
}
*************** stmt_dynexecute : K_EXECUTE
*** 1790,1796 ****
expr = read_sql_construct(K_INTO, K_USING, ';',
"INTO or USING or ;",
"SELECT ",
! true, true,
NULL, &endtoken);
new = palloc(sizeof(PLpgSQL_stmt_dynexecute));
--- 1792,1798 ----
expr = read_sql_construct(K_INTO, K_USING, ';',
"INTO or USING or ;",
"SELECT ",
! true, true, true,
NULL, &endtoken);
new = palloc(sizeof(PLpgSQL_stmt_dynexecute));
*************** stmt_dynexecute : K_EXECUTE
*** 1829,1835 ****
expr = read_sql_construct(',', ';', K_INTO,
", or ; or INTO",
"SELECT ",
! true, true,
NULL, &endtoken);
new->params = lappend(new->params, expr);
} while (endtoken == ',');
--- 1831,1837 ----
expr = read_sql_construct(',', ';', K_INTO,
", or ; or INTO",
"SELECT ",
! true, true, true,
NULL, &endtoken);
new->params = lappend(new->params, expr);
} while (endtoken == ',');
*************** static PLpgSQL_expr *
*** 2322,2328 ****
read_sql_expression(int until, const char *expected)
{
return read_sql_construct(until, 0, 0, expected,
! "SELECT ", true, true, NULL, NULL);
}
/* Convenience routine to read an expression with two possible terminators */
--- 2324,2342 ----
read_sql_expression(int until, const char *expected)
{
return read_sql_construct(until, 0, 0, expected,
! "SELECT ", true, 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, true, NULL, endtoken);
}
/* Convenience routine to read an expression with two possible terminators */
*************** read_sql_expression2(int until, int unti
*** 2331,2337 ****
int *endtoken)
{
return read_sql_construct(until, until2, 0, expected,
! "SELECT ", true, true, NULL, endtoken);
}
/* Convenience routine to read a SQL statement that must end with ';' */
--- 2345,2351 ----
int *endtoken)
{
return read_sql_construct(until, until2, 0, expected,
! "SELECT ", true, true, true, NULL, endtoken);
}
/* Convenience routine to read a SQL statement that must end with ';' */
*************** static PLpgSQL_expr *
*** 2339,2345 ****
read_sql_stmt(const char *sqlstart)
{
return read_sql_construct(';', 0, 0, ";",
! sqlstart, false, true, NULL, NULL);
}
/*
--- 2353,2359 ----
read_sql_stmt(const char *sqlstart)
{
return read_sql_construct(';', 0, 0, ";",
! sqlstart, false, true, true, NULL, NULL);
}
/*
*************** read_sql_stmt(const char *sqlstart)
*** 2352,2357 ****
--- 2366,2372 ----
* sqlstart: text to prefix to the accumulated SQL text
* isexpression: whether to say we're reading an "expression" or a "statement"
* valid_sql: whether to check the syntax of the expr (prefixed with sqlstart)
+ * bool: trim trailing whitespace
* startloc: if not NULL, location of first token is stored at *startloc
* endtoken: if not NULL, ending token is stored at *endtoken
* (this is only interesting if until2 or until3 isn't zero)
*************** read_sql_construct(int until,
*** 2364,2369 ****
--- 2379,2385 ----
const char *sqlstart,
bool isexpression,
bool valid_sql,
+ bool trim,
int *startloc,
int *endtoken)
{
*************** read_sql_construct(int until,
*** 2443,2450 ****
plpgsql_append_source_text(&ds, startlocation, yylloc);
/* trim any trailing whitespace, for neatness */
! while (ds.len > 0 && scanner_isspace(ds.data[ds.len - 1]))
! ds.data[--ds.len] = '\0';
expr = palloc0(sizeof(PLpgSQL_expr));
expr->dtype = PLPGSQL_DTYPE_EXPR;
--- 2459,2467 ----
plpgsql_append_source_text(&ds, startlocation, yylloc);
/* trim any trailing whitespace, for neatness */
! if (trim)
! while (ds.len > 0 && scanner_isspace(ds.data[ds.len - 1]))
! ds.data[--ds.len] = '\0';
expr = palloc0(sizeof(PLpgSQL_expr));
expr->dtype = PLPGSQL_DTYPE_EXPR;
*************** check_labels(const char *start_label, co
*** 3374,3389 ****
/*
* 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)
--- 3391,3415 ----
/*
* 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;
! bool named = false;
! bool positional = false;
tok = yylex();
if (cursor->cursor_explicit_argrow < 0)
*************** read_cursor_args(PLpgSQL_var *cursor, in
*** 3402,3407 ****
--- 3428,3436 ----
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
*** 3410,3420 ****
--- 3439,3545 ----
cursor->refname),
parser_errposition(yylloc)));
+ if (false)
+ {
/*
* Read expressions until the matching ')'.
*/
expr = read_sql_expression(')', ")");
+ }
+ else
+ {
+ for (argc = 0; argc < row->nfields; argc++)
+ {
+ int argpos;
+ int endtoken;
+ PLpgSQL_expr *item;
+ if (plpgsql_isidentassign())
+ {
+ /* Named parameter assignment */
+ named = true;
+ for (argpos = 0; argpos < row->nfields; argpos++)
+ if (strcmp(row->fieldnames[argpos], yylval.str) == 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 */
+ positional = true;
+ argpos = argc;
+ }
+
+ /* Read one expression at a time until the matching endtoken. */
+ item = read_sql_construct(',', ')', 0,
+ ",\" or \")",
+ sqlstart,
+ true, true,
+ false, /* do not trim trailing whitespace to keep possible newlines */
+ NULL, &endtoken);
+
+ if (endtoken == ')' && !(argc == row->nfields - 1))
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("not enough arguments for cursor \"%s\"",
+ cursor->refname),
+ parser_errposition(yylloc)));
+
+ if (endtoken == ',' && (argc == row->nfields - 1))
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("too many arguments for cursor \"%s\"",
+ cursor->refname),
+ parser_errposition(yylloc)));
+
+ if (argv[argpos] != NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("cursor \"%s\" argument \"%s\" provided multiple times",
+ cursor->refname, row->fieldnames[argpos]),
+ parser_errposition(yylloc)));
+
+ if (named && positional)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("mixing positional and named parameter assignment not allowed in cursor \"%s\"",
+ cursor->refname),
+ parser_errposition(startlocation)));
+
+ argv[argpos] = item->query + strlen(sqlstart);
+ }
+
+ /* Make positional argument list */
+ initStringInfo(&ds);
+ appendStringInfoString(&ds, sqlstart);
+ for (argc = 0; argc < row->nfields; argc++)
+ {
+ Assert(argv[argc] != NULL);
+ if (named)
+ {
+ appendStringInfo(&ds, "/* %s := */ ", row->fieldnames[argc]);
+ }
+ appendStringInfoString(&ds, argv[argc]);
+
+ if (argc < row->nfields - 1)
+ appendStringInfoString(&ds, ", ");
+ }
+ appendStringInfoChar(&ds, ';');
+
+ expr = palloc0(sizeof(PLpgSQL_expr));
+ expr->dtype = PLPGSQL_DTYPE_EXPR;
+ expr->query = pstrdup(ds.data);
+ expr->plan = NULL;
+ expr->paramnos = NULL;
+ expr->ns = plpgsql_ns_top();
+ pfree(ds.data);
+ }
/* Next we'd better find the until token */
tok = yylex();
if (tok != until)
diff --git a/src/pl/plpgsql/src/pl_scanner.c b/src/pl/plpgsql/src/pl_scanner.c
new file mode 100644
index 76e8436..9c233c4
*** a/src/pl/plpgsql/src/pl_scanner.c
--- b/src/pl/plpgsql/src/pl_scanner.c
*************** plpgsql_scanner_finish(void)
*** 583,585 ****
--- 583,617 ----
yyscanner = NULL;
scanorig = NULL;
}
+
+ /*
+ * Return true if 'IDENT' ':=' are the next two tokens
+ */
+ bool
+ plpgsql_isidentassign(void)
+ {
+ int tok1, tok2;
+ TokenAuxData aux1, aux2;
+ bool result = false;
+
+ tok1 = internal_yylex(&aux1);
+ if (tok1 == IDENT)
+ {
+ tok2 = internal_yylex(&aux2);
+
+ if (tok2 == COLON_EQUALS)
+ result = true;
+ else
+ push_back_token(tok2, &aux2);
+ }
+
+ if (!result)
+ push_back_token(tok1, &aux1);
+
+ plpgsql_yylval = aux1.lval;
+ plpgsql_yylloc = aux1.lloc;
+ plpgsql_yyleng = aux1.leng;
+
+ return result;
+ }
+
diff --git a/src/pl/plpgsql/src/plpgsql.h b/src/pl/plpgsql/src/plpgsql.h
new file mode 100644
index c638f43..0f742e6
*** a/src/pl/plpgsql/src/plpgsql.h
--- b/src/pl/plpgsql/src/plpgsql.h
*************** extern int plpgsql_location_to_lineno(in
*** 968,973 ****
--- 968,974 ----
extern int plpgsql_latest_lineno(void);
extern void plpgsql_scanner_init(const char *str);
extern void plpgsql_scanner_finish(void);
+ extern bool plpgsql_isidentassign(void);
/* ----------
* Externs in gram.y
diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out
new file mode 100644
index fc9d401..fe1db4c
*** a/src/test/regress/expected/plpgsql.out
--- b/src/test/regress/expected/plpgsql.out
*************** select refcursor_test2(20000, 20000) as
*** 2292,2297 ****
--- 2292,2398 ----
(1 row)
--
+ -- tests for cursors with named parameter arguments
+ --
+ create function namedparmcursor_test1(int, int) returns boolean as $$
+ declare
+ c1 cursor (param1 int, param12 int) for select * from rc_test where a > param1 and b > param12;
+ nonsense record;
+ begin
+ open c1(param12 := $2, -- comment after , should be ignored
+ param1 := $1);
+ fetch c1 into nonsense;
+ close c1;
+ if found then
+ return true;
+ else
+ return false;
+ end if;
+ end
+ $$ language plpgsql;
+ select namedparmcursor_test1(20000, 20000) as "Should be false",
+ namedparmcursor_test1(20, 20) as "Should be true";
+ Should be false | Should be true
+ -----------------+----------------
+ f | t
+ (1 row)
+
+ -- should fail
+ create function namedparmcursor_test2(int, int) returns boolean as $$
+ declare
+ c1 cursor (param1 int, param2 int) for select * from rc_test where a > param1 and b > param2;
+ nonsense record;
+ begin
+ open c1(param1 := $1, $2);
+ end
+ $$ language plpgsql;
+ ERROR: mixing positional and named parameter assignment not allowed in cursor "c1"
+ LINE 6: open c1(param1 := $1, $2);
+ ^
+ -- should fail
+ create function namedparmcursor_test6(int, int) returns void as $$
+ declare
+ c1 cursor (param1 int, param2 int) for select * from rc_test where a > param1 and b > param2;
+ begin
+ open c1(param2 := $2);
+ end
+ $$ language plpgsql;
+ ERROR: not enough arguments for cursor "c1"
+ LINE 5: open c1(param2 := $2);
+ ^
+ -- START ADDITIONAL TESTS
+ -- test runtime error
+ create or replace function fooey() returns void as $$
+ declare
+ c1 cursor (p1 int, p2 int) for
+ select * from tenk1 where thousand = p1 and tenthous = p2;
+ begin
+ open c1 (p2 := 77, p1 := 42/0);
+ end $$ language plpgsql;
+ select fooey();
+ ERROR: division by zero
+ CONTEXT: SQL statement "SELECT /* p1 := */ 42/0, /* p2 := */ 77;"
+ PL/pgSQL function "fooey" line 6 at OPEN
+ -- test comment and newline structure
+ create or replace function fooey() returns void as $$
+ declare
+ c1 cursor (p1 int, p2 int) for
+ select * from tenk1 where thousand = p1 and tenthous = p2;
+ begin
+ open c1 (77 -- test
+ , 42/);
+ end $$ language plpgsql;
+ ERROR: syntax error at end of input
+ LINE 7: , 42/);
+ ^
+ select fooey();
+ ERROR: division by zero
+ CONTEXT: SQL statement "SELECT /* p1 := */ 42/0, /* p2 := */ 77;"
+ PL/pgSQL function "fooey" line 6 at OPEN
+ -- test syntax error
+ create or replace function fooey() returns void as $$
+ declare
+ c1 cursor (p1 int, p2 int) for
+ select * from tenk1 where thousand = p1 and tenthous = p2;
+ begin
+ open c1 ( p2 := 42, p1 : = 77/);
+ end $$ language plpgsql;
+ ERROR: syntax error at or near ":"
+ LINE 6: open c1 ( p2 := 42, p1 : = 77/);
+ ^
+ -- test another syntax error
+ create or replace function fooey() returns void as $$
+ declare
+ c1 cursor (p1 int, p2 int) for
+ select * from tenk1 where thousand = p1 and tenthous = p2;
+ begin
+ open c1 ( p2 := 42/, p1 : = 77/);
+ end $$ language plpgsql;
+ ERROR: syntax error at end of input
+ LINE 6: open c1 ( p2 := 42/, p1 : = 77/);
+ ^
+ -- END ADDITIONAL TESTS
+ --
-- tests for "raise" processing
--
create function raise_test1(int) returns int as $$
diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql
new file mode 100644
index 2906943..9233aa7
*** a/src/test/regress/sql/plpgsql.sql
--- b/src/test/regress/sql/plpgsql.sql
*************** select refcursor_test2(20000, 20000) as
*** 1946,1951 ****
--- 1946,2035 ----
refcursor_test2(20, 20) as "Should be true";
--
+ -- tests for cursors with named parameter arguments
+ --
+ create function namedparmcursor_test1(int, int) returns boolean as $$
+ declare
+ c1 cursor (param1 int, param12 int) for select * from rc_test where a > param1 and b > param12;
+ nonsense record;
+ begin
+ open c1(param12 := $2, -- comment after , should be ignored
+ param1 := $1);
+ fetch c1 into nonsense;
+ close c1;
+ if found then
+ return true;
+ else
+ return false;
+ end if;
+ end
+ $$ language plpgsql;
+
+ select namedparmcursor_test1(20000, 20000) as "Should be false",
+ namedparmcursor_test1(20, 20) as "Should be true";
+
+ -- should fail
+ create function namedparmcursor_test2(int, int) returns boolean as $$
+ declare
+ c1 cursor (param1 int, param2 int) for select * from rc_test where a > param1 and b > param2;
+ nonsense record;
+ begin
+ open c1(param1 := $1, $2);
+ end
+ $$ language plpgsql;
+
+ -- should fail
+ create function namedparmcursor_test6(int, int) returns void as $$
+ declare
+ c1 cursor (param1 int, param2 int) for select * from rc_test where a > param1 and b > param2;
+ begin
+ open c1(param2 := $2);
+ end
+ $$ language plpgsql;
+
+ -- START ADDITIONAL TESTS
+ -- test runtime error
+ create or replace function fooey() returns void as $$
+ declare
+ c1 cursor (p1 int, p2 int) for
+ select * from tenk1 where thousand = p1 and tenthous = p2;
+ begin
+ open c1 (p2 := 77, p1 := 42/0);
+ end $$ language plpgsql;
+ select fooey();
+
+ -- test comment and newline structure, will not give runtime error when read_sql_construct trims trailing whitespace
+ create or replace function fooey() returns void as $$
+ declare
+ c1 cursor (p1 int, p2 int) for
+ select * from tenk1 where thousand = p1 and tenthous = p2;
+ begin
+ open c1 (77 -- test
+ , 42/);
+ end $$ language plpgsql;
+ select fooey();
+
+ -- test syntax error
+ create or replace function fooey() returns void as $$
+ declare
+ c1 cursor (p1 int, p2 int) for
+ select * from tenk1 where thousand = p1 and tenthous = p2;
+ begin
+ open c1 ( p2 := 42, p1 : = 77);
+ end $$ language plpgsql;
+
+ -- test another syntax error
+ create or replace function fooey() returns void as $$
+ declare
+ c1 cursor (p1 int, p2 int) for
+ select * from tenk1 where thousand = p1 and tenthous = p2;
+ begin
+ open c1 ( p2 := 42/, p1 : = 77);
+ end $$ language plpgsql;
+
+ -- END ADDITIONAL TESTS
+
+ --
-- tests for "raise" processing
--
create function raise_test1(int) returns int as $$
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
