In a PostgreSQL CHECK constraint using a boolean stored procedure written in PL/Python, can the detail of the error message be updated?

Question

The qustion's in the (albeit long) title. I have code in this fashion:

-- create the tables for models
CREATE TABLE invoice(
    id SERIAL PRIMARY KEY,
    value VARCHAR(8)
      CONSTRAINT valid_money CHECK(validate_invoice_money(value))
);

with this procedure:

CREATE OR REPLACE FUNCTION validate_invoice_money(test VARCHAR) RETURNS BOOLEAN AS $$
    import plpy
    import re

    if (re.match("^[0-9]+\\.[0-9]{2}$", test) == None):
        return False

    return True
$$ LANGUAGE plpython3u IMMUTABLE;

(the specifics of this example are not important, I know there's better ways to validate a value than this, such as using the MONEY type.)

When an insert is attempted that fails this check, I get the following error:

ERROR  : new row for relation "invoice" violates check constraint "valid_model"
DETAIL : Failing row contains (1, "notvalid").

(the error/detail descriptors are my own code, but a PostgreSQL error has an error and a detail field provided.)

Is there any way for me to change the "detail" portion of this error from my Python procedure?

score 2 · Accepted Answer · answered Dec 26 '17 at 00:25

In Postgres 9.6+ you can raise an error with detailed messages from a plpython function using utility functions, e.g.:

CREATE OR REPLACE FUNCTION validate_invoice_money(test VARCHAR) 
RETURNS BOOLEAN AS $$
    import re

    if (re.match("^[0-9]+\\.[0-9]{2}$", test) == None):
        plpy.error("custom exception message",
            detail="some info about exception",
            hint="hint for users")
        return False

    return True
$$ LANGUAGE plpython3u IMMUTABLE;

insert into invoice
values (default, 'notvalid');

ERROR:  plpy.Error: custom exception message
DETAIL:  some info about exception
HINT:  hint for users
CONTEXT:  Traceback (most recent call last):
  PL/Python function "validate_invoice_money", line 8, in <module>
    hint="hint for users")
PL/Python function "validate_invoice_money"

This answers the question, great job! – Sam Weaver Dec 27 '17 at 17:47 — Sam Weaver, Dec 27 '17 at 17:47

wildplasser · Answer 2 · 2017-12-25T23:58:40.567

You don't need an additional language; you can use a plain regexp for the value constraint:

-- create the tables for models
CREATE TABLE invoice(
    id SERIAL PRIMARY KEY,
    val VARCHAR(8)
      -- CONSTRAINT valid_money CHECK(validate_invoice_money(value))
      CONSTRAINT valid_money CHECK(val ~ '^[0-9]+\.[0-9]{2}$' )
);

INSERT INTO invoice (val) VALUES ('0123.98' ); -- success
INSERT INTO invoice (val) VALUES ('a123.98' ); -- failure

SELECT * FROM invoice;

Result:

CREATE TABLE
INSERT 0 1
ERROR:  new row for relation "invoice" violates check constraint "valid_money"
DETAIL:  Failing row contains (2, a123.98).
 id |   val   
----+---------
  1 | 0123.98
(1 row)

And to answer the question: I do not think you can get additional info on the constraint violation.

This doesn't exactly answer the question, but it is definitely useful, thanks! — Sam Weaver, Dec 27 '17 at 17:47

In a PostgreSQL CHECK constraint using a boolean stored procedure written in PL/Python, can the detail of the error message be updated?

2 Answers2