*** a/doc/src/sgml/plpgsql.sgml --- b/doc/src/sgml/plpgsql.sgml *************** *** 2635,2645 **** GET STACKED DIAGNOSTICS variable = item< --- 2635,2670 ---- the SQLSTATE error code of the exception + COLUMN_NAME + text + the name of column related to exception + + + CONSTRAINT_NAME + text + the name of constraint related to exception + + + DATATYPE_NAME + text + the name of datatype related to exception + + MESSAGE_TEXT text the text of the exception's primary message + TABLE_NAME + text + the name of table related to exception + + + SCHEMA_NAME + text + the name of schema related to exception + + PG_EXCEPTION_DETAIL text the text of the exception's detail message, if any *************** *** 3325,3330 **** RAISE NOTICE 'Calling cs_create_job(%)', v_job_id; --- 3350,3390 ---- five-character SQLSTATE code. + + + COLUMN_NAME + + Specifies the column name field of exception + + + + + CONSTRAINT_NAME + + Specifies the constraint name field of exception + + + + + DATATYPE_NAME + + Specifies the data type name field of exception + + + + + TABLE_NAME + + Specifies the table name field of exception + + + + + SCHEMA_NAME + + Specifies the schema name field of exception + + *** a/src/pl/plpgsql/src/pl_exec.c --- b/src/pl/plpgsql/src/pl_exec.c *************** *** 1569,1579 **** exec_stmt_getdiag(PLpgSQL_execstate *estate, PLpgSQL_stmt_getdiag *stmt) --- 1569,1604 ---- unpack_sql_state(estate->cur_error->sqlerrcode)); break; + case PLPGSQL_GETDIAG_COLUMN_NAME: + exec_assign_c_string(estate, var, + estate->cur_error->column_name); + break; + + case PLPGSQL_GETDIAG_CONSTRAINT_NAME: + exec_assign_c_string(estate, var, + estate->cur_error->constraint_name); + break; + + case PLPGSQL_GETDIAG_DATATYPE_NAME: + exec_assign_c_string(estate, var, + estate->cur_error->datatype_name); + break; + case PLPGSQL_GETDIAG_MESSAGE_TEXT: exec_assign_c_string(estate, var, estate->cur_error->message); break; + case PLPGSQL_GETDIAG_TABLE_NAME: + exec_assign_c_string(estate, var, + estate->cur_error->table_name); + break; + + case PLPGSQL_GETDIAG_SCHEMA_NAME: + exec_assign_c_string(estate, var, + estate->cur_error->schema_name); + break; + default: elog(ERROR, "unrecognized diagnostic item kind: %d", diag_item->kind); *************** *** 2799,2804 **** exec_init_tuple_store(PLpgSQL_execstate *estate) --- 2824,2839 ---- estate->rettupdesc = rsi->expectedDesc; } + #define SET_RAISE_OPTION_TEXT(opt, name) \ + do { \ + if (opt) \ + ereport(ERROR, \ + (errcode(ERRCODE_SYNTAX_ERROR), \ + errmsg("RAISE option already specified: %s", \ + name))); \ + opt = pstrdup(extval); \ + } while (0) + /* ---------- * exec_stmt_raise Build a message and throw it with elog() * ---------- *************** *** 2811,2816 **** exec_stmt_raise(PLpgSQL_execstate *estate, PLpgSQL_stmt_raise *stmt) --- 2846,2856 ---- char *err_message = NULL; char *err_detail = NULL; char *err_hint = NULL; + char *err_column_name = NULL; + char *err_constraint_name = NULL; + char *err_datatype_name = NULL; + char *err_table_name = NULL; + char *err_schema_name = NULL; ListCell *lc; /* RAISE with no parameters: re-throw current exception */ *************** *** 2926,2955 **** exec_stmt_raise(PLpgSQL_execstate *estate, PLpgSQL_stmt_raise *stmt) err_code = plpgsql_recognize_err_condition(extval, true); condname = pstrdup(extval); break; case PLPGSQL_RAISEOPTION_MESSAGE: ! if (err_message) ! ereport(ERROR, ! (errcode(ERRCODE_SYNTAX_ERROR), ! errmsg("RAISE option already specified: %s", ! "MESSAGE"))); ! err_message = pstrdup(extval); break; case PLPGSQL_RAISEOPTION_DETAIL: ! if (err_detail) ! ereport(ERROR, ! (errcode(ERRCODE_SYNTAX_ERROR), ! errmsg("RAISE option already specified: %s", ! "DETAIL"))); ! err_detail = pstrdup(extval); break; case PLPGSQL_RAISEOPTION_HINT: ! if (err_hint) ! ereport(ERROR, ! (errcode(ERRCODE_SYNTAX_ERROR), ! errmsg("RAISE option already specified: %s", ! "HINT"))); ! err_hint = pstrdup(extval); break; default: elog(ERROR, "unrecognized raise option: %d", opt->opt_type); } --- 2966,3004 ---- err_code = plpgsql_recognize_err_condition(extval, true); condname = pstrdup(extval); break; + case PLPGSQL_RAISEOPTION_MESSAGE: ! SET_RAISE_OPTION_TEXT(err_message, "MESSAGE"); break; + case PLPGSQL_RAISEOPTION_DETAIL: ! SET_RAISE_OPTION_TEXT(err_detail, "DETAIL"); break; + case PLPGSQL_RAISEOPTION_HINT: ! SET_RAISE_OPTION_TEXT(err_hint, "HINT"); ! break; ! ! case PLPGSQL_RAISEOPTION_COLUMN_NAME: ! SET_RAISE_OPTION_TEXT(err_column_name, "COLUMN_NAME"); break; + + case PLPGSQL_RAISEOPTION_CONSTRAINT_NAME: + SET_RAISE_OPTION_TEXT(err_constraint_name, "CONSTRAINT_NAME"); + break; + + case PLPGSQL_RAISEOPTION_DATATYPE_NAME: + SET_RAISE_OPTION_TEXT(err_datatype_name, "DATATYPE_NAME"); + break; + + case PLPGSQL_RAISEOPTION_TABLE_NAME: + SET_RAISE_OPTION_TEXT(err_table_name, "TABLE_NAME"); + break; + + case PLPGSQL_RAISEOPTION_SCHEMA_NAME: + SET_RAISE_OPTION_TEXT(err_schema_name, "SCHEMA_NAME"); + break; + default: elog(ERROR, "unrecognized raise option: %d", opt->opt_type); } *************** *** 2982,2988 **** exec_stmt_raise(PLpgSQL_execstate *estate, PLpgSQL_stmt_raise *stmt) (err_code ? errcode(err_code) : 0, errmsg_internal("%s", err_message), (err_detail != NULL) ? errdetail_internal("%s", err_detail) : 0, ! (err_hint != NULL) ? errhint("%s", err_hint) : 0)); estate->err_text = NULL; /* un-suppress... */ --- 3031,3047 ---- (err_code ? errcode(err_code) : 0, errmsg_internal("%s", err_message), (err_detail != NULL) ? errdetail_internal("%s", err_detail) : 0, ! (err_hint != NULL) ? errhint("%s", err_hint) : 0, ! (err_column_name != NULL) ? ! err_generic_string(PG_DIAG_COLUMN_NAME, err_column_name) : 0, ! (err_constraint_name != NULL) ? ! err_generic_string(PG_DIAG_CONSTRAINT_NAME, err_constraint_name) : 0, ! (err_datatype_name != NULL) ? ! err_generic_string(PG_DIAG_DATATYPE_NAME, err_datatype_name) : 0, ! (err_table_name != NULL) ? ! err_generic_string(PG_DIAG_TABLE_NAME, err_table_name) : 0, ! (err_schema_name != NULL) ? ! err_generic_string(PG_DIAG_SCHEMA_NAME, err_schema_name) : 0)); estate->err_text = NULL; /* un-suppress... */ *************** *** 2994,2999 **** exec_stmt_raise(PLpgSQL_execstate *estate, PLpgSQL_stmt_raise *stmt) --- 3053,3068 ---- pfree(err_detail); if (err_hint != NULL) pfree(err_hint); + if (err_column_name != NULL) + pfree(err_column_name); + if (err_constraint_name != NULL) + pfree(err_constraint_name); + if (err_datatype_name != NULL) + pfree(err_datatype_name); + if (err_table_name != NULL) + pfree(err_table_name); + if (err_schema_name != NULL) + pfree(err_schema_name); return PLPGSQL_RC_OK; } *** a/src/pl/plpgsql/src/pl_funcs.c --- b/src/pl/plpgsql/src/pl_funcs.c *************** *** 285,292 **** plpgsql_getdiag_kindname(int kind) --- 285,302 ---- return "PG_EXCEPTION_HINT"; case PLPGSQL_GETDIAG_RETURNED_SQLSTATE: return "RETURNED_SQLSTATE"; + case PLPGSQL_GETDIAG_COLUMN_NAME: + return "COLUMN_NAME"; + case PLPGSQL_GETDIAG_CONSTRAINT_NAME: + return "CONSTRAINT_NAME"; + case PLPGSQL_GETDIAG_DATATYPE_NAME: + return "DATATYPE_TEXT"; case PLPGSQL_GETDIAG_MESSAGE_TEXT: return "MESSAGE_TEXT"; + case PLPGSQL_GETDIAG_TABLE_NAME: + return "TABLE_NAME"; + case PLPGSQL_GETDIAG_SCHEMA_NAME: + return "SCHEMA_NAME"; } return "unknown"; *************** *** 1317,1322 **** dump_raise(PLpgSQL_stmt_raise *stmt) --- 1327,1347 ---- case PLPGSQL_RAISEOPTION_HINT: printf(" HINT = "); break; + case PLPGSQL_RAISEOPTION_COLUMN_NAME: + printf(" COLUMN_NAME = "); + break; + case PLPGSQL_RAISEOPTION_CONSTRAINT_NAME: + printf(" CONSTRAINT_NAME = "); + break; + case PLPGSQL_RAISEOPTION_DATATYPE_NAME: + printf(" DATATYPE_NAME = "); + break; + case PLPGSQL_RAISEOPTION_TABLE_NAME: + printf(" TABLE_NAME = "); + break; + case PLPGSQL_RAISEOPTION_SCHEMA_NAME: + printf(" SCHEMA_NAME = "); + break; } dump_expr(opt->expr); printf("\n"); *** a/src/pl/plpgsql/src/pl_gram.y --- b/src/pl/plpgsql/src/pl_gram.y *************** *** 251,260 **** static List *read_raise_options(void); --- 251,263 ---- %token K_CASE %token K_CLOSE %token K_COLLATE + %token K_COLUMN_NAME %token K_CONSTANT + %token K_CONSTRAINT_NAME %token K_CONTINUE %token K_CURRENT %token K_CURSOR + %token K_DATATYPE_NAME %token K_DEBUG %token K_DECLARE %token K_DEFAULT *************** *** 311,321 **** static List *read_raise_options(void); --- 314,326 ---- %token K_REVERSE %token K_ROWTYPE %token K_ROW_COUNT + %token K_SCHEMA_NAME %token K_SCROLL %token K_SLICE %token K_SQLSTATE %token K_STACKED %token K_STRICT + %token K_TABLE_NAME %token K_THEN %token K_TO %token K_TYPE *************** *** 896,902 **** stmt_getdiag : K_GET getdiag_area_opt K_DIAGNOSTICS getdiag_list ';' --- 901,912 ---- case PLPGSQL_GETDIAG_ERROR_DETAIL: case PLPGSQL_GETDIAG_ERROR_HINT: case PLPGSQL_GETDIAG_RETURNED_SQLSTATE: + case PLPGSQL_GETDIAG_COLUMN_NAME: + case PLPGSQL_GETDIAG_CONSTRAINT_NAME: + case PLPGSQL_GETDIAG_DATATYPE_NAME: case PLPGSQL_GETDIAG_MESSAGE_TEXT: + case PLPGSQL_GETDIAG_TABLE_NAME: + case PLPGSQL_GETDIAG_SCHEMA_NAME: if (!new->is_stacked) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), *************** *** 971,979 **** getdiag_item : --- 981,1004 ---- K_PG_EXCEPTION_CONTEXT, "pg_exception_context")) $$ = PLPGSQL_GETDIAG_ERROR_CONTEXT; else if (tok_is_keyword(tok, &yylval, + K_COLUMN_NAME, "column_name")) + $$ = PLPGSQL_GETDIAG_COLUMN_NAME; + else if (tok_is_keyword(tok, &yylval, + K_CONSTRAINT_NAME, "constraint_name")) + $$ = PLPGSQL_GETDIAG_CONSTRAINT_NAME; + else if (tok_is_keyword(tok, &yylval, + K_DATATYPE_NAME, "datatype_name")) + $$ = PLPGSQL_GETDIAG_DATATYPE_NAME; + else if (tok_is_keyword(tok, &yylval, K_MESSAGE_TEXT, "message_text")) $$ = PLPGSQL_GETDIAG_MESSAGE_TEXT; else if (tok_is_keyword(tok, &yylval, + K_TABLE_NAME, "table_name")) + $$ = PLPGSQL_GETDIAG_TABLE_NAME; + else if (tok_is_keyword(tok, &yylval, + K_SCHEMA_NAME, "schema_name")) + $$ = PLPGSQL_GETDIAG_SCHEMA_NAME; + else if (tok_is_keyword(tok, &yylval, K_RETURNED_SQLSTATE, "returned_sqlstate")) $$ = PLPGSQL_GETDIAG_RETURNED_SQLSTATE; else *************** *** 2231,2239 **** unreserved_keyword : --- 2256,2267 ---- | K_ALIAS | K_ARRAY | K_BACKWARD + | K_COLUMN_NAME | K_CONSTANT + | K_CONSTRAINT_NAME | K_CURRENT | K_CURSOR + | K_DATATYPE_NAME | K_DEBUG | K_DETAIL | K_DUMP *************** *** 2263,2268 **** unreserved_keyword : --- 2291,2298 ---- | K_REVERSE | K_ROW_COUNT | K_ROWTYPE + | K_TABLE_NAME + | K_SCHEMA_NAME | K_SCROLL | K_SLICE | K_SQLSTATE *************** *** 3631,3637 **** read_raise_options(void) --- 3661,3683 ---- else if (tok_is_keyword(tok, &yylval, K_HINT, "hint")) opt->opt_type = PLPGSQL_RAISEOPTION_HINT; + else if (tok_is_keyword(tok, &yylval, + K_COLUMN_NAME, "column_name")) + opt->opt_type = PLPGSQL_RAISEOPTION_COLUMN_NAME; + else if (tok_is_keyword(tok, &yylval, + K_CONSTRAINT_NAME, "constraint_name")) + opt->opt_type = PLPGSQL_RAISEOPTION_CONSTRAINT_NAME; + else if (tok_is_keyword(tok, &yylval, + K_DATATYPE_NAME, "datatype_name")) + opt->opt_type = PLPGSQL_RAISEOPTION_DATATYPE_NAME; + else if (tok_is_keyword(tok, &yylval, + K_TABLE_NAME, "table_name")) + opt->opt_type = PLPGSQL_RAISEOPTION_TABLE_NAME; + else if (tok_is_keyword(tok, &yylval, + K_SCHEMA_NAME, "schema_name")) + opt->opt_type = PLPGSQL_RAISEOPTION_SCHEMA_NAME; else + yyerror("unrecognized RAISE statement option"); tok = yylex(); *** a/src/pl/plpgsql/src/pl_scanner.c --- b/src/pl/plpgsql/src/pl_scanner.c *************** *** 109,117 **** static const ScanKeyword unreserved_keywords[] = { --- 109,120 ---- PG_KEYWORD("alias", K_ALIAS, UNRESERVED_KEYWORD) PG_KEYWORD("array", K_ARRAY, UNRESERVED_KEYWORD) PG_KEYWORD("backward", K_BACKWARD, UNRESERVED_KEYWORD) + PG_KEYWORD("column_name", K_COLUMN_NAME, UNRESERVED_KEYWORD) PG_KEYWORD("constant", K_CONSTANT, UNRESERVED_KEYWORD) + PG_KEYWORD("constraint_name", K_CONSTRAINT_NAME, UNRESERVED_KEYWORD) PG_KEYWORD("current", K_CURRENT, UNRESERVED_KEYWORD) PG_KEYWORD("cursor", K_CURSOR, UNRESERVED_KEYWORD) + PG_KEYWORD("datatype_name", K_DATATYPE_NAME, UNRESERVED_KEYWORD) PG_KEYWORD("debug", K_DEBUG, UNRESERVED_KEYWORD) PG_KEYWORD("detail", K_DETAIL, UNRESERVED_KEYWORD) PG_KEYWORD("dump", K_DUMP, UNRESERVED_KEYWORD) *************** *** 141,150 **** static const ScanKeyword unreserved_keywords[] = { --- 144,155 ---- PG_KEYWORD("reverse", K_REVERSE, UNRESERVED_KEYWORD) PG_KEYWORD("row_count", K_ROW_COUNT, UNRESERVED_KEYWORD) PG_KEYWORD("rowtype", K_ROWTYPE, UNRESERVED_KEYWORD) + PG_KEYWORD("schema_name", K_SCHEMA_NAME, UNRESERVED_KEYWORD) PG_KEYWORD("scroll", K_SCROLL, UNRESERVED_KEYWORD) PG_KEYWORD("slice", K_SLICE, UNRESERVED_KEYWORD) PG_KEYWORD("sqlstate", K_SQLSTATE, UNRESERVED_KEYWORD) PG_KEYWORD("stacked", K_STACKED, UNRESERVED_KEYWORD) + PG_KEYWORD("table_name", K_TABLE_NAME, UNRESERVED_KEYWORD) PG_KEYWORD("type", K_TYPE, UNRESERVED_KEYWORD) PG_KEYWORD("use_column", K_USE_COLUMN, UNRESERVED_KEYWORD) PG_KEYWORD("use_variable", K_USE_VARIABLE, UNRESERVED_KEYWORD) *** a/src/pl/plpgsql/src/plpgsql.h --- b/src/pl/plpgsql/src/plpgsql.h *************** *** 128,134 **** enum PLPGSQL_GETDIAG_ERROR_DETAIL, PLPGSQL_GETDIAG_ERROR_HINT, PLPGSQL_GETDIAG_RETURNED_SQLSTATE, ! PLPGSQL_GETDIAG_MESSAGE_TEXT }; /* -------- --- 128,139 ---- PLPGSQL_GETDIAG_ERROR_DETAIL, PLPGSQL_GETDIAG_ERROR_HINT, PLPGSQL_GETDIAG_RETURNED_SQLSTATE, ! PLPGSQL_GETDIAG_COLUMN_NAME, ! PLPGSQL_GETDIAG_CONSTRAINT_NAME, ! PLPGSQL_GETDIAG_DATATYPE_NAME, ! PLPGSQL_GETDIAG_MESSAGE_TEXT, ! PLPGSQL_GETDIAG_TABLE_NAME, ! PLPGSQL_GETDIAG_SCHEMA_NAME }; /* -------- *************** *** 140,146 **** enum PLPGSQL_RAISEOPTION_ERRCODE, PLPGSQL_RAISEOPTION_MESSAGE, PLPGSQL_RAISEOPTION_DETAIL, ! PLPGSQL_RAISEOPTION_HINT }; /* -------- --- 145,156 ---- PLPGSQL_RAISEOPTION_ERRCODE, PLPGSQL_RAISEOPTION_MESSAGE, PLPGSQL_RAISEOPTION_DETAIL, ! PLPGSQL_RAISEOPTION_HINT, ! PLPGSQL_RAISEOPTION_COLUMN_NAME, ! PLPGSQL_RAISEOPTION_CONSTRAINT_NAME, ! PLPGSQL_RAISEOPTION_DATATYPE_NAME, ! PLPGSQL_RAISEOPTION_TABLE_NAME, ! PLPGSQL_RAISEOPTION_SCHEMA_NAME }; /* -------- *** a/src/test/regress/expected/plpgsql.out --- b/src/test/regress/expected/plpgsql.out *************** *** 3974,3979 **** select raise_test(); --- 3974,4013 ---- NOTICE: 22012 ERROR: substitute message drop function raise_test(); + -- test passing column_name, constraint_name, datatype_name, table_name + -- and schema_name error fields + create or replace function stacked_diagnostics_test() returns void as $$ + declare _column_name text; + _constraint_name text; + _datatype_name text; + _table_name text; + _schema_name text; + begin + raise exception using + column_name = '>>some column name<<', + constraint_name = '>>some constraint name<<', + datatype_name = '>>some datatype name<<', + table_name = '>>some table name<<', + schema_name = '>>some schema name<<'; + exception when others then + get stacked diagnostics + _column_name = column_name, + _constraint_name = constraint_name, + _datatype_name = datatype_name, + _table_name = table_name, + _schema_name = schema_name; + raise notice 'column_name: %, constraint_name: %, datatype_name: %, table_name: %, schema_name: %', + _column_name, _constraint_name, _datatype_name, _table_name, _schema_name; + end; + $$ language plpgsql; + select stacked_diagnostics_test(); + NOTICE: column_name: >>some column name<<, constraint_name: >>some constraint name<<, datatype_name: >>some datatype name<<, table_name: >>some table name<<, schema_name: >>some schema name<< + stacked_diagnostics_test + -------------------------- + + (1 row) + + drop function stacked_diagnostics_test(); -- test CASE statement create or replace function case_test(bigint) returns text as $$ declare a int = 10; *** a/src/test/regress/sql/plpgsql.sql --- b/src/test/regress/sql/plpgsql.sql *************** *** 3262,3267 **** select raise_test(); --- 3262,3299 ---- drop function raise_test(); + -- test passing column_name, constraint_name, datatype_name, table_name + -- and schema_name error fields + + create or replace function stacked_diagnostics_test() returns void as $$ + declare _column_name text; + _constraint_name text; + _datatype_name text; + _table_name text; + _schema_name text; + begin + raise exception using + column_name = '>>some column name<<', + constraint_name = '>>some constraint name<<', + datatype_name = '>>some datatype name<<', + table_name = '>>some table name<<', + schema_name = '>>some schema name<<'; + exception when others then + get stacked diagnostics + _column_name = column_name, + _constraint_name = constraint_name, + _datatype_name = datatype_name, + _table_name = table_name, + _schema_name = schema_name; + raise notice 'column_name: %, constraint_name: %, datatype_name: %, table_name: %, schema_name: %', + _column_name, _constraint_name, _datatype_name, _table_name, _schema_name; + end; + $$ language plpgsql; + + select stacked_diagnostics_test(); + + drop function stacked_diagnostics_test(); + -- test CASE statement create or replace function case_test(bigint) returns text as $$