Kevin,
Thank you for your review!
On 2011-12-03 21:53, Kevin Grittner wrote:
(1) Doc changes:
(a) These contain some unnecessary whitespace changes which make it
harder to see exactly what changed.
This is probably caused because I used emacs's fill-paragraph (alt+q)
command, after some changes. If this is against policy, I could change
the additions in such a way as to cause minor differences, however I
believe that the benefit of that ends immediately after review.
(b) There's one point where "cursors" should be possessive
"cursor's".
(c) I think it would be less confusing to be consistent about
mentioning "positional" and "named" in the same order each
time. Mixing it up like that is harder to read, at least for
me.
Good point, both changed.
(2) The error for mixing positional and named parameters should be
moved up. That seems more important than "too many arguments" or
"provided multiple times" if both are true.
I moved the error up, though I personally tend to believe it doesn't
even need to be an error. There is no technical reason not to allow it.
All the user needs to do is make sure that the combination of named
parameters and the positional ones together are complete and not
overlapping. This is also in line with calling functions, where mixing
named and positional is allowed, as long as the positional arguments are
first. Personally I have no opinion what is best, include the feature or
give an error, and I removed the possibility during the previous commitfest.
(3) The "-- END ADDITIONAL TESTS" comment shouldn't be added to the
regression tests.
This is removed, also the -- START ADDITIONAL TESTS, though I kept the
tests between those to comments.
The way this parses out the named parameters and then builds the
positional list, with some code from read_sql_construct() repeated in
read_cursor_args() seems a little bit clumsy to me, but I couldn't
think of a better way to do it.
Same here.
The new patch is attached.
regards
Yeb Havinga
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
new file mode 100644
index f33cef5..a3e6540
*** 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,2867 ----
</para>
<para>
+ Cursors may be opened using either <firstterm>positional</firstterm>
+ or <firstterm>named</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,3186 ****
<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>
The cursor variable must have been bound to some query when it was
! declared, and it <emphasis>cannot</> be open already. The
! <command>FOR</> statement automatically opens the cursor, and it closes
! the cursor again when the loop exits. A list of actual argument value
! expressions must appear if and only if the cursor was declared to take
! arguments. These values will be substituted in the query, in just
! the same way as during an <command>OPEN</>.
The variable <replaceable>recordvar</replaceable> is automatically
defined as type <type>record</> and exists only inside the loop (any
existing definition of the variable name is ignored within the loop).
--- 3180,3203 ----
<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>
The cursor variable must have been bound to some query when it was
! declared, and it <emphasis>cannot</> be open already. The <command>FOR</>
! statement automatically opens the cursor, and it closes the cursor again
! when the loop exits. The cursor's arguments may be supplied in either
! <firstterm>positional</firstterm> or <firstterm>named</firstterm>
! notation. A list of actual argument value expressions must appear if and
! only if the cursor was declared to take arguments. These values will be
! substituted in the query, in just the same way as during an
! <command>OPEN</command> described in <xref
! linkend="plpgsql-open-bound-cursor">.
! </para>
!
! <para>
The variable <replaceable>recordvar</replaceable> is automatically
defined as type <type>record</> and exists only inside the loop (any
existing definition of the variable name is ignored within the loop).
diff --git a/src/pl/plpgsql/src/gram.y b/src/pl/plpgsql/src/gram.y
new file mode 100644
index 8c4c2f7..f7d6b9d
*** 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,2330 ----
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 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 ';' */
--- 2333,2339 ----
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);
}
/*
--- 2341,2347 ----
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 ****
--- 2354,2360 ----
* 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 ****
--- 2367,2373 ----
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;
--- 2447,2455 ----
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)
--- 3379,3402 ----
/*
* 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 ";
! bool positional = false;
! bool named = false;
tok = yylex();
if (cursor->cursor_explicit_argrow < 0)
*************** read_cursor_args(PLpgSQL_var *cursor, in
*** 3402,3407 ****
--- 3415,3423 ----
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,3419 ****
cursor->refname),
parser_errposition(yylloc)));
! /*
! * Read expressions until the matching ')'.
! */
! expr = read_sql_expression(')', ")");
/* Next we'd better find the until token */
tok = yylex();
--- 3426,3531 ----
cursor->refname),
parser_errposition(yylloc)));
! for (argc = 0; argc < row->nfields; argc++)
! {
! int argpos;
! int endtoken;
! PLpgSQL_expr *item;
! int arglocation;
!
! if (plpgsql_isidentassign(&arglocation))
! {
! /* 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. To
! * provide the user with meaningful parse error positions, we check the
! * syntax immediately, instead of checking the final expression that
! * may have a permutated argument list. Also trailing whitespace may
! * not be trimmed, since otherwise input of the form (param --
! * comment\n, param) is translated into a form that comments the
! * remaining parameters.
! */
! item = read_sql_construct(',', ')', 0,
! ",\" or \")",
! sqlstart,
! true, true,
! false, /* do not trim */
! 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 (named && positional)
! ereport(ERROR,
! (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("mixing positional and named parameter assignment not allowed in cursor \"%s\"",
! cursor->refname),
! parser_errposition(arglocation)));
!
! 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(arglocation)));
!
! 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);
!
! /* Argument name included for meaningful runtime errors */
! 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();
diff --git a/src/pl/plpgsql/src/pl_scanner.c b/src/pl/plpgsql/src/pl_scanner.c
new file mode 100644
index 76e8436..ce3161c
*** a/src/pl/plpgsql/src/pl_scanner.c
--- b/src/pl/plpgsql/src/pl_scanner.c
*************** plpgsql_scanner_finish(void)
*** 583,585 ****
--- 583,622 ----
yyscanner = NULL;
scanorig = NULL;
}
+
+ /*
+ * Return true if 'IDENT' ':=' are the next two tokens
+ *
+ * startloc: if not NULL, location of first token is stored at *startloc
+ */
+ bool
+ plpgsql_isidentassign(int *startloc)
+ {
+ int tok1, tok2;
+ TokenAuxData aux1, aux2;
+ bool result = false;
+
+ tok1 = internal_yylex(&aux1);
+ if (startloc)
+ *startloc = aux1.lloc;
+
+ 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..b3ff847
*** 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(int *startloc);
/* ----------
* 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 238bf5f..8516466
*** a/src/test/regress/expected/plpgsql.out
--- b/src/test/regress/expected/plpgsql.out
*************** select refcursor_test2(20000, 20000) as
*** 2292,2297 ****
--- 2292,2410 ----
(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)
+
+ -- may not mix named and positional, parse time error at $2
+ 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);
+ ^
+ -- not enough parameters, parse time error at );
+ 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);
+ ^
+ -- multiple same parameter, parse time error at second p2
+ 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, p2 := 42);
+ end $$ language plpgsql;
+ ERROR: cursor "c1" argument "p2" provided multiple times
+ LINE 6: open c1 (p2 := 77, p2 := 42);
+ ^
+ -- division by zero runtime error,
+ -- provides context users can make sense of:
+ -- CONTEXT: SQL statement "SELECT /* p1 := */ 42/0, /* p2 := */ 77;"
+ 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, 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;
+ 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 at :
+ 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 at ,
+ 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);
+ ^
+ --
-- 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 b47c2de..749aa46
*** a/src/test/regress/sql/plpgsql.sql
--- b/src/test/regress/sql/plpgsql.sql
*************** select refcursor_test2(20000, 20000) as
*** 1946,1951 ****
--- 1946,2044 ----
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";
+
+ -- may not mix named and positional, parse time error at $2
+ 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;
+
+ -- not enough parameters, parse time error at );
+ 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;
+
+ -- multiple same parameter, parse time error at second p2
+ 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, p2 := 42);
+ end $$ language plpgsql;
+
+ -- division by zero runtime error,
+ -- provides context users can make sense of:
+ -- CONTEXT: SQL statement "SELECT /* p1 := */ 42/0, /* p2 := */ 77;"
+ 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 at :
+ 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 at ,
+ 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;
+
+ --
-- 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
