From 9c8d0850c367e3f5eaac1f608c183311216f1cb0 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sat, 31 Jul 2004 23:04:58 +0000 Subject: [PATCH] Support "OR condition ..." in plpgsql EXCEPTION clauses to make the syntax more nearly Oracle-equivalent. Allow matching by category as well as specific error code. Document the set of available condition names (or more accurately, synchronize it with the existing documentation). In passing, update errcodes.sgml to include codes added during 7.5 development. --- doc/src/sgml/errcodes.sgml | 74 ++++++++++++++++++++++++----- doc/src/sgml/plpgsql.sgml | 29 +++++++----- src/include/utils/elog.h | 6 ++- src/pl/plpgsql/src/gram.y | 44 ++++++++++++++++-- src/pl/plpgsql/src/pl_exec.c | 58 +++++++++++++++-------- src/pl/plpgsql/src/pl_funcs.c | 12 ++++- src/pl/plpgsql/src/plerrcodes.h | 56 +++++++++------------- src/pl/plpgsql/src/plpgsql.h | 10 +++- src/pl/plpgsql/src/scan.l | 3 +- src/test/regress/expected/plpgsql.out | 87 ++++++++++++++++++++++++++++++++++- src/test/regress/sql/plpgsql.sql | 60 +++++++++++++++++++++++- 11 files changed, 350 insertions(+), 89 deletions(-) diff --git a/doc/src/sgml/errcodes.sgml b/doc/src/sgml/errcodes.sgml index 5d1a770bf8..ca3bc9ca4f 100644 --- a/doc/src/sgml/errcodes.sgml +++ b/doc/src/sgml/errcodes.sgml @@ -1,4 +1,4 @@ - + <productname>PostgreSQL</productname> Error Codes @@ -41,10 +41,18 @@ within the class but do not have any more-specific code assigned. + + The PL/pgSQL condition name for each error code is the + same as the phrase shown in the table, with underscores substituted + for spaces. For example, code 22012, DIVISION BY ZERO, + has condition name DIVISION_BY_ZERO. Condition names can + be written in either upper or lower case. + + @@ -82,27 +90,37 @@ 0100C -WARNING DYNAMIC RESULT SETS RETURNED +DYNAMIC RESULT SETS RETURNED 01008 -WARNING IMPLICIT ZERO BIT PADDING +IMPLICIT ZERO BIT PADDING 01003 -WARNING NULL VALUE ELIMINATED IN SET FUNCTION +NULL VALUE ELIMINATED IN SET FUNCTION + + + +01007 +PRIVILEGE NOT GRANTED + + + +01006 +PRIVILEGE NOT REVOKED 01004 -WARNING STRING DATA RIGHT TRUNCATION +STRING DATA RIGHT TRUNCATION 01P01 -WARNING DEPRECATED FEATURE +DEPRECATED FEATURE @@ -218,7 +236,7 @@ 0F001 -INVALID SPECIFICATION +INVALID LOCATOR SPECIFICATION @@ -272,7 +290,7 @@ 2202E -ARRAY ELEMENT ERROR +ARRAY SUBSCRIPT ERROR @@ -729,6 +747,22 @@ +Class 3B +Savepoint Exception + + + +3B000 +SAVEPOINT EXCEPTION + + + +3B001 +INVALID SAVEPOINT SPECIFICATION + + + + Class 3D Invalid Catalog Name @@ -762,7 +796,7 @@ 40002 -INTEGRITY CONSTRAINT VIOLATION +TRANSACTION INTEGRITY CONSTRAINT VIOLATION @@ -893,7 +927,7 @@ 42P05 -DUPLICATE PSTATEMENT +DUPLICATE PREPARED STATEMENT @@ -963,7 +997,7 @@ 42P14 -INVALID PSTATEMENT DEFINITION +INVALID PREPARED STATEMENT DEFINITION @@ -1135,6 +1169,22 @@ +Class P0 +PL/pgSQL Error + + + +P0000 +PLPGSQL ERROR + + + +P0001 +RAISE EXCEPTION + + + + Class XX Internal Error diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index 7f3f84448d..e44b886214 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -1,5 +1,5 @@ @@ -1816,12 +1816,11 @@ END LOOP; BEGIN statements EXCEPTION - WHEN condition THEN + WHEN condition OR condition ... THEN handler_statements - WHEN condition THEN - handler_statements - ... - + WHEN condition OR condition ... THEN + handler_statements + ... END; @@ -1841,10 +1840,18 @@ END; as though the EXCEPTION clause were not there at all: the error can be caught by an enclosing block with EXCEPTION, or if there is none it aborts processing - of the function. The special condition name OTHERS + of the function. + + + + The condition names can be any of those + shown in . A category name matches + any error within its category. + The special condition name OTHERS matches every error type except QUERY_CANCELED. - (It is possible, but usually not a good idea, to trap + (It is possible, but often unwise, to trap QUERY_CANCELED by name.) + Condition names are not case-sensitive. @@ -1879,9 +1886,9 @@ END; the EXCEPTION clause. The value returned in the RETURN statement will be the incremented value of x, but the effects of the UPDATE command will - have been rolled back. The INSERT command is not rolled - back, however, so the end result is that the database contains - Tom Jones not Joe Jones. + have been rolled back. The INSERT command preceding the + block is not rolled back, however, so the end result is that the database + contains Tom Jones not Joe Jones. diff --git a/src/include/utils/elog.h b/src/include/utils/elog.h index e9f2b0e879..4a2d26ddb9 100644 --- a/src/include/utils/elog.h +++ b/src/include/utils/elog.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/utils/elog.h,v 1.71 2004/07/31 00:45:43 tgl Exp $ + * $PostgreSQL: pgsql/src/include/utils/elog.h,v 1.72 2004/07/31 23:04:55 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -61,6 +61,10 @@ (PGSIXBIT(ch1) + (PGSIXBIT(ch2) << 6) + (PGSIXBIT(ch3) << 12) + \ (PGSIXBIT(ch4) << 18) + (PGSIXBIT(ch5) << 24)) +/* These macros depend on the fact that '0' becomes a zero in SIXBIT */ +#define ERRCODE_TO_CATEGORY(ec) ((ec) & ((1 << 12) - 1)) +#define ERRCODE_IS_CATEGORY(ec) (((ec) & ~((1 << 12) - 1)) == 0) + /* SQLSTATE codes for errors are defined in a separate file */ #include "utils/errcodes.h" diff --git a/src/pl/plpgsql/src/gram.y b/src/pl/plpgsql/src/gram.y index 628c81b570..96c5c61725 100644 --- a/src/pl/plpgsql/src/gram.y +++ b/src/pl/plpgsql/src/gram.y @@ -4,7 +4,7 @@ * procedural language * * IDENTIFICATION - * $PostgreSQL: pgsql/src/pl/plpgsql/src/gram.y,v 1.58 2004/07/31 07:39:20 tgl Exp $ + * $PostgreSQL: pgsql/src/pl/plpgsql/src/gram.y,v 1.59 2004/07/31 23:04:56 tgl Exp $ * * This software is copyrighted by Jan Wieck - Hamburg. * @@ -96,6 +96,7 @@ static void check_assignable(PLpgSQL_datum *datum); PLpgSQL_stmt *stmt; PLpgSQL_stmts *stmts; PLpgSQL_stmt_block *program; + PLpgSQL_condition *condition; PLpgSQL_exception *exception; PLpgSQL_exceptions *exceptions; PLpgSQL_nsitem *nsitem; @@ -135,6 +136,7 @@ static void check_assignable(PLpgSQL_datum *datum); %type exception_sect proc_exceptions %type proc_exception +%type proc_conditions %type raise_params %type raise_level raise_param @@ -181,6 +183,7 @@ static void check_assignable(PLpgSQL_datum *datum); %token K_NOTICE %token K_NULL %token K_OPEN +%token K_OR %token K_PERFORM %token K_ROW_COUNT %token K_RAISE @@ -1563,21 +1566,52 @@ proc_exceptions : proc_exceptions proc_exception } ; -proc_exception : K_WHEN lno opt_lblname K_THEN proc_sect +proc_exception : K_WHEN lno proc_conditions K_THEN proc_sect { PLpgSQL_exception *new; new = malloc(sizeof(PLpgSQL_exception)); memset(new, 0, sizeof(PLpgSQL_exception)); - new->lineno = $2; - new->label = $3; - new->action = $5; + new->lineno = $2; + new->conditions = $3; + new->action = $5; $$ = new; } ; +proc_conditions : proc_conditions K_OR opt_lblname + { + PLpgSQL_condition *new; + PLpgSQL_condition *old; + + new = malloc(sizeof(PLpgSQL_condition)); + memset(new, 0, sizeof(PLpgSQL_condition)); + + new->condname = $3; + new->next = NULL; + + for (old = $1; old->next != NULL; old = old->next) + /* skip */ ; + old->next = new; + + $$ = $1; + } + | opt_lblname + { + PLpgSQL_condition *new; + + new = malloc(sizeof(PLpgSQL_condition)); + memset(new, 0, sizeof(PLpgSQL_condition)); + + new->condname = $1; + new->next = NULL; + + $$ = new; + } + ; + expr_until_semi : { $$ = plpgsql_read_expression(';', ";"); } ; diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c index 0950149a49..4c579223d9 100644 --- a/src/pl/plpgsql/src/pl_exec.c +++ b/src/pl/plpgsql/src/pl_exec.c @@ -3,7 +3,7 @@ * procedural language * * IDENTIFICATION - * $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.110 2004/07/31 20:55:44 tgl Exp $ + * $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.111 2004/07/31 23:04:56 tgl Exp $ * * This software is copyrighted by Jan Wieck - Hamburg. * @@ -795,27 +795,45 @@ copy_rec(PLpgSQL_rec * rec) static bool -exception_matches_label(ErrorData *edata, const char *label) +exception_matches_conditions(ErrorData *edata, PLpgSQL_condition *cond) { - int i; - - /* - * OTHERS matches everything *except* query-canceled; - * if you're foolish enough, you can match that explicitly. - */ - if (pg_strcasecmp(label, "OTHERS") == 0) - { - if (edata->sqlerrcode == ERRCODE_QUERY_CANCELED) - return false; - else - return true; - } - for (i = 0; exception_label_map[i].label != NULL; i++) + for (; cond != NULL; cond = cond->next) { - if (pg_strcasecmp(label, exception_label_map[i].label) == 0) - return (edata->sqlerrcode == exception_label_map[i].sqlerrstate); + const char *condname = cond->condname; + int i; + + /* + * OTHERS matches everything *except* query-canceled; + * if you're foolish enough, you can match that explicitly. + */ + if (pg_strcasecmp(condname, "OTHERS") == 0) + { + if (edata->sqlerrcode == ERRCODE_QUERY_CANCELED) + return false; + else + return true; + } + for (i = 0; exception_label_map[i].label != NULL; i++) + { + if (pg_strcasecmp(condname, exception_label_map[i].label) == 0) + { + int labelerrcode = exception_label_map[i].sqlerrstate; + + /* Exact match? */ + if (edata->sqlerrcode == labelerrcode) + return true; + /* Category match? */ + if (ERRCODE_IS_CATEGORY(labelerrcode) && + ERRCODE_TO_CATEGORY(edata->sqlerrcode) == labelerrcode) + return true; + /* + * You would think we should "break" here, but there are some + * duplicate names in the table, so keep looking. + */ + } + } + /* Should we raise an error if condname is unrecognized?? */ } - /* Should we raise an error if label is unrecognized?? */ return false; } @@ -944,7 +962,7 @@ exec_stmt_block(PLpgSQL_execstate * estate, PLpgSQL_stmt_block * block) { PLpgSQL_exception *exception = exceptions->exceptions[j]; - if (exception_matches_label(edata, exception->label)) + if (exception_matches_conditions(edata, exception->conditions)) { rc = exec_stmts(estate, exception->action); break; diff --git a/src/pl/plpgsql/src/pl_funcs.c b/src/pl/plpgsql/src/pl_funcs.c index b464906752..028bc3836c 100644 --- a/src/pl/plpgsql/src/pl_funcs.c +++ b/src/pl/plpgsql/src/pl_funcs.c @@ -3,7 +3,7 @@ * procedural language * * IDENTIFICATION - * $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_funcs.c,v 1.33 2004/07/31 07:39:20 tgl Exp $ + * $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_funcs.c,v 1.34 2004/07/31 23:04:56 tgl Exp $ * * This software is copyrighted by Jan Wieck - Hamburg. * @@ -615,9 +615,17 @@ dump_block(PLpgSQL_stmt_block * block) for (i = 0; i < block->exceptions->exceptions_used; i++) { PLpgSQL_exception *exc = block->exceptions->exceptions[i]; + PLpgSQL_condition *cond; dump_ind(); - printf(" EXCEPTION WHEN %s THEN\n", exc->label); + printf(" EXCEPTION WHEN "); + for (cond = exc->conditions; cond; cond = cond->next) + { + if (cond != exc->conditions) + printf(" OR "); + printf("%s", cond->condname); + } + printf(" THEN\n"); dump_stmts(exc->action); } } diff --git a/src/pl/plpgsql/src/plerrcodes.h b/src/pl/plpgsql/src/plerrcodes.h index 4e76bde673..2448701d90 100644 --- a/src/pl/plpgsql/src/plerrcodes.h +++ b/src/pl/plpgsql/src/plerrcodes.h @@ -9,22 +9,12 @@ * * Copyright (c) 2003, PostgreSQL Global Development Group * - * $PostgreSQL: pgsql/src/pl/plpgsql/src/plerrcodes.h,v 1.1 2004/07/31 07:39:20 tgl Exp $ + * $PostgreSQL: pgsql/src/pl/plpgsql/src/plerrcodes.h,v 1.2 2004/07/31 23:04:56 tgl Exp $ * *------------------------------------------------------------------------- */ -{ "SUCCESSFUL_COMPLETION", ERRCODE_SUCCESSFUL_COMPLETION }, -{ "WARNING", ERRCODE_WARNING }, -{ "WARNING_DYNAMIC_RESULT_SETS_RETURNED", ERRCODE_WARNING_DYNAMIC_RESULT_SETS_RETURNED }, -{ "WARNING_IMPLICIT_ZERO_BIT_PADDING", ERRCODE_WARNING_IMPLICIT_ZERO_BIT_PADDING }, -{ "WARNING_NULL_VALUE_ELIMINATED_IN_SET_FUNCTION", ERRCODE_WARNING_NULL_VALUE_ELIMINATED_IN_SET_FUNCTION }, -{ "WARNING_PRIVILEGE_NOT_GRANTED", ERRCODE_WARNING_PRIVILEGE_NOT_GRANTED }, -{ "WARNING_PRIVILEGE_NOT_REVOKED", ERRCODE_WARNING_PRIVILEGE_NOT_REVOKED }, -{ "WARNING_STRING_DATA_RIGHT_TRUNCATION", ERRCODE_WARNING_STRING_DATA_RIGHT_TRUNCATION }, -{ "WARNING_DEPRECATED_FEATURE", ERRCODE_WARNING_DEPRECATED_FEATURE }, -{ "NO_DATA", ERRCODE_NO_DATA }, -{ "NO_ADDITIONAL_DYNAMIC_RESULT_SETS_RETURNED", ERRCODE_NO_ADDITIONAL_DYNAMIC_RESULT_SETS_RETURNED }, +/* Success and warnings can't be caught, so omit them from table */ { "SQL_STATEMENT_NOT_YET_COMPLETE", ERRCODE_SQL_STATEMENT_NOT_YET_COMPLETE }, { "CONNECTION_EXCEPTION", ERRCODE_CONNECTION_EXCEPTION }, { "CONNECTION_DOES_NOT_EXIST", ERRCODE_CONNECTION_DOES_NOT_EXIST }, @@ -37,7 +27,7 @@ { "FEATURE_NOT_SUPPORTED", ERRCODE_FEATURE_NOT_SUPPORTED }, { "INVALID_TRANSACTION_INITIATION", ERRCODE_INVALID_TRANSACTION_INITIATION }, { "LOCATOR_EXCEPTION", ERRCODE_LOCATOR_EXCEPTION }, -{ "L_E_INVALID_SPECIFICATION", ERRCODE_L_E_INVALID_SPECIFICATION }, +{ "INVALID_LOCATOR_SPECIFICATION", ERRCODE_L_E_INVALID_SPECIFICATION }, { "INVALID_GRANTOR", ERRCODE_INVALID_GRANTOR }, { "INVALID_GRANT_OPERATION", ERRCODE_INVALID_GRANT_OPERATION }, { "INVALID_ROLE_SPECIFICATION", ERRCODE_INVALID_ROLE_SPECIFICATION }, @@ -53,7 +43,7 @@ { "ESCAPE_CHARACTER_CONFLICT", ERRCODE_ESCAPE_CHARACTER_CONFLICT }, { "INDICATOR_OVERFLOW", ERRCODE_INDICATOR_OVERFLOW }, { "INTERVAL_FIELD_OVERFLOW", ERRCODE_INTERVAL_FIELD_OVERFLOW }, -{ "INVALID_ARGUMENT_FOR_LOG", ERRCODE_INVALID_ARGUMENT_FOR_LOG }, +{ "INVALID_ARGUMENT_FOR_LOGARITHM", ERRCODE_INVALID_ARGUMENT_FOR_LOG }, { "INVALID_ARGUMENT_FOR_POWER_FUNCTION", ERRCODE_INVALID_ARGUMENT_FOR_POWER_FUNCTION }, { "INVALID_ARGUMENT_FOR_WIDTH_BUCKET_FUNCTION", ERRCODE_INVALID_ARGUMENT_FOR_WIDTH_BUCKET_FUNCTION }, { "INVALID_CHARACTER_VALUE_FOR_CAST", ERRCODE_INVALID_CHARACTER_VALUE_FOR_CAST }, @@ -107,30 +97,30 @@ { "DEPENDENT_OBJECTS_STILL_EXIST", ERRCODE_DEPENDENT_OBJECTS_STILL_EXIST }, { "INVALID_TRANSACTION_TERMINATION", ERRCODE_INVALID_TRANSACTION_TERMINATION }, { "SQL_ROUTINE_EXCEPTION", ERRCODE_SQL_ROUTINE_EXCEPTION }, -{ "S_R_E_FUNCTION_EXECUTED_NO_RETURN_STATEMENT", ERRCODE_S_R_E_FUNCTION_EXECUTED_NO_RETURN_STATEMENT }, -{ "S_R_E_MODIFYING_SQL_DATA_NOT_PERMITTED", ERRCODE_S_R_E_MODIFYING_SQL_DATA_NOT_PERMITTED }, -{ "S_R_E_PROHIBITED_SQL_STATEMENT_ATTEMPTED", ERRCODE_S_R_E_PROHIBITED_SQL_STATEMENT_ATTEMPTED }, -{ "S_R_E_READING_SQL_DATA_NOT_PERMITTED", ERRCODE_S_R_E_READING_SQL_DATA_NOT_PERMITTED }, +{ "FUNCTION_EXECUTED_NO_RETURN_STATEMENT", ERRCODE_S_R_E_FUNCTION_EXECUTED_NO_RETURN_STATEMENT }, +{ "MODIFYING_SQL_DATA_NOT_PERMITTED", ERRCODE_S_R_E_MODIFYING_SQL_DATA_NOT_PERMITTED }, +{ "PROHIBITED_SQL_STATEMENT_ATTEMPTED", ERRCODE_S_R_E_PROHIBITED_SQL_STATEMENT_ATTEMPTED }, +{ "READING_SQL_DATA_NOT_PERMITTED", ERRCODE_S_R_E_READING_SQL_DATA_NOT_PERMITTED }, { "INVALID_CURSOR_NAME", ERRCODE_INVALID_CURSOR_NAME }, { "EXTERNAL_ROUTINE_EXCEPTION", ERRCODE_EXTERNAL_ROUTINE_EXCEPTION }, -{ "E_R_E_CONTAINING_SQL_NOT_PERMITTED", ERRCODE_E_R_E_CONTAINING_SQL_NOT_PERMITTED }, -{ "E_R_E_MODIFYING_SQL_DATA_NOT_PERMITTED", ERRCODE_E_R_E_MODIFYING_SQL_DATA_NOT_PERMITTED }, -{ "E_R_E_PROHIBITED_SQL_STATEMENT_ATTEMPTED", ERRCODE_E_R_E_PROHIBITED_SQL_STATEMENT_ATTEMPTED }, -{ "E_R_E_READING_SQL_DATA_NOT_PERMITTED", ERRCODE_E_R_E_READING_SQL_DATA_NOT_PERMITTED }, +{ "CONTAINING_SQL_NOT_PERMITTED", ERRCODE_E_R_E_CONTAINING_SQL_NOT_PERMITTED }, +{ "MODIFYING_SQL_DATA_NOT_PERMITTED", ERRCODE_E_R_E_MODIFYING_SQL_DATA_NOT_PERMITTED }, +{ "PROHIBITED_SQL_STATEMENT_ATTEMPTED", ERRCODE_E_R_E_PROHIBITED_SQL_STATEMENT_ATTEMPTED }, +{ "READING_SQL_DATA_NOT_PERMITTED", ERRCODE_E_R_E_READING_SQL_DATA_NOT_PERMITTED }, { "EXTERNAL_ROUTINE_INVOCATION_EXCEPTION", ERRCODE_EXTERNAL_ROUTINE_INVOCATION_EXCEPTION }, -{ "E_R_I_E_INVALID_SQLSTATE_RETURNED", ERRCODE_E_R_I_E_INVALID_SQLSTATE_RETURNED }, -{ "E_R_I_E_NULL_VALUE_NOT_ALLOWED", ERRCODE_E_R_I_E_NULL_VALUE_NOT_ALLOWED }, -{ "E_R_I_E_TRIGGER_PROTOCOL_VIOLATED", ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED }, -{ "E_R_I_E_SRF_PROTOCOL_VIOLATED", ERRCODE_E_R_I_E_SRF_PROTOCOL_VIOLATED }, +{ "INVALID_SQLSTATE_RETURNED", ERRCODE_E_R_I_E_INVALID_SQLSTATE_RETURNED }, +{ "NULL_VALUE_NOT_ALLOWED", ERRCODE_E_R_I_E_NULL_VALUE_NOT_ALLOWED }, +{ "TRIGGER_PROTOCOL_VIOLATED", ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED }, +{ "SRF_PROTOCOL_VIOLATED", ERRCODE_E_R_I_E_SRF_PROTOCOL_VIOLATED }, { "SAVEPOINT_EXCEPTION", ERRCODE_SAVEPOINT_EXCEPTION }, -{ "S_E_INVALID_SPECIFICATION", ERRCODE_S_E_INVALID_SPECIFICATION }, +{ "INVALID_SAVEPOINT_SPECIFICATION", ERRCODE_S_E_INVALID_SPECIFICATION }, { "INVALID_CATALOG_NAME", ERRCODE_INVALID_CATALOG_NAME }, { "INVALID_SCHEMA_NAME", ERRCODE_INVALID_SCHEMA_NAME }, { "TRANSACTION_ROLLBACK", ERRCODE_TRANSACTION_ROLLBACK }, -{ "T_R_INTEGRITY_CONSTRAINT_VIOLATION", ERRCODE_T_R_INTEGRITY_CONSTRAINT_VIOLATION }, -{ "T_R_SERIALIZATION_FAILURE", ERRCODE_T_R_SERIALIZATION_FAILURE }, -{ "T_R_STATEMENT_COMPLETION_UNKNOWN", ERRCODE_T_R_STATEMENT_COMPLETION_UNKNOWN }, -{ "T_R_DEADLOCK_DETECTED", ERRCODE_T_R_DEADLOCK_DETECTED }, +{ "TRANSACTION_INTEGRITY_CONSTRAINT_VIOLATION", ERRCODE_T_R_INTEGRITY_CONSTRAINT_VIOLATION }, +{ "SERIALIZATION_FAILURE", ERRCODE_T_R_SERIALIZATION_FAILURE }, +{ "STATEMENT_COMPLETION_UNKNOWN", ERRCODE_T_R_STATEMENT_COMPLETION_UNKNOWN }, +{ "DEADLOCK_DETECTED", ERRCODE_T_R_DEADLOCK_DETECTED }, { "SYNTAX_ERROR_OR_ACCESS_RULE_VIOLATION", ERRCODE_SYNTAX_ERROR_OR_ACCESS_RULE_VIOLATION }, { "SYNTAX_ERROR", ERRCODE_SYNTAX_ERROR }, { "INSUFFICIENT_PRIVILEGE", ERRCODE_INSUFFICIENT_PRIVILEGE }, @@ -156,7 +146,7 @@ { "DUPLICATE_CURSOR", ERRCODE_DUPLICATE_CURSOR }, { "DUPLICATE_DATABASE", ERRCODE_DUPLICATE_DATABASE }, { "DUPLICATE_FUNCTION", ERRCODE_DUPLICATE_FUNCTION }, -{ "DUPLICATE_PSTATEMENT", ERRCODE_DUPLICATE_PSTATEMENT }, +{ "DUPLICATE_PREPARED_STATEMENT", ERRCODE_DUPLICATE_PSTATEMENT }, { "DUPLICATE_SCHEMA", ERRCODE_DUPLICATE_SCHEMA }, { "DUPLICATE_TABLE", ERRCODE_DUPLICATE_TABLE }, { "DUPLICATE_ALIAS", ERRCODE_DUPLICATE_ALIAS }, @@ -170,7 +160,7 @@ { "INVALID_CURSOR_DEFINITION", ERRCODE_INVALID_CURSOR_DEFINITION }, { "INVALID_DATABASE_DEFINITION", ERRCODE_INVALID_DATABASE_DEFINITION }, { "INVALID_FUNCTION_DEFINITION", ERRCODE_INVALID_FUNCTION_DEFINITION }, -{ "INVALID_PSTATEMENT_DEFINITION", ERRCODE_INVALID_PSTATEMENT_DEFINITION }, +{ "INVALID_PREPARED_STATEMENT_DEFINITION", ERRCODE_INVALID_PSTATEMENT_DEFINITION }, { "INVALID_SCHEMA_DEFINITION", ERRCODE_INVALID_SCHEMA_DEFINITION }, { "INVALID_TABLE_DEFINITION", ERRCODE_INVALID_TABLE_DEFINITION }, { "INVALID_OBJECT_DEFINITION", ERRCODE_INVALID_OBJECT_DEFINITION }, diff --git a/src/pl/plpgsql/src/plpgsql.h b/src/pl/plpgsql/src/plpgsql.h index 28868cf731..d57d4a7025 100644 --- a/src/pl/plpgsql/src/plpgsql.h +++ b/src/pl/plpgsql/src/plpgsql.h @@ -3,7 +3,7 @@ * procedural language * * IDENTIFICATION - * $PostgreSQL: pgsql/src/pl/plpgsql/src/plpgsql.h,v 1.48 2004/07/31 07:39:20 tgl Exp $ + * $PostgreSQL: pgsql/src/pl/plpgsql/src/plpgsql.h,v 1.49 2004/07/31 23:04:56 tgl Exp $ * * This software is copyrighted by Jan Wieck - Hamburg. * @@ -322,10 +322,16 @@ typedef struct } PLpgSQL_stmts; +typedef struct PLpgSQL_condition +{ /* One EXCEPTION condition name */ + char *condname; + struct PLpgSQL_condition *next; +} PLpgSQL_condition; + typedef struct { /* One EXCEPTION ... WHEN clause */ int lineno; - char *label; + PLpgSQL_condition *conditions; PLpgSQL_stmts *action; } PLpgSQL_exception; diff --git a/src/pl/plpgsql/src/scan.l b/src/pl/plpgsql/src/scan.l index d369170cf3..5e6ccd68d7 100644 --- a/src/pl/plpgsql/src/scan.l +++ b/src/pl/plpgsql/src/scan.l @@ -4,7 +4,7 @@ * procedural language * * IDENTIFICATION - * $PostgreSQL: pgsql/src/pl/plpgsql/src/scan.l,v 1.35 2004/06/03 22:56:43 tgl Exp $ + * $PostgreSQL: pgsql/src/pl/plpgsql/src/scan.l,v 1.36 2004/07/31 23:04:56 tgl Exp $ * * This software is copyrighted by Jan Wieck - Hamburg. * @@ -165,6 +165,7 @@ not { return K_NOT; } notice { return K_NOTICE; } null { return K_NULL; } open { return K_OPEN; } +or { return K_OR; } perform { return K_PERFORM; } raise { return K_RAISE; } rename { return K_RENAME; } diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out index 6907905cc5..73962dbb37 100644 --- a/src/test/regress/expected/plpgsql.out +++ b/src/test/regress/expected/plpgsql.out @@ -1798,7 +1798,7 @@ drop table perform_test; -- create function trap_zero_divide(int) returns int as $$ declare x int; -declare sx smallint; + sx smallint; begin begin -- start a subtransaction raise notice 'should see this'; @@ -1850,3 +1850,88 @@ NOTICE: should see this NOTICE: should see this only if -100 <> 0 NOTICE: should see this only if -100 fits in smallint ERROR: -100 is less than zero +create function trap_matching_test(int) returns int as $$ +declare x int; + sx smallint; + y int; +begin + begin -- start a subtransaction + x := 100 / $1; + sx := $1; + select into y unique1 from tenk1 where unique2 = + (select unique2 from tenk1 b where ten = $1); + exception + when data_exception then -- category match + raise notice 'caught data_exception'; + x := -1; + when NUMERIC_VALUE_OUT_OF_RANGE OR CARDINALITY_VIOLATION then + raise notice 'caught numeric_value_out_of_range or cardinality_violation'; + x := -2; + end; + return x; +end$$ language plpgsql; +select trap_matching_test(50); + trap_matching_test +-------------------- + 2 +(1 row) + +select trap_matching_test(0); +NOTICE: caught data_exception + trap_matching_test +-------------------- + -1 +(1 row) + +select trap_matching_test(100000); +NOTICE: caught data_exception + trap_matching_test +-------------------- + -1 +(1 row) + +select trap_matching_test(1); +NOTICE: caught numeric_value_out_of_range or cardinality_violation + trap_matching_test +-------------------- + -2 +(1 row) + +create temp table foo (f1 int); +create function blockme() returns int as $$ +declare x int; +begin + x := 1; + insert into foo values(x); + begin + x := x + 1; + insert into foo values(x); + -- we assume this will take longer than 1 second: + select count(*) into x from tenk1 a, tenk1 b, tenk1 c; + exception + when others then + raise notice 'caught others?'; + return -1; + when query_canceled then + raise notice 'nyeah nyeah, can''t stop me'; + x := x * 10; + end; + insert into foo values(x); + return x; +end$$ language plpgsql; +set statement_timeout to 1000; +select blockme(); +NOTICE: nyeah nyeah, can't stop me + blockme +--------- + 20 +(1 row) + +reset statement_timeout; +select * from foo; + f1 +---- + 1 + 20 +(2 rows) + diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql index 48a78196a8..948a02ac0e 100644 --- a/src/test/regress/sql/plpgsql.sql +++ b/src/test/regress/sql/plpgsql.sql @@ -1615,7 +1615,7 @@ drop table perform_test; create function trap_zero_divide(int) returns int as $$ declare x int; -declare sx smallint; + sx smallint; begin begin -- start a subtransaction raise notice 'should see this'; @@ -1641,3 +1641,61 @@ select trap_zero_divide(50); select trap_zero_divide(0); select trap_zero_divide(100000); select trap_zero_divide(-100); + +create function trap_matching_test(int) returns int as $$ +declare x int; + sx smallint; + y int; +begin + begin -- start a subtransaction + x := 100 / $1; + sx := $1; + select into y unique1 from tenk1 where unique2 = + (select unique2 from tenk1 b where ten = $1); + exception + when data_exception then -- category match + raise notice 'caught data_exception'; + x := -1; + when NUMERIC_VALUE_OUT_OF_RANGE OR CARDINALITY_VIOLATION then + raise notice 'caught numeric_value_out_of_range or cardinality_violation'; + x := -2; + end; + return x; +end$$ language plpgsql; + +select trap_matching_test(50); +select trap_matching_test(0); +select trap_matching_test(100000); +select trap_matching_test(1); + +create temp table foo (f1 int); + +create function blockme() returns int as $$ +declare x int; +begin + x := 1; + insert into foo values(x); + begin + x := x + 1; + insert into foo values(x); + -- we assume this will take longer than 1 second: + select count(*) into x from tenk1 a, tenk1 b, tenk1 c; + exception + when others then + raise notice 'caught others?'; + return -1; + when query_canceled then + raise notice 'nyeah nyeah, can''t stop me'; + x := x * 10; + end; + insert into foo values(x); + return x; +end$$ language plpgsql; + +set statement_timeout to 1000; + +select blockme(); + +reset statement_timeout; + +select * from foo; -- 2.11.0