On 2011-11-14 15:45, Yeb Havinga wrote:
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.
Please disregard the previous patch: besides that it contained an unused
function, it turned out my statement that all of Tom's points were
addressed was not true - the attached patch fixes the remaining issue of
putting two kinds of errors at the correct start of the current argument
location.
I also put some more comments in the regression test section: mainly to
assist providing testcases for review, not for permanent inclusion.
To address a corner case of the form 'p1 := 1 -- comments\n, p2 := 2' it
was necessary to have read_sql_construct not trim trailing whitespace,
since that results in an expression of the form '1 -- comments, 2' which
is wrong.
regards,
Yeb Havinga
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
new file mode 100644
index f33cef5..17c04d2
*** 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>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,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 cursors arguments may be supplied in either
! <firstterm>named</firstterm> or <firstterm>positional</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..19f8da8
*** 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 named = false;
! bool positional = 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,3530 ----
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
! * consecutive 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("cursor \"%s\" argument \"%s\" provided multiple times",
! cursor->refname, row->fieldnames[argpos]),
! parser_errposition(arglocation)));
!
! 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)));
!
! 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 fc9d401..bf31a9a
*** a/src/test/regress/expected/plpgsql.out
--- b/src/test/regress/expected/plpgsql.out
*************** select refcursor_test2(20000, 20000) as
*** 2292,2297 ****
--- 2292,2412 ----
(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);
+ ^
+ -- START ADDITIONAL TESTS
+ -- 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);
+ ^
+ -- 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..bad7f05
*** a/src/test/regress/sql/plpgsql.sql
--- b/src/test/regress/sql/plpgsql.sql
*************** select refcursor_test2(20000, 20000) as
*** 1946,1951 ****
--- 1946,2047 ----
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;
+
+ -- START ADDITIONAL TESTS
+ -- 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;
+
+ -- 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
