On 2011-12-11 16:26, Yeb Havinga wrote:
On 2011-12-06 17:58, Kevin Grittner wrote:
Kevin Grittner<kgri...@wicourts.gov> wrote:
Yeb Havinga<yebhavi...@gmail.com> wrote:
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.
If there are no objections, I suggest that Yeb implement the mixed
notation for cursor parameters.
Hearing no objections -- Yeb, are you OK with doing this, and do you
feel this is doable for this CF?
Attach is v6 of the patch, allowing mixed mode and with new test cases
in the regression tests. One difference with calling functions
remains: it is allowed to place positional arguments after named
parameters. Including that would add code, but nothing would be gained.
Forgot to copy regression output to expected - attached v7 fixes that.
-- Yeb
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
new file mode 100644
index f33cef5..ee2e3a3
*** 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. Similar to calling
+ functions, described in <xref linkend="sql-syntax-calling-funcs">, it
+ is also 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..107007a
*** 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,3401 ----
/*
* 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 named = false;
tok = yylex();
if (cursor->cursor_explicit_argrow < 0)
*************** read_cursor_args(PLpgSQL_var *cursor, in
*** 3402,3407 ****
--- 3414,3422 ----
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();
--- 3425,3525 ----
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 */
! 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 (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("duplicate value for cursor \"%s\" parameter \"%s\"",
! 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);
!
! /*
! * Because named notation allows permutated argument lists, include
! * the parameter name 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..1df0f23
*** a/src/test/regress/expected/plpgsql.out
--- b/src/test/regress/expected/plpgsql.out
*************** select refcursor_test2(20000, 20000) as
*** 2292,2297 ****
--- 2292,2428 ----
(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)
+
+ -- mixing named and positional, ok
+ create or replace function named(int, int) returns boolean as $$
+ declare
+ c1 cursor (param1 int, param2 int) for select * from rc_test where a > param1 and b > param2;
+ begin
+ open c1(param1 := $1, $2);
+ end
+ $$ language plpgsql;
+ -- mixing named and positional, double param2, parse time error at second value
+ create or replace function named(int, int) returns boolean as $$
+ declare
+ c1 cursor (param1 int, param2 int) for select * from rc_test where a > param1 and b > param2;
+ begin
+ open c1(param2 := $1, $2);
+ end
+ $$ language plpgsql;
+ ERROR: duplicate value for cursor "c1" parameter "param2"
+ LINE 5: open c1(param2 := $1, $2);
+ ^
+ -- mixing named and positional, double param1, parse time error at second value
+ create or replace function named(int, int) returns boolean as $$
+ declare
+ c1 cursor (param1 int, param2 int) for select * from rc_test where a > param1 and b > param2;
+ begin
+ open c1($1, param1 := $2);
+ end
+ $$ language plpgsql;
+ ERROR: duplicate value for cursor "c1" parameter "param1"
+ LINE 5: open c1($1, param1 := $2);
+ ^
+ -- not enough parameters, parse time error at );
+ create or replace function named(int, int) returns boolean 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);
+ ^
+ -- double 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: duplicate value for cursor "c1" parameter "p2"
+ 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..f1fb7fd
*** a/src/test/regress/sql/plpgsql.sql
--- b/src/test/regress/sql/plpgsql.sql
*************** select refcursor_test2(20000, 20000) as
*** 1946,1951 ****
--- 1946,2061 ----
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";
+
+ -- mixing named and positional, ok
+ create or replace function named(int, int) returns boolean as $$
+ declare
+ c1 cursor (param1 int, param2 int) for select * from rc_test where a > param1 and b > param2;
+ begin
+ open c1(param1 := $1, $2);
+ end
+ $$ language plpgsql;
+
+ -- mixing named and positional, double param2, parse time error at second value
+ create or replace function named(int, int) returns boolean as $$
+ declare
+ c1 cursor (param1 int, param2 int) for select * from rc_test where a > param1 and b > param2;
+ begin
+ open c1(param2 := $1, $2);
+ end
+ $$ language plpgsql;
+
+ -- mixing named and positional, double param1, parse time error at second value
+ create or replace function named(int, int) returns boolean as $$
+ declare
+ c1 cursor (param1 int, param2 int) for select * from rc_test where a > param1 and b > param2;
+ begin
+ open c1($1, param1 := $2);
+ end
+ $$ language plpgsql;
+
+ -- not enough parameters, parse time error at );
+ create or replace function named(int, int) returns boolean 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;
+
+ -- double 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
