PL/PgSQL STRICT
Hi,
Courtesy of me, Christmas comes a bit early this year. I wrote a patch
which allows you to add STRICT into PERFORM and INSERT/UPDATE/DELETE
without specifying an INTO clause. Observe:
=# create table foo(a int);
CREATE TABLE
=# create function foof() returns void as $$ begin update strict foo set
a=a+1; end $$ language plpgsql;
CREATE FUNCTION
=# select foof();
ERROR: query returned no rows
I know everyone obviously wants this, so I will be sending another
version with regression tests and documentation later. The code is a
bit ugly at places, but I'm going to work on that too.
Regards,
Marko Tiikkaja
Attachments:
strict.patchtext/plain; charset=UTF-8; name=strict.patch; x-mac-creator=0; x-mac-type=0Download
*** a/src/pl/plpgsql/src/pl_exec.c
--- b/src/pl/plpgsql/src/pl_exec.c
***************
*** 1500,1508 **** static int
exec_stmt_perform(PLpgSQL_execstate *estate, PLpgSQL_stmt_perform *stmt)
{
PLpgSQL_expr *expr = stmt->expr;
(void) exec_run_select(estate, expr, 0, NULL);
! exec_set_found(estate, (estate->eval_processed != 0));
exec_eval_cleanup(estate);
return PLPGSQL_RC_OK;
--- 1500,1519 ----
exec_stmt_perform(PLpgSQL_execstate *estate, PLpgSQL_stmt_perform *stmt)
{
PLpgSQL_expr *expr = stmt->expr;
+ uint32 n;
(void) exec_run_select(estate, expr, 0, NULL);
! n = estate->eval_processed;
! if (stmt->strict && n == 0)
! ereport(ERROR,
! (errcode(ERRCODE_NO_DATA_FOUND),
! errmsg("query returned no rows")));
! else if (stmt->strict && n > 1)
! ereport(ERROR,
! (errcode(ERRCODE_TOO_MANY_ROWS),
! errmsg("query returned more than one row")));
!
! exec_set_found(estate, (n != 0));
exec_eval_cleanup(estate);
return PLPGSQL_RC_OK;
***************
*** 3211,3217 **** exec_stmt_execsql(PLpgSQL_execstate *estate,
* forcing completion of a sequential scan. So don't do it unless we need
* to enforce strictness.
*/
! if (stmt->into)
{
if (stmt->strict || stmt->mod_stmt)
tcount = 2;
--- 3222,3228 ----
* forcing completion of a sequential scan. So don't do it unless we need
* to enforce strictness.
*/
! if (stmt->into || stmt->strict)
{
if (stmt->strict || stmt->mod_stmt)
tcount = 2;
***************
*** 3335,3340 **** exec_stmt_execsql(PLpgSQL_execstate *estate,
--- 3346,3366 ----
exec_eval_cleanup(estate);
SPI_freetuptable(SPI_tuptable);
}
+ else if (stmt->strict)
+ {
+ /*
+ * If a mod stmt specified STRICT, and the query didn't find
+ * exactly one row, throw an error.
+ */
+ if (SPI_processed == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_NO_DATA_FOUND),
+ errmsg("query returned no rows")));
+ else if (SPI_processed > 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_TOO_MANY_ROWS),
+ errmsg("query returned more than one row")));
+ }
else
{
/* If the statement returned a tuple table, complain */
*** a/src/pl/plpgsql/src/pl_funcs.c
--- b/src/pl/plpgsql/src/pl_funcs.c
***************
*** 1187,1193 **** static void
dump_perform(PLpgSQL_stmt_perform *stmt)
{
dump_ind();
! printf("PERFORM expr = ");
dump_expr(stmt->expr);
printf("\n");
}
--- 1187,1193 ----
dump_perform(PLpgSQL_stmt_perform *stmt)
{
dump_ind();
! printf("PERFORM%s expr = ", stmt->strict ? " STRICT" : "");
dump_expr(stmt->expr);
printf("\n");
}
*** a/src/pl/plpgsql/src/pl_gram.y
--- b/src/pl/plpgsql/src/pl_gram.y
***************
*** 178,183 **** static List *read_raise_options(void);
--- 178,184 ----
%type <expr> expr_until_semi expr_until_rightbracket
%type <expr> expr_until_then expr_until_loop opt_expr_until_when
%type <expr> opt_exitcond
+ %type <boolean> opt_strict
%type <ival> assign_var foreach_slice
%type <var> cursor_variable
***************
*** 834,847 **** proc_stmt : pl_block ';'
{ $$ = $1; }
;
! stmt_perform : K_PERFORM expr_until_semi
{
PLpgSQL_stmt_perform *new;
new = palloc0(sizeof(PLpgSQL_stmt_perform));
new->cmd_type = PLPGSQL_STMT_PERFORM;
new->lineno = plpgsql_location_to_lineno(@1);
! new->expr = $2;
$$ = (PLpgSQL_stmt *)new;
}
--- 835,849 ----
{ $$ = $1; }
;
! stmt_perform : K_PERFORM opt_strict expr_until_semi
{
PLpgSQL_stmt_perform *new;
new = palloc0(sizeof(PLpgSQL_stmt_perform));
new->cmd_type = PLPGSQL_STMT_PERFORM;
new->lineno = plpgsql_location_to_lineno(@1);
! new->strict = $2;
! new->expr = $3;
$$ = (PLpgSQL_stmt *)new;
}
***************
*** 2207,2212 **** opt_exitcond : ';'
--- 2209,2223 ----
{ $$ = $2; }
;
+ opt_strict :
+ {
+ $$ = false;
+ }
+ | K_STRICT
+ {
+ $$ = true;
+ }
+
/*
* need to allow DATUM because scanner will have tried to resolve as variable
*/
***************
*** 2665,2679 **** make_execsql_stmt(int firsttoken, int location)
{
prev_tok = tok;
tok = yylex();
! if (have_into && into_end_loc < 0)
! into_end_loc = yylloc; /* token after the INTO part */
if (tok == ';')
break;
if (tok == 0)
yyerror("unexpected end of function definition");
if (tok == K_INTO && prev_tok != K_INSERT)
{
if (have_into)
yyerror("INTO specified more than once");
have_into = true;
--- 2676,2698 ----
{
prev_tok = tok;
tok = yylex();
! if ((have_strict || have_into) && into_end_loc < 0)
! into_end_loc = yylloc; /* token after the INTO (or STRICT) part */
if (tok == ';')
break;
if (tok == 0)
yyerror("unexpected end of function definition");
+ if (tok == K_STRICT)
+ {
+ into_start_loc = yylloc;
+ have_strict = true;
+ }
+
if (tok == K_INTO && prev_tok != K_INSERT)
{
+ if (have_strict)
+ yyerror("STRICT must be part of INTO clause of INTO is specified");
if (have_into)
yyerror("INTO specified more than once");
have_into = true;
***************
*** 2686,2692 **** make_execsql_stmt(int firsttoken, int location)
plpgsql_IdentifierLookup = save_IdentifierLookup;
! if (have_into)
{
/*
* Insert an appropriate number of spaces corresponding to the
--- 2705,2711 ----
plpgsql_IdentifierLookup = save_IdentifierLookup;
! if (have_strict || have_into)
{
/*
* Insert an appropriate number of spaces corresponding to the
*** a/src/pl/plpgsql/src/plpgsql.h
--- b/src/pl/plpgsql/src/plpgsql.h
***************
*** 375,380 **** typedef struct
--- 375,381 ----
{ /* PERFORM statement */
int cmd_type;
int lineno;
+ bool strict;
PLpgSQL_expr *expr;
} PLpgSQL_stmt_perform;
Marko Tiikkaja <pgmail@joh.to> writes:
Courtesy of me, Christmas comes a bit early this year. I wrote a patch
which allows you to add STRICT into PERFORM and INSERT/UPDATE/DELETE
without specifying an INTO clause.
What is the use-case for this? Won't this result in the word STRICT
becoming effectively reserved in contexts where it currently is not?
(IOW, even if the feature is useful, I've got considerable doubts about
this syntax for it. The INTO clause is an ugly, badly designed kluge
already --- let's not make another one just like it.)
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 12/21/12 4:39 PM, Tom Lane wrote:
What is the use-case for this?
Currently, the way to do this would be something like:
DECLARE
_ok bool;
BEGIN
UPDATE foo .. RETURNING TRUE INTO STRICT _ok;
We have a lot of code like this, and I bet others do as well.
Won't this result in the word STRICT
becoming effectively reserved in contexts where it currently is not?
It will, which probably is not ideal if it can be avoided. I also
considered syntax like INTO STRICT NULL, but that felt a bit ugly. It
would be great if someone had any smart ideas about the syntax.
Regards,
Marko Tiikkaja
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 12/21/12 4:49 PM, I wrote:
On 12/21/12 4:39 PM, Tom Lane wrote:
What is the use-case for this?
Currently, the way to do this would be something like:
I realize I didn't really answer the question.
The use case is when you're UPDATEing or DELETEing a row and you want to
quickly assert that there should be exactly one row. For example, if
you've previously locked a row with SELECT .. FOR UPDATE, and now you
want to UPDATE or DELETE it, it better be there (or you have a bug
somewhere).
Regards,
Marko Tiikkaja
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 12/21/12 4:49 PM, I wrote:
Won't this result in the word STRICT
becoming effectively reserved in contexts where it currently is not?It will, which probably is not ideal if it can be avoided. I also
considered syntax like INTO STRICT NULL, but that felt a bit ugly. It
would be great if someone had any smart ideas about the syntax.
Another idea would be to force the STRICT to be immediately after
INSERT, UPDATE or DELETE.
Regards,
Marko Tiikkaja
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
2012/12/21 Marko Tiikkaja <pgmail@joh.to>:
On 12/21/12 4:49 PM, I wrote:
On 12/21/12 4:39 PM, Tom Lane wrote:
What is the use-case for this?
Currently, the way to do this would be something like:
I realize I didn't really answer the question.
The use case is when you're UPDATEing or DELETEing a row and you want to
quickly assert that there should be exactly one row. For example, if you've
previously locked a row with SELECT .. FOR UPDATE, and now you want to
UPDATE or DELETE it, it better be there (or you have a bug somewhere).
yes, it has sense
probably only after keyword it should be simple implementable
Regards
Pavel
Regards,
Marko Tiikkaja--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Dec 21, 2012 at 10:39 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Marko Tiikkaja <pgmail@joh.to> writes:
Courtesy of me, Christmas comes a bit early this year. I wrote a patch
which allows you to add STRICT into PERFORM and INSERT/UPDATE/DELETE
without specifying an INTO clause.What is the use-case for this? Won't this result in the word STRICT
becoming effectively reserved in contexts where it currently is not?
(IOW, even if the feature is useful, I've got considerable doubts about
this syntax for it. The INTO clause is an ugly, badly designed kluge
already --- let's not make another one just like it.)
Yep, the use case for this seems mighty narrow to me.
I could use GET DIAGNOSTICS to determine if nothing got altered, and
it seems likely to me that expressly doing this via IF/ELSE/END IF would
be easier to read in function code than a somewhat magic STRICT
side-effect.
I certainly appreciate that brevity can make things more readable, it's
just
that I'm not sure that is much of a help here.
This is adding specific syntax for what seems like an unusual case to me,
which seems like an unworthwhile complication.
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"
-----Original Message-----
From: pgsql-hackers-owner@postgresql.org [mailto:pgsql-hackers-
owner@postgresql.org] On Behalf Of Marko Tiikkaja
Sent: Friday, December 21, 2012 10:53 AM
To: Tom Lane
Cc: PostgreSQL-development
Subject: Re: [HACKERS] PL/PgSQL STRICTOn 12/21/12 4:49 PM, I wrote:
On 12/21/12 4:39 PM, Tom Lane wrote:
What is the use-case for this?
Currently, the way to do this would be something like:
I realize I didn't really answer the question.
The use case is when you're UPDATEing or DELETEing a row and you want to
quickly assert that there should be exactly one row. For example, if
you've
previously locked a row with SELECT .. FOR UPDATE, and now you want to
UPDATE or DELETE it, it better be there (or you have a bug somewhere).
There had better be exactly one row - but who cares whether that is the row
we were actually expecting to delete/update...
I've recently had the experience of missing a "WHERE pk = ..." clause in an
UPDATE statement inside a function so I do see the value in having an "easy
to implement" safety idiom along these lines.
Along the lines of "EXPLAIN (options) CMD" would something like
"UPDATE|DELETE (STRICT) identifier" work?
David J.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Marko Tiikkaja <pgmail@joh.to> writes:
Another idea would be to force the STRICT to be immediately after
INSERT, UPDATE or DELETE.
What about before it, ie
STRICT UPDATE ...
This should dodge the problem of possible conflict with table names,
and it seems to me to read more naturally too.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
2012/12/21 Tom Lane <tgl@sss.pgh.pa.us>:
Marko Tiikkaja <pgmail@joh.to> writes:
Another idea would be to force the STRICT to be immediately after
INSERT, UPDATE or DELETE.What about before it, ie
STRICT UPDATE ...
This should dodge the problem of possible conflict with table names,
and it seems to me to read more naturally too.
+1
Pavel
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 12/21/12 5:09 PM, Christopher Browne wrote:
I could use GET DIAGNOSTICS to determine if nothing got altered, and
it seems likely to me that expressly doing this via IF/ELSE/END IF would
be easier to read in function code than a somewhat magic STRICT
side-effect.
STRICT is used in INTO, so PL/PgSQL users should already have an idea
what it's going to do outside of INTO.
I certainly appreciate that brevity can make things more readable, it's
just
that I'm not sure that is much of a help here.This is adding specific syntax for what seems like an unusual case to me,
which seems like an unworthwhile complication.
A quick grep suggests that our (the company I work for) code base has
160 occurrences of INSERT/UPDATE/DELETE followed by IF NOT FOUND THEN
RAISE EXCEPTION. So it doesn't seem like an unusual case to me.
Of course, some of them couldn't use STRICT because they are expected to
happen (in which case they can send a more descriptive error message),
but most of them could.
Regards,
Marko Tiikkaja
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 12/21/12 5:22 PM, Tom Lane wrote:
Marko Tiikkaja <pgmail@joh.to> writes:
Another idea would be to force the STRICT to be immediately after
INSERT, UPDATE or DELETE.What about before it, ie
STRICT UPDATE ...
This should dodge the problem of possible conflict with table names,
and it seems to me to read more naturally too.
Yeah, putting STRICT after the command wouldn't work for UPDATE.
I like this one best so far, so I'm going with this syntax for the next
version of the patch.
Regards,
Marko Tiikkaja
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Dec 21, 2012 at 4:22 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
What about before it, ie
STRICT UPDATE ...
+1 from me too.
This feature would be awesome.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Christopher Browne <cbbrowne@gmail.com> writes:
This is adding specific syntax for what seems like an unusual case to me,
which seems like an unworthwhile complication.
That was my first reaction too, but Marko's followon examples seem to
make a reasonable case for it. There are many situations where you
expect an UPDATE or DELETE to hit exactly one row. Often, programmers
won't bother to add code to check that it did ... but if a one-word
addition to the command can provide such a check, it seems more likely
that they would add the check.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
2012/12/21 Tom Lane <tgl@sss.pgh.pa.us>:
Christopher Browne <cbbrowne@gmail.com> writes:
This is adding specific syntax for what seems like an unusual case to me,
which seems like an unworthwhile complication.That was my first reaction too, but Marko's followon examples seem to
make a reasonable case for it. There are many situations where you
expect an UPDATE or DELETE to hit exactly one row. Often, programmers
won't bother to add code to check that it did ... but if a one-word
addition to the command can provide such a check, it seems more likely
that they would add the check.
and it can be used for optimization - it can be optimized for fast first row
Regards
Pavel
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Dec 21, 2012 at 4:53 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
That was my first reaction too, but Marko's followon examples seem to
make a reasonable case for it. There are many situations where you
expect an UPDATE or DELETE to hit exactly one row. Often, programmers
won't bother to add code to check that it did ... but if a one-word
addition to the command can provide such a check, it seems more likely
that they would add the check.
Very true.
When I was a PL/PgSQL beginner a few years ago I did exactly that, I
didn't check if the update actually updated any row, I didn't know it
could fail, and felt extremely worried and stupid when I realised
this. I spent an entire day going through all functions fixing this
problem at all places. The fix was not beautiful and it bugged me
there was not a prettier way to fix it.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, 21 Dec 2012 16:14:19 +0100, I wrote:
I wrote a patch
which allows you to add STRICT into PERFORM and INSERT/UPDATE/DELETE
without specifying an INTO clause.
Here's the second version of the patch, addressing the syntax issues. I
also couldn't make the grammar work with PERFORM STRICT, so I allowed
STRICT SELECT instead.
Any feedback welcome.
Regards,
Marko Tiikkaja
Attachments:
plpgsql_strict2.patchapplication/octet-stream; name=plpgsql_strict2.patchDownload
*** a/doc/src/sgml/plpgsql.sgml
--- b/doc/src/sgml/plpgsql.sgml
***************
*** 1072,1077 **** BEGIN
--- 1072,1094 ----
RAISE EXCEPTION 'employee % not unique', myname;
END;
</programlisting>
+ </para>
+
+ <para>
+ If you're not interested in the results of the query, but you want
+ to enforce that exactly one row was affected, you can use
+ <literal>STRICT</> at the beginning of the SQL statement,
+ for example:
+
+ <programlisting>
+ STRICT UPDATE emp SET last_seen = current_date WHERE empname = myname;
+ </programlisting>
+
+ If <literal>STRICT</> is specified at the beginning of the statement,
+ an <literal>INTO</> clause must not be present.
+ </para>
+
+ <para>
Successful execution of a command with <literal>STRICT</>
always sets <literal>FOUND</literal> to true.
</para>
*** a/src/pl/plpgsql/src/pl_exec.c
--- b/src/pl/plpgsql/src/pl_exec.c
***************
*** 3201,3217 **** exec_stmt_execsql(PLpgSQL_execstate *estate,
paramLI = setup_param_list(estate, expr);
/*
! * If we have INTO, then we only need one row back ... but if we have INTO
* STRICT, ask for two rows, so that we can verify the statement returns
* only one. INSERT/UPDATE/DELETE are always treated strictly. Without
! * INTO, just run the statement to completion (tcount = 0).
*
* We could just ask for two rows always when using INTO, but there are
* some cases where demanding the extra row costs significant time, eg by
* forcing completion of a sequential scan. So don't do it unless we need
* to enforce strictness.
*/
! if (stmt->into)
{
if (stmt->strict || stmt->mod_stmt)
tcount = 2;
--- 3201,3217 ----
paramLI = setup_param_list(estate, expr);
/*
! * If we have INTO, then we only need one row back ... but if we have
* STRICT, ask for two rows, so that we can verify the statement returns
* only one. INSERT/UPDATE/DELETE are always treated strictly. Without
! * INTO or STRICT, just run the statement to completion (tcount = 0).
*
* We could just ask for two rows always when using INTO, but there are
* some cases where demanding the extra row costs significant time, eg by
* forcing completion of a sequential scan. So don't do it unless we need
* to enforce strictness.
*/
! if (stmt->into || stmt->strict)
{
if (stmt->strict || stmt->mod_stmt)
tcount = 2;
***************
*** 3337,3348 **** exec_stmt_execsql(PLpgSQL_execstate *estate,
}
else
{
! /* If the statement returned a tuple table, complain */
if (SPI_tuptable != NULL)
! ereport(ERROR,
! (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("query has no destination for result data"),
! (rc == SPI_OK_SELECT) ? errhint("If you want to discard the results of a SELECT, use PERFORM instead.") : 0));
}
if (paramLI)
--- 3337,3376 ----
}
else
{
! if (stmt->strict)
! {
! /*
! * If a statement specified STRICT, and the query didn't find
! * exactly one row, throw an error.
! */
! if (SPI_processed == 0)
! ereport(ERROR,
! (errcode(ERRCODE_NO_DATA_FOUND),
! errmsg("query returned no rows")));
! else if (SPI_processed > 1)
! ereport(ERROR,
! (errcode(ERRCODE_TOO_MANY_ROWS),
! errmsg("query returned more than one row")));
! }
!
! /*
! * If the statement returned a tuple table, complain, unless it's a
! * STRICT SELECT statement.
! * */
if (SPI_tuptable != NULL)
! {
! if (stmt->strict && rc == SPI_OK_SELECT)
! {
! /* Clean up */
! exec_eval_cleanup(estate);
! SPI_freetuptable(SPI_tuptable);
! }
! else
! ereport(ERROR,
! (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("query has no destination for result data"),
! (rc == SPI_OK_SELECT) ? errhint("If you want to discard the results of a SELECT, use PERFORM instead.") : 0));
! }
}
if (paramLI)
*** a/src/pl/plpgsql/src/pl_gram.y
--- b/src/pl/plpgsql/src/pl_gram.y
***************
*** 77,83 **** static PLpgSQL_expr *read_sql_expression2(int until, int until2,
int *endtoken);
static PLpgSQL_expr *read_sql_stmt(const char *sqlstart);
static PLpgSQL_type *read_datatype(int tok);
! static PLpgSQL_stmt *make_execsql_stmt(int firsttoken, int location);
static PLpgSQL_stmt_fetch *read_fetch_direction(void);
static void complete_direction(PLpgSQL_stmt_fetch *fetch,
bool *check_FROM);
--- 77,84 ----
int *endtoken);
static PLpgSQL_expr *read_sql_stmt(const char *sqlstart);
static PLpgSQL_type *read_datatype(int tok);
! static PLpgSQL_stmt *make_execsql_stmt(int firsttoken, int location,
! bool have_strict);
static PLpgSQL_stmt_fetch *read_fetch_direction(void);
static void complete_direction(PLpgSQL_stmt_fetch *fetch,
bool *check_FROM);
***************
*** 1782,1788 **** loop_body : proc_sect K_END K_LOOP opt_label ';'
*/
stmt_execsql : K_INSERT
{
! $$ = make_execsql_stmt(K_INSERT, @1);
}
| T_WORD
{
--- 1783,1796 ----
*/
stmt_execsql : K_INSERT
{
! $$ = make_execsql_stmt(K_INSERT, @1, false);
! }
! | K_STRICT
! {
! int tok;
!
! tok = yylex();
! $$ = make_execsql_stmt(tok, yylloc, true);
}
| T_WORD
{
***************
*** 1792,1798 **** stmt_execsql : K_INSERT
plpgsql_push_back_token(tok);
if (tok == '=' || tok == COLON_EQUALS || tok == '[')
word_is_not_variable(&($1), @1);
! $$ = make_execsql_stmt(T_WORD, @1);
}
| T_CWORD
{
--- 1800,1806 ----
plpgsql_push_back_token(tok);
if (tok == '=' || tok == COLON_EQUALS || tok == '[')
word_is_not_variable(&($1), @1);
! $$ = make_execsql_stmt(T_WORD, @1, false);
}
| T_CWORD
{
***************
*** 1802,1808 **** stmt_execsql : K_INSERT
plpgsql_push_back_token(tok);
if (tok == '=' || tok == COLON_EQUALS || tok == '[')
cword_is_not_variable(&($1), @1);
! $$ = make_execsql_stmt(T_CWORD, @1);
}
;
--- 1810,1816 ----
plpgsql_push_back_token(tok);
if (tok == '=' || tok == COLON_EQUALS || tok == '[')
cword_is_not_variable(&($1), @1);
! $$ = make_execsql_stmt(T_CWORD, @1, false);
}
;
***************
*** 2631,2637 **** read_datatype(int tok)
}
static PLpgSQL_stmt *
! make_execsql_stmt(int firsttoken, int location)
{
StringInfoData ds;
IdentifierLookup save_IdentifierLookup;
--- 2639,2645 ----
}
static PLpgSQL_stmt *
! make_execsql_stmt(int firsttoken, int location, bool have_strict)
{
StringInfoData ds;
IdentifierLookup save_IdentifierLookup;
***************
*** 2642,2648 **** make_execsql_stmt(int firsttoken, int location)
int tok;
int prev_tok;
bool have_into = false;
- bool have_strict = false;
int into_start_loc = -1;
int into_end_loc = -1;
--- 2650,2655 ----
***************
*** 2674,2679 **** make_execsql_stmt(int firsttoken, int location)
--- 2681,2688 ----
if (tok == K_INTO && prev_tok != K_INSERT)
{
+ if (have_strict)
+ yyerror("STRICT must be part of INTO clause of INTO is specified");
if (have_into)
yyerror("INTO specified more than once");
have_into = true;
*** a/src/test/regress/expected/plpgsql.out
--- b/src/test/regress/expected/plpgsql.out
***************
*** 3103,3108 **** end$$ language plpgsql;
--- 3103,3173 ----
select footest();
ERROR: query returned more than one row
CONTEXT: PL/pgSQL function footest() line 5 at EXECUTE statement
+ --
+ -- Test STRICT without INTO
+ --
+ create or replace function footest() returns void as $$
+ begin
+ -- should work
+ strict select 1;
+ end$$ language plpgsql;
+ select footest();
+ footest
+ ---------
+
+ (1 row)
+
+ create or replace function footest() returns void as $$
+ begin
+ -- should fail, no rows
+ strict select 1 where false;
+ end$$ language plpgsql;
+ select footest();
+ ERROR: query returned no rows
+ CONTEXT: PL/pgSQL function footest() line 4 at SQL statement
+ create or replace function footest() returns void as $$
+ begin
+ -- should fail, too many rows
+ strict select 1 from generate_series(1,2);
+ end$$ language plpgsql;
+ select footest();
+ ERROR: query returned more than one row
+ CONTEXT: PL/pgSQL function footest() line 4 at SQL statement
+ create or replace function footest() returns void as $$
+ begin
+ -- should work
+ strict insert into foo values(5,6);
+ end$$ language plpgsql;
+ select footest();
+ footest
+ ---------
+
+ (1 row)
+
+ create or replace function footest() returns void as $$
+ begin
+ -- should fail, no rows
+ strict insert into foo values(5,6) limit 0;
+ end$$ language plpgsql;
+ select footest();
+ ERROR: query returned no rows
+ CONTEXT: PL/pgSQL function footest() line 4 at SQL statement
+ create or replace function footest() returns void as $$
+ begin
+ -- should fail, too many rows
+ strict insert into foo values(5,6),(7,8);
+ end$$ language plpgsql;
+ select footest();
+ ERROR: query returned more than one row
+ CONTEXT: PL/pgSQL function footest() line 4 at SQL statement
+ create or replace function footest() returns void as $$
+ begin
+ -- should fail, no INTO
+ strict insert into foo values(5,6) returning *;
+ end$$ language plpgsql;
+ select footest();
+ ERROR: query has no destination for result data
+ CONTEXT: PL/pgSQL function footest() line 4 at SQL statement
drop function footest();
-- test scrollable cursor support
create function sc_test() returns setof integer as $$
*** a/src/test/regress/sql/plpgsql.sql
--- b/src/test/regress/sql/plpgsql.sql
***************
*** 2585,2590 **** end$$ language plpgsql;
--- 2585,2650 ----
select footest();
+ --
+ -- Test STRICT without INTO
+ --
+
+ create or replace function footest() returns void as $$
+ begin
+ -- should work
+ strict select 1;
+ end$$ language plpgsql;
+
+ select footest();
+
+ create or replace function footest() returns void as $$
+ begin
+ -- should fail, no rows
+ strict select 1 where false;
+ end$$ language plpgsql;
+
+ select footest();
+
+ create or replace function footest() returns void as $$
+ begin
+ -- should fail, too many rows
+ strict select 1 from generate_series(1,2);
+ end$$ language plpgsql;
+
+ select footest();
+
+ create or replace function footest() returns void as $$
+ begin
+ -- should work
+ strict insert into foo values(5,6);
+ end$$ language plpgsql;
+
+ select footest();
+
+ create or replace function footest() returns void as $$
+ begin
+ -- should fail, no rows
+ strict insert into foo values(5,6) limit 0;
+ end$$ language plpgsql;
+
+ select footest();
+
+ create or replace function footest() returns void as $$
+ begin
+ -- should fail, too many rows
+ strict insert into foo values(5,6),(7,8);
+ end$$ language plpgsql;
+
+ select footest();
+
+ create or replace function footest() returns void as $$
+ begin
+ -- should fail, no INTO
+ strict insert into foo values(5,6) returning *;
+ end$$ language plpgsql;
+
+ select footest();
+
drop function footest();
-- test scrollable cursor support
On 1/26/13 11:28 AM, Marko Tiikkaja wrote:
On Fri, 21 Dec 2012 16:14:19 +0100, I wrote:
I wrote a patch
which allows you to add STRICT into PERFORM and INSERT/UPDATE/DELETE
without specifying an INTO clause.Here's the second version of the patch, addressing the syntax issues.
I think the new syntax is horribly ugly. The actual command name should
always come first, not options. What will happen if people add more
options along this line?
I also couldn't make the grammar work with PERFORM STRICT, so I allowed
STRICT SELECT instead.
I don't quite understand the reason for distinguishing PERFORM and
SELECT, but what you are proposing will make this even more confusing.
That said, I don't quite believe in the premise for this patch to begin
with. The supposed analogy is with INTO STRICT. But that is
effectively a variable assignment and checks whether that assignment was
performed correctly. So for scalar variables, this checks that exactly
one value was returned. I'd imagine if we allowed a syntax like ...
INTO (a, b, c), (d, e, f) it would check that exactly two rows were
returned. So this clause basically just ensures that the run-time
behavior is consistent with the appearance of the source code.
What you are now proposing is that STRICT means "one", but that's just
an opinion. The SQL standard recognizes that updating or deleting
nothing is a noteworthy condition, so I could get partially behind this
patch if it just errored out when zero rows are affected, but insisting
on one is just arbitrary.
(Note also that elsewhere STRICT means something like "not null", so the
term is getting a bit overloaded.)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, 01 Feb 2013 18:11:13 +0100, Peter Eisentraut <peter_e@gmx.net>
wrote:
On 1/26/13 11:28 AM, Marko Tiikkaja wrote:
Here's the second version of the patch, addressing the syntax issues.
I think the new syntax is horribly ugly. The actual command name should
always come first, not options. What will happen if people add more
options along this line?
WITH foo AS (..) SELECT ..; doesn't have the command first either.
I don't really see what other plpgsql-specific options we would add..
I also couldn't make the grammar work with PERFORM STRICT, so I allowed
STRICT SELECT instead.I don't quite understand the reason for distinguishing PERFORM and
SELECT, but what you are proposing will make this even more confusing.That said, I don't quite believe in the premise for this patch to begin
with. The supposed analogy is with INTO STRICT. But that is
effectively a variable assignment and checks whether that assignment was
performed correctly. So for scalar variables, this checks that exactly
one value was returned. I'd imagine if we allowed a syntax like ...
INTO (a, b, c), (d, e, f) it would check that exactly two rows were
returned. So this clause basically just ensures that the run-time
behavior is consistent with the appearance of the source code.
Fine, I can see why you see it that way.
What you are now proposing is that STRICT means "one", but that's just
an opinion. The SQL standard recognizes that updating or deleting
nothing is a noteworthy condition, so I could get partially behind this
patch if it just errored out when zero rows are affected, but insisting
on one is just arbitrary.
*shrug*
To me, this makes the most sense. In my experience if you know something
should be there, it's exactly one row and not "one or more". Not throwing
an error on "more than one" would make this feature a lot less useful in
practice.
Regards,
Marko Tiikkaja
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers