*** a/doc/src/sgml/libpq.sgml --- b/doc/src/sgml/libpq.sgml *************** *** 2515,2521 **** char *PQresultErrorField(const PGresult *res, int fieldcode); --- 2515,2567 ---- + + + PG_DIAG_COLUMN_NAME + + + The name of the column related to the error. + + + + + + PG_DIAG_TABLE_NAME + + + The name of the table related to the error. + + + + + + PG_DIAG_SCHEMA_NAME + + + The name of the schema where table, related to the error, is defined. + + + + + + PG_DIAG_SCHEMA_NAME + + + The name of the constraint related to the error. + + + + + + PG_DIAG_SCHEMA_NAME + + + The schema's name of constraint related to the error. + + + + *** a/doc/src/sgml/plpgsql.sgml --- b/doc/src/sgml/plpgsql.sgml *************** *** 2612,2620 **** GET STACKED DIAGNOSTICS variable = item< ! RETURNED_SQLSTATE text ! the SQLSTATE error code of the exception MESSAGE_TEXT --- 2612,2630 ---- ! COLUMN_NAME text ! a name of column related to error ! ! ! CONSTRAINT_NAME ! text ! a name of constraint related to error ! ! ! CONSTRAINT_SCHEMA ! text ! a schema where is defined a constraint related to error MESSAGE_TEXT *************** *** 2636,2641 **** GET STACKED DIAGNOSTICS variable = item< --- 2646,2666 ---- text line(s) of text describing the call stack + + RETURNED_SQLSTATE + text + the SQLSTATE error code of the exception + + + SCHEMA_NAME + text + a name of shema where is defined table related to error + + + TABLE_NAME + text + a name of table related to error + *************** *** 3246,3253 **** RAISE NOTICE 'Calling cs_create_job(%)', v_job_id; class="parameter">option = expression items. The allowed option keywords are ! MESSAGE, DETAIL, HINT, and ! ERRCODE, while each expression can be any string-valued expression. MESSAGE sets the error message text (this option can't --- 3271,3280 ---- class="parameter">option = expression items. The allowed option keywords are ! COLUMN_NAME, CONSTRAINT_NAME, ! CONSTRAINT_SCHEMA, DETAIL, ERRCODE, ! HINT, MESSAGE, TABLE_NAME, and ! SCHEMA_NAME, while each expression can be any string-valued expression. MESSAGE sets the error message text (this option can't *** a/doc/src/sgml/protocol.sgml --- b/doc/src/sgml/protocol.sgml *************** *** 4682,4687 **** message. --- 4682,4742 ---- + + + A + + + + Column name: the name of column related to error. + + + + + + + T + + + + Table name: the name of the table related to error. + + + + + + + t + + + + Schema name: the name of schema where is comming table related to the error. + + + + + + + N + + + + Constraint name: the name of the constraint related to the error. + + + + + + + n + + + + Constraint schema: the name of the schema where is defined a constraint related to the error. + + + + *** a/doc/src/sgml/sources.sgml --- b/doc/src/sgml/sources.sgml *************** *** 298,303 **** ereport(ERROR, --- 298,314 ---- includes the current statement already. + + + + errstr(int diagnostics_field, const char *str) can be + called to specify a some other data like COLUMN_NAME, + TABLE_NAME, SCHEMA_NAME, CONSTRAINT_NAME, + CONSTRAINT_SCHEMA. + + + + *** a/src/backend/access/nbtree/nbtinsert.c --- b/src/backend/access/nbtree/nbtinsert.c *************** *** 23,28 **** --- 23,29 ---- #include "storage/lmgr.h" #include "storage/predicate.h" #include "utils/inval.h" + #include "utils/lsyscache.h" #include "utils/tqual.h" *************** *** 394,400 **** _bt_check_unique(Relation rel, IndexTuple itup, Relation heapRel, RelationGetRelationName(rel)), errdetail("Key %s already exists.", BuildIndexValueDescription(rel, ! values, isnull)))); } } else if (all_dead) --- 395,405 ---- RelationGetRelationName(rel)), errdetail("Key %s already exists.", BuildIndexValueDescription(rel, ! values, isnull)), ! errstr(CONSTRAINT_NAME, RelationGetRelationName(rel)), ! errstr(CONSTRAINT_SCHEMA, get_namespace_name(RelationGetNamespace(rel))), ! errstr(TABLE_NAME, RelationGetRelationName(heapRel)), ! errstr(SCHEMA_NAME, get_namespace_name(RelationGetNamespace(heapRel))))); } } else if (all_dead) *************** *** 534,540 **** _bt_findinsertloc(Relation rel, RelationGetRelationName(rel)), errhint("Values larger than 1/3 of a buffer page cannot be indexed.\n" "Consider a function index of an MD5 hash of the value, " ! "or use full text indexing."))); /*---------- * If we will need to split the page to put the item on this page, --- 539,549 ---- RelationGetRelationName(rel)), errhint("Values larger than 1/3 of a buffer page cannot be indexed.\n" "Consider a function index of an MD5 hash of the value, " ! "or use full text indexing."), ! errstr(CONSTRAINT_NAME, RelationGetRelationName(rel)), ! errstr(CONSTRAINT_SCHEMA, get_namespace_name(RelationGetNamespace(rel))), ! errstr(TABLE_NAME, RelationGetRelationName(heapRel)), ! errstr(SCHEMA_NAME, get_namespace_name(RelationGetNamespace(heapRel))))); /*---------- * If we will need to split the page to put the item on this page, *** a/src/backend/commands/typecmds.c --- b/src/backend/commands/typecmds.c *************** *** 2570,2575 **** GetDomainConstraints(Oid typeOid) --- 2570,2576 ---- List *result = NIL; bool notNull = false; Relation conRel; + Oid namespaceid = InvalidOid; conRel = heap_open(ConstraintRelationId, AccessShareLock); *************** *** 2595,2601 **** GetDomainConstraints(Oid typeOid) --- 2596,2605 ---- /* Test for NOT NULL Constraint */ if (typTup->typnotnull) + { notNull = true; + namespaceid = typTup->typnamespace; + } /* Look for CHECK Constraints on this domain */ ScanKeyInit(&key[0], *************** *** 2635,2640 **** GetDomainConstraints(Oid typeOid) --- 2639,2645 ---- r = makeNode(DomainConstraintState); r->constrainttype = DOM_CONSTRAINT_CHECK; r->name = pstrdup(NameStr(c->conname)); + r->namespace = c->connamespace; r->check_expr = ExecInitExpr(check_expr, NULL); /* *************** *** 2663,2668 **** GetDomainConstraints(Oid typeOid) --- 2668,2674 ---- r->constrainttype = DOM_CONSTRAINT_NOTNULL; r->name = pstrdup("NOT NULL"); + r->namespace = namespaceid; r->check_expr = NULL; /* lcons to apply the nullness check FIRST */ *** a/src/backend/executor/execMain.c --- b/src/backend/executor/execMain.c *************** *** 1565,1571 **** ExecConstraints(ResultRelInfo *resultRelInfo, ereport(ERROR, (errcode(ERRCODE_NOT_NULL_VIOLATION), errmsg("null value in column \"%s\" violates not-null constraint", ! NameStr(rel->rd_att->attrs[attrChk - 1]->attname)))); } } --- 1565,1576 ---- ereport(ERROR, (errcode(ERRCODE_NOT_NULL_VIOLATION), errmsg("null value in column \"%s\" violates not-null constraint", ! NameStr(rel->rd_att->attrs[attrChk - 1]->attname)), ! errstr(COLUMN_NAME, ! NameStr(rel->rd_att->attrs[attrChk - 1]->attname)), ! errstr(TABLE_NAME, RelationGetRelationName(rel)), ! errstr(SCHEMA_NAME, ! get_namespace_name(RelationGetNamespace(rel))))); } } *************** *** 1577,1583 **** ExecConstraints(ResultRelInfo *resultRelInfo, ereport(ERROR, (errcode(ERRCODE_CHECK_VIOLATION), errmsg("new row for relation \"%s\" violates check constraint \"%s\"", ! RelationGetRelationName(rel), failed))); } } --- 1582,1594 ---- ereport(ERROR, (errcode(ERRCODE_CHECK_VIOLATION), errmsg("new row for relation \"%s\" violates check constraint \"%s\"", ! RelationGetRelationName(rel), failed), ! errstr(TABLE_NAME, RelationGetRelationName(rel)), ! errstr(SCHEMA_NAME, ! get_namespace_name(RelationGetNamespace(rel))), ! errstr(CONSTRAINT_NAME, failed), ! errstr(CONSTRAINT_SCHEMA, ! get_namespace_name(RelationGetNamespace(rel))))); } } *** a/src/backend/executor/execQual.c --- b/src/backend/executor/execQual.c *************** *** 3777,3783 **** ExecEvalCoerceToDomain(CoerceToDomainState *cstate, ExprContext *econtext, ereport(ERROR, (errcode(ERRCODE_NOT_NULL_VIOLATION), errmsg("domain %s does not allow null values", ! format_type_be(ctest->resulttype)))); break; case DOM_CONSTRAINT_CHECK: { --- 3777,3785 ---- ereport(ERROR, (errcode(ERRCODE_NOT_NULL_VIOLATION), errmsg("domain %s does not allow null values", ! format_type_be(ctest->resulttype)), ! errstr(CONSTRAINT_NAME, con->name), ! errstr(CONSTRAINT_SCHEMA, get_namespace_name(con->namespace)))); break; case DOM_CONSTRAINT_CHECK: { *************** *** 3807,3813 **** ExecEvalCoerceToDomain(CoerceToDomainState *cstate, ExprContext *econtext, (errcode(ERRCODE_CHECK_VIOLATION), errmsg("value for domain %s violates check constraint \"%s\"", format_type_be(ctest->resulttype), ! con->name))); econtext->domainValue_datum = save_datum; econtext->domainValue_isNull = save_isNull; --- 3809,3817 ---- (errcode(ERRCODE_CHECK_VIOLATION), errmsg("value for domain %s violates check constraint \"%s\"", format_type_be(ctest->resulttype), ! con->name), ! errstr(CONSTRAINT_NAME, con->name), ! errstr(CONSTRAINT_SCHEMA, get_namespace_name(con->namespace)))); econtext->domainValue_datum = save_datum; econtext->domainValue_isNull = save_isNull; *** a/src/backend/utils/adt/ri_triggers.c --- b/src/backend/utils/adt/ri_triggers.c *************** *** 244,250 **** static void ri_ReportViolation(RI_QueryKey *qkey, const char *constrname, HeapTuple violator, TupleDesc tupdesc, bool spi_err); - /* ---------- * RI_FKey_check - * --- 244,249 ---- *************** *** 410,416 **** RI_FKey_check(PG_FUNCTION_ARGS) errmsg("insert or update on table \"%s\" violates foreign key constraint \"%s\"", RelationGetRelationName(trigdata->tg_relation), NameStr(riinfo.conname)), ! errdetail("MATCH FULL does not allow mixing of null and nonnull key values."))); heap_close(pk_rel, RowShareLock); return PointerGetDatum(NULL); --- 409,421 ---- errmsg("insert or update on table \"%s\" violates foreign key constraint \"%s\"", RelationGetRelationName(trigdata->tg_relation), NameStr(riinfo.conname)), ! errdetail("MATCH FULL does not allow mixing of null and nonnull key values."), ! errstr(TABLE_NAME, RelationGetRelationName(trigdata->tg_relation)), ! errstr(SCHEMA_NAME, ! get_namespace_name(RelationGetNamespace(trigdata->tg_relation))), ! errstr(CONSTRAINT_NAME, NameStr(riinfo.conname)), ! errstr(CONSTRAINT_SCHEMA, ! get_namespace_name(RelationGetNamespace(trigdata->tg_relation))))); heap_close(pk_rel, RowShareLock); return PointerGetDatum(NULL); *************** *** 2840,2846 **** RI_Initial_Check(Trigger *trigger, Relation fk_rel, Relation pk_rel) errmsg("insert or update on table \"%s\" violates foreign key constraint \"%s\"", RelationGetRelationName(fk_rel), constrname), ! errdetail("MATCH FULL does not allow mixing of null and nonnull key values."))); } /* --- 2845,2857 ---- errmsg("insert or update on table \"%s\" violates foreign key constraint \"%s\"", RelationGetRelationName(fk_rel), constrname), ! errdetail("MATCH FULL does not allow mixing of null and nonnull key values."), ! errstr(TABLE_NAME, RelationGetRelationName(fk_rel)), ! errstr(SCHEMA_NAME, ! get_namespace_name(RelationGetNamespace(fk_rel))), ! errstr(CONSTRAINT_NAME, constrname), ! errstr(CONSTRAINT_SCHEMA, ! get_namespace_name(RelationGetNamespace(fk_rel))))); } /* *************** *** 3538,3544 **** ri_ReportViolation(RI_QueryKey *qkey, const char *constrname, errmsg("insert or update on table \"%s\" violates foreign key constraint \"%s\"", RelationGetRelationName(fk_rel), constrname), errdetail("No rows were found in \"%s\".", ! RelationGetRelationName(pk_rel)))); } /* Get printable versions of the keys involved */ --- 3549,3561 ---- errmsg("insert or update on table \"%s\" violates foreign key constraint \"%s\"", RelationGetRelationName(fk_rel), constrname), errdetail("No rows were found in \"%s\".", ! RelationGetRelationName(pk_rel)), ! errstr(TABLE_NAME, RelationGetRelationName(fk_rel)), ! errstr(SCHEMA_NAME, ! get_namespace_name(RelationGetNamespace(fk_rel))), ! errstr(CONSTRAINT_NAME, constrname), ! errstr(CONSTRAINT_SCHEMA, ! get_namespace_name(RelationGetNamespace(fk_rel))))); } /* Get printable versions of the keys involved */ *************** *** 3571,3577 **** ri_ReportViolation(RI_QueryKey *qkey, const char *constrname, RelationGetRelationName(fk_rel), constrname), errdetail("Key (%s)=(%s) is not present in table \"%s\".", key_names.data, key_values.data, ! RelationGetRelationName(pk_rel)))); else ereport(ERROR, (errcode(ERRCODE_FOREIGN_KEY_VIOLATION), --- 3588,3600 ---- RelationGetRelationName(fk_rel), constrname), errdetail("Key (%s)=(%s) is not present in table \"%s\".", key_names.data, key_values.data, ! RelationGetRelationName(pk_rel)), ! errstr(TABLE_NAME, RelationGetRelationName(fk_rel)), ! errstr(SCHEMA_NAME, ! get_namespace_name(RelationGetNamespace(fk_rel))), ! errstr(CONSTRAINT_NAME, constrname), ! errstr(CONSTRAINT_SCHEMA, ! get_namespace_name(RelationGetNamespace(fk_rel))))); else ereport(ERROR, (errcode(ERRCODE_FOREIGN_KEY_VIOLATION), *************** *** 3580,3586 **** ri_ReportViolation(RI_QueryKey *qkey, const char *constrname, constrname, RelationGetRelationName(fk_rel)), errdetail("Key (%s)=(%s) is still referenced from table \"%s\".", key_names.data, key_values.data, ! RelationGetRelationName(fk_rel)))); } /* ---------- --- 3603,3615 ---- constrname, RelationGetRelationName(fk_rel)), errdetail("Key (%s)=(%s) is still referenced from table \"%s\".", key_names.data, key_values.data, ! RelationGetRelationName(fk_rel)), ! errstr(TABLE_NAME, RelationGetRelationName(pk_rel)), ! errstr(SCHEMA_NAME, ! get_namespace_name(RelationGetNamespace(pk_rel))), ! errstr(CONSTRAINT_NAME, constrname), ! errstr(CONSTRAINT_SCHEMA, ! get_namespace_name(RelationGetNamespace(fk_rel))))); } /* ---------- *** a/src/backend/utils/error/elog.c --- b/src/backend/utils/error/elog.c *************** *** 457,462 **** errfinish(int dummy,...) --- 457,472 ---- pfree(edata->context); if (edata->internalquery) pfree(edata->internalquery); + if (edata->column_name) + pfree(edata->column_name); + if (edata->table_name) + pfree(edata->table_name); + if (edata->schema_name) + pfree(edata->schema_name); + if (edata->constraint_schema) + pfree(edata->constraint_schema); + if (edata->constraint_name) + pfree(edata->constraint_name); errordata_stack_depth--; *************** *** 1016,1021 **** errposition(int cursorpos) --- 1026,1106 ---- } /* + * errcatalog_info - add a diagnostics value to the current error + */ + int + errcatalog_info(int kind, const char *str) + { + ErrorData *edata = &errordata[errordata_stack_depth]; + MemoryContext oldcontext; + + recursion_depth++; + CHECK_STACK_DEPTH(); + oldcontext = MemoryContextSwitchTo(ErrorContext); + + /* we don't bother incrementing recursion_depth */ + CHECK_STACK_DEPTH(); + + switch (kind) + { + case PG_DIAG_COLUMN_NAME: + if (edata->column_name) + { + pfree(edata->column_name); + edata->column_name = NULL; + } + if (str) + edata->column_name = MemoryContextStrdup(ErrorContext, str); + break; + + case PG_DIAG_TABLE_NAME: + if (edata->table_name) + { + pfree(edata->table_name); + edata->table_name = NULL; + } + if (str) + edata->table_name = MemoryContextStrdup(ErrorContext, str); + break; + + case PG_DIAG_SCHEMA_NAME: + if (edata->schema_name) + { + pfree(edata->schema_name); + edata->schema_name = NULL; + } + if (str) + edata->schema_name = MemoryContextStrdup(ErrorContext, str); + break; + + case PG_DIAG_CONSTRAINT_NAME: + if (edata->constraint_name) + { + pfree(edata->constraint_name); + edata->constraint_name = NULL; + } + if (str) + edata->constraint_name = MemoryContextStrdup(ErrorContext, str); + break; + + case PG_DIAG_CONSTRAINT_SCHEMA: + if (edata->constraint_schema) + { + pfree(edata->constraint_schema); + edata->constraint_schema = NULL; + } + if (str) + edata->constraint_schema = MemoryContextStrdup(ErrorContext, str); + break; + } + + MemoryContextSwitchTo(oldcontext); + recursion_depth--; + return 0; /* return value does not matter */ + } + + + /* * internalerrposition --- add internal cursor position to the current error */ int *************** *** 1306,1311 **** CopyErrorData(void) --- 1391,1406 ---- newedata->context = pstrdup(newedata->context); if (newedata->internalquery) newedata->internalquery = pstrdup(newedata->internalquery); + if (edata->column_name) + newedata->column_name = pstrdup(newedata->column_name); + if (newedata->table_name) + newedata->table_name = pstrdup(edata->table_name); + if (newedata->schema_name) + newedata->schema_name = pstrdup(newedata->schema_name); + if (newedata->constraint_schema) + newedata->constraint_schema = pstrdup(newedata->constraint_schema); + if (newedata->constraint_name) + newedata->constraint_name = pstrdup(newedata->constraint_name); return newedata; } *************** *** 1331,1336 **** FreeErrorData(ErrorData *edata) --- 1426,1441 ---- pfree(edata->context); if (edata->internalquery) pfree(edata->internalquery); + if (edata->column_name) + pfree(edata->column_name); + if (edata->table_name) + pfree(edata->table_name); + if (edata->schema_name) + pfree(edata->schema_name); + if (edata->constraint_schema) + pfree(edata->constraint_schema); + if (edata->constraint_name) + pfree(edata->constraint_name); pfree(edata); } *************** *** 1403,1408 **** ReThrowError(ErrorData *edata) --- 1508,1523 ---- newedata->context = pstrdup(newedata->context); if (newedata->internalquery) newedata->internalquery = pstrdup(newedata->internalquery); + if (edata->column_name) + newedata->column_name = pstrdup(newedata->column_name); + if (newedata->table_name) + newedata->table_name = pstrdup(edata->table_name); + if (newedata->schema_name) + newedata->schema_name = pstrdup(newedata->schema_name); + if (newedata->constraint_schema) + newedata->constraint_schema = pstrdup(newedata->constraint_schema); + if (newedata->constraint_name) + newedata->constraint_name = pstrdup(newedata->constraint_name); recursion_depth--; PG_RE_THROW(); *************** *** 2223,2228 **** write_csvlog(ErrorData *edata) --- 2338,2368 ---- /* application name */ if (application_name) appendCSVLiteral(&buf, application_name); + appendStringInfoChar(&buf, ','); + + /* column name */ + if (Log_error_verbosity >= PGERROR_VERBOSE) + appendCSVLiteral(&buf, edata->column_name); + appendStringInfoChar(&buf, ','); + + /* table name */ + if (Log_error_verbosity >= PGERROR_VERBOSE) + appendCSVLiteral(&buf, edata->table_name); + appendStringInfoChar(&buf, ','); + + /* schema name */ + if (Log_error_verbosity >= PGERROR_VERBOSE) + appendCSVLiteral(&buf, edata->schema_name); + appendStringInfoChar(&buf, ','); + + /* constraint name */ + if (Log_error_verbosity >= PGERROR_VERBOSE) + appendCSVLiteral(&buf, edata->constraint_name); + appendStringInfoChar(&buf, ','); + + /* constraint schema */ + if (Log_error_verbosity >= PGERROR_VERBOSE) + appendCSVLiteral(&buf, edata->constraint_schema); appendStringInfoChar(&buf, '\n'); *************** *** 2341,2346 **** send_message_to_server_log(ErrorData *edata) --- 2481,2521 ---- appendStringInfo(&buf, _("LOCATION: %s:%d\n"), edata->filename, edata->lineno); } + if (edata->column_name) + { + log_line_prefix(&buf, edata); + appendStringInfoString(&buf, _("COLUMN: ")); + append_with_tabs(&buf, edata->column_name); + appendStringInfoChar(&buf, '\n'); + } + if (edata->table_name) + { + log_line_prefix(&buf, edata); + appendStringInfoString(&buf, _("TABLE: ")); + append_with_tabs(&buf, edata->table_name); + appendStringInfoChar(&buf, '\n'); + } + if (edata->schema_name) + { + log_line_prefix(&buf, edata); + appendStringInfoString(&buf, _("SCHEMA: ")); + append_with_tabs(&buf, edata->schema_name); + appendStringInfoChar(&buf, '\n'); + } + if (edata->constraint_name) + { + log_line_prefix(&buf, edata); + appendStringInfoString(&buf, _("CONSTRAINT NAME: ")); + append_with_tabs(&buf, edata->constraint_name); + appendStringInfoChar(&buf, '\n'); + } + if (edata->constraint_schema) + { + log_line_prefix(&buf, edata); + appendStringInfoString(&buf, _("CONSTRAINT SCHEMA: ")); + append_with_tabs(&buf, edata->constraint_schema); + appendStringInfoChar(&buf, '\n'); + } } } *************** *** 2618,2623 **** send_message_to_frontend(ErrorData *edata) --- 2793,2828 ---- err_sendstring(&msgbuf, edata->funcname); } + if (edata->column_name) + { + pq_sendbyte(&msgbuf, PG_DIAG_COLUMN_NAME); + err_sendstring(&msgbuf, edata->column_name); + } + + if (edata->table_name) + { + pq_sendbyte(&msgbuf, PG_DIAG_TABLE_NAME); + err_sendstring(&msgbuf, edata->table_name); + } + + if (edata->schema_name) + { + pq_sendbyte(&msgbuf, PG_DIAG_SCHEMA_NAME); + err_sendstring(&msgbuf, edata->schema_name); + } + + if (edata->constraint_name) + { + pq_sendbyte(&msgbuf, PG_DIAG_CONSTRAINT_NAME); + err_sendstring(&msgbuf, edata->constraint_name); + } + + if (edata->constraint_schema) + { + pq_sendbyte(&msgbuf, PG_DIAG_CONSTRAINT_SCHEMA); + err_sendstring(&msgbuf, edata->constraint_schema); + } + pq_sendbyte(&msgbuf, '\0'); /* terminator */ } else *** a/src/include/nodes/execnodes.h --- b/src/include/nodes/execnodes.h *************** *** 941,946 **** typedef struct DomainConstraintState --- 941,947 ---- NodeTag type; DomainConstraintType constrainttype; /* constraint type */ char *name; /* name of constraint (for error msgs) */ + Oid namespace; /* schema of constraint (for error msgs) */ ExprState *check_expr; /* for CHECK, a boolean expression */ } DomainConstraintState; *** a/src/include/postgres_ext.h --- b/src/include/postgres_ext.h *************** *** 55,59 **** typedef unsigned int Oid; --- 55,64 ---- #define PG_DIAG_SOURCE_FILE 'F' #define PG_DIAG_SOURCE_LINE 'L' #define PG_DIAG_SOURCE_FUNCTION 'R' + #define PG_DIAG_COLUMN_NAME 'A' + #define PG_DIAG_TABLE_NAME 'T' + #define PG_DIAG_SCHEMA_NAME 't' + #define PG_DIAG_CONSTRAINT_NAME 'N' + #define PG_DIAG_CONSTRAINT_SCHEMA 'n' #endif *** a/src/include/utils/elog.h --- b/src/include/utils/elog.h *************** *** 182,187 **** extern int errhidestmt(bool hide_stmt); --- 182,191 ---- extern int errfunction(const char *funcname); extern int errposition(int cursorpos); + extern int errcatalog_info(int kind, const char *str); + + #define errstr(kind, str) \ + errcatalog_info(PG_DIAG_##kind, str) extern int internalerrposition(int cursorpos); extern int internalerrquery(const char *query); *************** *** 321,326 **** typedef struct ErrorData --- 325,335 ---- char *detail_log; /* detail error message for server log only */ char *hint; /* hint message */ char *context; /* context message */ + char *column_name; /* name of column related to error or empty string */ + char *table_name; /* name of table related to error */ + char *schema_name; /* name of schema where is a table related to error */ + char *constraint_schema; /* schema of constraint or empty string */ + char *constraint_name; /* name of constraint of empty string */ int cursorpos; /* cursor index into query string */ int internalpos; /* cursor index into internalquery */ char *internalquery; /* text of internally-generated query */ *** a/src/interfaces/libpq/fe-protocol3.c --- b/src/interfaces/libpq/fe-protocol3.c *************** *** 856,861 **** pqGetErrorNotice3(PGconn *conn, bool isError) --- 856,876 ---- valf, vall); appendPQExpBufferChar(&workBuf, '\n'); } + val = PQresultErrorField(res, PG_DIAG_COLUMN_NAME); + if (val) + appendPQExpBuffer(&workBuf, libpq_gettext("COLUMN: %s\n"), val); + val = PQresultErrorField(res, PG_DIAG_TABLE_NAME); + if (val) + appendPQExpBuffer(&workBuf, libpq_gettext("TABLE: %s\n"), val); + val = PQresultErrorField(res, PG_DIAG_SCHEMA_NAME); + if (val) + appendPQExpBuffer(&workBuf, libpq_gettext("SCHEMA: %s\n"), val); + val = PQresultErrorField(res, PG_DIAG_CONSTRAINT_NAME); + if (val) + appendPQExpBuffer(&workBuf, libpq_gettext("CONSTRAINT NAME: %s\n"), val); + val = PQresultErrorField(res, PG_DIAG_CONSTRAINT_SCHEMA); + if (val) + appendPQExpBuffer(&workBuf, libpq_gettext("CONSTRAINT SCHEMA: %s\n"), val); } /* *** a/src/pl/plpgsql/src/gram.y --- b/src/pl/plpgsql/src/gram.y *************** *** 250,256 **** static List *read_raise_options(void); --- 250,259 ---- %token K_CASE %token K_CLOSE %token K_COLLATE + %token K_COLUMN_NAME %token K_CONSTANT + %token K_CONSTRAINT_NAME + %token K_CONSTRAINT_SCHEMA %token K_CONTINUE %token K_CURRENT %token K_CURSOR *************** *** 310,320 **** static List *read_raise_options(void); --- 313,325 ---- %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 *************** *** 876,881 **** stmt_getdiag : K_GET getdiag_area_opt K_DIAGNOSTICS getdiag_list ';' --- 881,891 ---- case PLPGSQL_GETDIAG_ERROR_HINT: case PLPGSQL_GETDIAG_RETURNED_SQLSTATE: case PLPGSQL_GETDIAG_MESSAGE_TEXT: + case PLPGSQL_GETDIAG_COLUMN_NAME: + case PLPGSQL_GETDIAG_CONSTRAINT_NAME: + case PLPGSQL_GETDIAG_CONSTRAINT_SCHEMA: + case PLPGSQL_GETDIAG_SCHEMA_NAME: + case PLPGSQL_GETDIAG_TABLE_NAME: if (!new->is_stacked) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), *************** *** 955,960 **** getdiag_item : --- 965,985 ---- else if (tok_is_keyword(tok, &yylval, K_RETURNED_SQLSTATE, "returned_sqlstate")) $$ = PLPGSQL_GETDIAG_RETURNED_SQLSTATE; + 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_CONSTRAINT_SCHEMA, "constraint_schema")) + $$ = PLPGSQL_GETDIAG_CONSTRAINT_SCHEMA; + else if (tok_is_keyword(tok, &yylval, + K_SCHEMA_NAME, "schema_name")) + $$ = PLPGSQL_GETDIAG_SCHEMA_NAME; + else if (tok_is_keyword(tok, &yylval, + K_TABLE_NAME, "table_name")) + $$ = PLPGSQL_GETDIAG_TABLE_NAME; else yyerror("unrecognized GET DIAGNOSTICS item"); } *************** *** 2212,2218 **** unreserved_keyword : --- 2237,2246 ---- | K_ALIAS | K_ARRAY | K_BACKWARD + | K_COLUMN_NAME | K_CONSTANT + | K_CONSTRAINT_NAME + | K_CONSTRAINT_SCHEMA | K_CURRENT | K_CURSOR | K_DEBUG *************** *** 2244,2253 **** unreserved_keyword : --- 2272,2283 ---- | K_REVERSE | K_ROW_COUNT | K_ROWTYPE + | K_SCHEMA_NAME | K_SCROLL | K_SLICE | K_SQLSTATE | K_STACKED + | K_TABLE_NAME | K_TYPE | K_USE_COLUMN | K_USE_VARIABLE *************** *** 3463,3468 **** read_raise_options(void) --- 3493,3513 ---- 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_CONSTRAINT_SCHEMA, "constraint_schema")) + opt->opt_type = PLPGSQL_RAISEOPTION_CONSTRAINT_SCHEMA; + else if (tok_is_keyword(tok, &yylval, + K_SCHEMA_NAME, "schema_name")) + opt->opt_type = PLPGSQL_RAISEOPTION_SCHEMA_NAME; + else if (tok_is_keyword(tok, &yylval, + K_TABLE_NAME, "table_name")) + opt->opt_type = PLPGSQL_RAISEOPTION_TABLE_NAME; else yyerror("unrecognized RAISE statement option"); *** a/src/pl/plpgsql/src/pl_exec.c --- b/src/pl/plpgsql/src/pl_exec.c *************** *** 1488,1493 **** exec_stmt_getdiag(PLpgSQL_execstate *estate, PLpgSQL_stmt_getdiag *stmt) --- 1488,1518 ---- estate->cur_error->message); 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_CONSTRAINT_SCHEMA: + exec_assign_c_string(estate, var, + estate->cur_error->constraint_schema); + break; + + case PLPGSQL_GETDIAG_SCHEMA_NAME: + exec_assign_c_string(estate, var, + estate->cur_error->schema_name); + break; + + case PLPGSQL_GETDIAG_TABLE_NAME: + exec_assign_c_string(estate, var, + estate->cur_error->table_name); + break; + default: elog(ERROR, "unrecognized diagnostic item kind: %d", diag_item->kind); *************** *** 2661,2666 **** exec_stmt_raise(PLpgSQL_execstate *estate, PLpgSQL_stmt_raise *stmt) --- 2686,2696 ---- char *err_message = NULL; char *err_detail = NULL; char *err_hint = NULL; + char *err_column_name = NULL; + char *err_constraint_name = NULL; + char *err_constraint_schema = NULL; + char *err_schema_name = NULL; + char *err_table_name = NULL; ListCell *lc; /* RAISE with no parameters: re-throw current exception */ *************** *** 2798,2803 **** exec_stmt_raise(PLpgSQL_execstate *estate, PLpgSQL_stmt_raise *stmt) --- 2828,2873 ---- "HINT"))); err_hint = pstrdup(extval); break; + case PLPGSQL_RAISEOPTION_COLUMN_NAME: + if (err_column_name) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("RAISE option already specified: %s", + "COLUMN_NAME"))); + err_column_name = pstrdup(extval); + break; + case PLPGSQL_RAISEOPTION_CONSTRAINT_NAME: + if (err_constraint_name) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("RAISE option already specified: %s", + "CONSTRAINT_NAME"))); + err_constraint_name = pstrdup(extval); + break; + case PLPGSQL_RAISEOPTION_CONSTRAINT_SCHEMA: + if (err_constraint_schema) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("RAISE option already specified: %s", + "CONSTRAINT_SCHEMA"))); + err_constraint_schema = pstrdup(extval); + break; + case PLPGSQL_RAISEOPTION_SCHEMA_NAME: + if (err_schema_name) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("RAISE option already specified: %s", + "SCHEMA_NAME"))); + err_schema_name = pstrdup(extval); + break; + case PLPGSQL_RAISEOPTION_TABLE_NAME: + if (err_table_name) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("RAISE option already specified: %s", + "TABLE_NAME"))); + err_table_name = pstrdup(extval); + break; default: elog(ERROR, "unrecognized raise option: %d", opt->opt_type); } *************** *** 2830,2836 **** 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... */ --- 2900,2911 ---- (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) ? errstr(COLUMN_NAME, err_column_name) : 0, ! (err_constraint_name != NULL) ? errstr(CONSTRAINT_NAME, err_constraint_name) : 0, ! (err_constraint_schema != NULL) ? errstr(CONSTRAINT_SCHEMA, err_constraint_schema) : 0, ! (err_schema_name != NULL) ? errstr(SCHEMA_NAME, err_schema_name) : 0, ! (err_table_name != NULL) ? errstr(TABLE_NAME, err_table_name) : 0)); estate->err_text = NULL; /* un-suppress... */ *************** *** 2842,2847 **** exec_stmt_raise(PLpgSQL_execstate *estate, PLpgSQL_stmt_raise *stmt) --- 2917,2932 ---- 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_constraint_schema != NULL) + pfree(err_constraint_name); + if (err_schema_name != NULL) + pfree(err_schema_name); + if (err_table_name != NULL) + pfree(err_table_name); return PLPGSQL_RC_OK; } *** a/src/pl/plpgsql/src/pl_funcs.c --- b/src/pl/plpgsql/src/pl_funcs.c *************** *** 287,292 **** plpgsql_getdiag_kindname(int kind) --- 287,302 ---- return "RETURNED_SQLSTATE"; case PLPGSQL_GETDIAG_MESSAGE_TEXT: return "MESSAGE_TEXT"; + case PLPGSQL_GETDIAG_COLUMN_NAME: + return "COLUMN_NAME"; + case PLPGSQL_GETDIAG_CONSTRAINT_NAME: + return "CONSTRAINT_NAME"; + case PLPGSQL_GETDIAG_CONSTRAINT_SCHEMA: + return "CONSTRAINT_SCHEMA"; + case PLPGSQL_GETDIAG_SCHEMA_NAME: + return "SCHEMA_NAME"; + case PLPGSQL_GETDIAG_TABLE_NAME: + return "TABLE_NAME"; } return "unknown"; *** a/src/pl/plpgsql/src/pl_scanner.c --- b/src/pl/plpgsql/src/pl_scanner.c *************** *** 109,115 **** static const ScanKeyword unreserved_keywords[] = { --- 109,118 ---- 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("constraint_schema", K_CONSTRAINT_SCHEMA, UNRESERVED_KEYWORD) PG_KEYWORD("current", K_CURRENT, UNRESERVED_KEYWORD) PG_KEYWORD("cursor", K_CURSOR, UNRESERVED_KEYWORD) PG_KEYWORD("debug", K_DEBUG, 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 *************** *** 131,137 **** enum PLPGSQL_GETDIAG_ERROR_DETAIL, PLPGSQL_GETDIAG_ERROR_HINT, PLPGSQL_GETDIAG_RETURNED_SQLSTATE, ! PLPGSQL_GETDIAG_MESSAGE_TEXT }; /* -------- --- 131,142 ---- PLPGSQL_GETDIAG_ERROR_DETAIL, PLPGSQL_GETDIAG_ERROR_HINT, PLPGSQL_GETDIAG_RETURNED_SQLSTATE, ! PLPGSQL_GETDIAG_MESSAGE_TEXT, ! PLPGSQL_GETDIAG_COLUMN_NAME, ! PLPGSQL_GETDIAG_CONSTRAINT_NAME, ! PLPGSQL_GETDIAG_CONSTRAINT_SCHEMA, ! PLPGSQL_GETDIAG_SCHEMA_NAME, ! PLPGSQL_GETDIAG_TABLE_NAME }; /* -------- *************** *** 143,149 **** enum PLPGSQL_RAISEOPTION_ERRCODE, PLPGSQL_RAISEOPTION_MESSAGE, PLPGSQL_RAISEOPTION_DETAIL, ! PLPGSQL_RAISEOPTION_HINT }; /* -------- --- 148,159 ---- PLPGSQL_RAISEOPTION_ERRCODE, PLPGSQL_RAISEOPTION_MESSAGE, PLPGSQL_RAISEOPTION_DETAIL, ! PLPGSQL_RAISEOPTION_HINT, ! PLPGSQL_RAISEOPTION_COLUMN_NAME, ! PLPGSQL_RAISEOPTION_CONSTRAINT_NAME, ! PLPGSQL_RAISEOPTION_CONSTRAINT_SCHEMA, ! PLPGSQL_RAISEOPTION_SCHEMA_NAME, ! PLPGSQL_RAISEOPTION_TABLE_NAME }; /* -------- *** a/src/test/regress/expected/plpgsql.out --- b/src/test/regress/expected/plpgsql.out *************** *** 4509,4511 **** NOTICE: {"(35,78)","(88,76)"} --- 4509,4686 ---- drop function foreach_test(anyarray); drop type xy_tuple; + -- test a new fields in get diagnostics statement + create or replace function getdiagtest_raise() + returns void as $$ + begin + raise exception 'some plpgsql exception' + using column_name = 'some column name', + constraint_name = 'some constraint name', + constraint_schema = 'some constraint schema', + table_name = 'some table name', + schema_name = 'some schema name'; + end; + $$ language plpgsql; + create or replace function getdiagtest() + returns void as $$ + declare + _column_name text; + _constraint_name text; + _constraint_schema text; + _table_name text; + _schema_name text; + _message_text text; + begin + begin + perform getdiagtest_raise(); + exception when raise_exception then + get stacked diagnostics + _message_text = MESSAGE_TEXT, + _column_name = COLUMN_NAME, + _constraint_name = CONSTRAINT_NAME, + _constraint_schema = CONSTRAINT_SCHEMA, + _table_name = TABLE_NAME, + _schema_name = SCHEMA_NAME; + end; + raise notice 'message text: %, column name: %, constraint name: %, constraint schema: %, table name: %, schema name: %', + quote_literal(_message_text), + quote_literal(_column_name), + quote_literal(_constraint_name), + quote_literal(_constraint_schema), + quote_literal(_table_name), + quote_literal(_schema_name); + end; + $$ language plpgsql; + select getdiagtest(); + NOTICE: message text: 'some plpgsql exception', column name: 'some column name', constraint name: 'some constraint name', constraint schema: 'some constraint schema', table name: 'some table name', schema name: 'some schema name' + getdiagtest + ------------- + + (1 row) + + drop function getdiagtest_raise(); + drop function getdiagtest(); + -- real test + create table diagtest_alfa( + id int primary key, + v1 int not null check (v1 > 10), + v2 int, + constraint v1_is_greater_than_v2 check (v1 > v2) + ); + NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "diagtest_alfa_pkey" for table "diagtest_alfa" + create table diagtest_beta(alfa_id int references diagtest_alfa(id)); + create or replace function diagtest(int) + returns void as $$ + declare + _column_name text; + _constraint_name text; + _table_name text; + _schema_name text; + _constraint_schema text; + begin + begin + case $1 + when 1 then + insert into diagtest_alfa values(1,20,3); -- ok + when 2 then + insert into diagtest_alfa values(2, null, null); + when 3 then + insert into diagtest_alfa values(2, 1, null); + when 4 then + insert into diagtest_alfa values(2, 1, 20); + when 5 then + insert into diagtest_beta values(1); -- ok + when 6 then + insert into diagtest_beta values(3); + when 7 then + delete from diagtest_alfa where id = 1; + end case; + exception when others then + get stacked diagnostics + _column_name = COLUMN_NAME, + _constraint_name = CONSTRAINT_NAME, + _constraint_schema = CONSTRAINT_SCHEMA, + _table_name = TABLE_NAME, + _schema_name = SCHEMA_NAME; + RAISE NOTICE e'column name: %\nconstraint name: %\nconstraint schema: %\ntable name: %\nschema name: %', + quote_literal(_column_name), + quote_literal(_constraint_name), + quote_literal(_constraint_schema), + quote_literal(_table_name), + quote_literal(_schema_name); + raise; + end; + end; + $$ language plpgsql; + select diagtest(1); -- ok + diagtest + ---------- + + (1 row) + + select diagtest(1); -- fails - not unique + NOTICE: column name: '' + constraint name: 'diagtest_alfa_pkey' + constraint schema: 'public' + table name: 'diagtest_alfa' + schema name: 'public' + ERROR: duplicate key value violates unique constraint "diagtest_alfa_pkey" + DETAIL: Key (id)=(1) already exists. + CONTEXT: SQL statement "insert into diagtest_alfa values(1,20,3)" + PL/pgSQL function "diagtest" line 12 at SQL statement + select diagtest(2); -- fails - not null + NOTICE: column name: 'v1' + constraint name: '' + constraint schema: '' + table name: 'diagtest_alfa' + schema name: 'public' + ERROR: null value in column "v1" violates not-null constraint + CONTEXT: SQL statement "insert into diagtest_alfa values(2, null, null)" + PL/pgSQL function "diagtest" line 14 at SQL statement + select diagtest(3); -- fails - column's check constraint + NOTICE: column name: '' + constraint name: 'diagtest_alfa_v1_check' + constraint schema: 'public' + table name: 'diagtest_alfa' + schema name: 'public' + ERROR: new row for relation "diagtest_alfa" violates check constraint "diagtest_alfa_v1_check" + CONTEXT: SQL statement "insert into diagtest_alfa values(2, 1, null)" + PL/pgSQL function "diagtest" line 16 at SQL statement + select diagtest(4); -- fails - table's check constraint + NOTICE: column name: '' + constraint name: 'v1_is_greater_than_v2' + constraint schema: 'public' + table name: 'diagtest_alfa' + schema name: 'public' + ERROR: new row for relation "diagtest_alfa" violates check constraint "v1_is_greater_than_v2" + CONTEXT: SQL statement "insert into diagtest_alfa values(2, 1, 20)" + PL/pgSQL function "diagtest" line 18 at SQL statement + select diagtest(5); -- ok + diagtest + ---------- + + (1 row) + + select diagtest(6); -- fails FK + NOTICE: column name: '' + constraint name: 'diagtest_beta_alfa_id_fkey' + constraint schema: 'public' + table name: 'diagtest_beta' + schema name: 'public' + ERROR: insert or update on table "diagtest_beta" violates foreign key constraint "diagtest_beta_alfa_id_fkey" + DETAIL: Key (alfa_id)=(3) is not present in table "diagtest_alfa". + CONTEXT: SQL statement "insert into diagtest_beta values(3)" + PL/pgSQL function "diagtest" line 22 at SQL statement + select diagtest(7); -- fails PK + NOTICE: column name: '' + constraint name: 'diagtest_beta_alfa_id_fkey' + constraint schema: 'public' + table name: 'diagtest_alfa' + schema name: 'public' + ERROR: update or delete on table "diagtest_alfa" violates foreign key constraint "diagtest_beta_alfa_id_fkey" on table "diagtest_beta" + DETAIL: Key (id)=(1) is still referenced from table "diagtest_beta". + CONTEXT: SQL statement "delete from diagtest_alfa where id = 1" + PL/pgSQL function "diagtest" line 24 at SQL statement + drop function diagtest(int); + drop table diagtest_beta; + drop table diagtest_alfa; *** a/src/test/regress/sql/plpgsql.sql --- b/src/test/regress/sql/plpgsql.sql *************** *** 3559,3561 **** select foreach_test(ARRAY[[(10,20),(40,69)],[(35,78),(88,76)]]::xy_tuple[]); --- 3559,3680 ---- drop function foreach_test(anyarray); drop type xy_tuple; + + -- test a new fields in get diagnostics statement + create or replace function getdiagtest_raise() + returns void as $$ + begin + raise exception 'some plpgsql exception' + using column_name = 'some column name', + constraint_name = 'some constraint name', + constraint_schema = 'some constraint schema', + table_name = 'some table name', + schema_name = 'some schema name'; + end; + $$ language plpgsql; + + create or replace function getdiagtest() + returns void as $$ + declare + _column_name text; + _constraint_name text; + _constraint_schema text; + _table_name text; + _schema_name text; + _message_text text; + begin + begin + perform getdiagtest_raise(); + exception when raise_exception then + get stacked diagnostics + _message_text = MESSAGE_TEXT, + _column_name = COLUMN_NAME, + _constraint_name = CONSTRAINT_NAME, + _constraint_schema = CONSTRAINT_SCHEMA, + _table_name = TABLE_NAME, + _schema_name = SCHEMA_NAME; + end; + raise notice 'message text: %, column name: %, constraint name: %, constraint schema: %, table name: %, schema name: %', + quote_literal(_message_text), + quote_literal(_column_name), + quote_literal(_constraint_name), + quote_literal(_constraint_schema), + quote_literal(_table_name), + quote_literal(_schema_name); + end; + $$ language plpgsql; + + select getdiagtest(); + + drop function getdiagtest_raise(); + drop function getdiagtest(); + + -- real test + + create table diagtest_alfa( + id int primary key, + v1 int not null check (v1 > 10), + v2 int, + + constraint v1_is_greater_than_v2 check (v1 > v2) + ); + + create table diagtest_beta(alfa_id int references diagtest_alfa(id)); + + create or replace function diagtest(int) + returns void as $$ + declare + _column_name text; + _constraint_name text; + _table_name text; + _schema_name text; + _constraint_schema text; + begin + begin + case $1 + when 1 then + insert into diagtest_alfa values(1,20,3); -- ok + when 2 then + insert into diagtest_alfa values(2, null, null); + when 3 then + insert into diagtest_alfa values(2, 1, null); + when 4 then + insert into diagtest_alfa values(2, 1, 20); + when 5 then + insert into diagtest_beta values(1); -- ok + when 6 then + insert into diagtest_beta values(3); + when 7 then + delete from diagtest_alfa where id = 1; + end case; + exception when others then + get stacked diagnostics + _column_name = COLUMN_NAME, + _constraint_name = CONSTRAINT_NAME, + _constraint_schema = CONSTRAINT_SCHEMA, + _table_name = TABLE_NAME, + _schema_name = SCHEMA_NAME; + RAISE NOTICE e'column name: %\nconstraint name: %\nconstraint schema: %\ntable name: %\nschema name: %', + quote_literal(_column_name), + quote_literal(_constraint_name), + quote_literal(_constraint_schema), + quote_literal(_table_name), + quote_literal(_schema_name); + raise; + end; + end; + $$ language plpgsql; + + select diagtest(1); -- ok + select diagtest(1); -- fails - not unique + select diagtest(2); -- fails - not null + select diagtest(3); -- fails - column's check constraint + select diagtest(4); -- fails - table's check constraint + select diagtest(5); -- ok + select diagtest(6); -- fails FK + select diagtest(7); -- fails PK + + drop function diagtest(int); + + drop table diagtest_beta; + drop table diagtest_alfa;