Postgresql – Postgres list constraints that use a stored procedure

check-constraintsfunctionspostgresqlpostgresql-9.6

I have a postgres database (v9.6) and I have multiple CHECK constraints that use user-defined functions.

something like:

CREATE TABLE payment (
  amount bigint,
  CONSTRAINT amount_gt_0 CHECK (round_amount(amount) > 0)
);

where round_amount is user-defined function.

Is it possible to get a list of all constraint or at least tables that use such constraints?

postgresql version is 9.6

Best Answer

It looks like that there is already a view called information_schema.check_constraint_routine_usage that provide this information.

The view originally only shows routines owned by the current role but you can find the original query by typing \d+ information_schema.check_constraint_routine_usage and tweak it.

UPSERT 1 row at a time with function

A "table-function" is a function returning a set of rows (acting like a table when called with SELECT * FROM myfunc()). What you have is not a table-function. Since nothing is returned you can use a simple call:

SELECT merge_vehicles(vid, cid, vname, reg_no, name, name_1st)
FROM  (
   VALUES
     (2335, 55, '246BDH', '246BDH', '811', 1::numeric) -- example for explicit cast
    ,(2336, 55, '038THX', '038THX', '831', 1)
    , ...
   ) t(vid, cid, vname, reg_no, name, name_1st)        -- arbitrary column names

While that works, upserting 1 row at a time is very inefficient for big sets.

Bulk UPSERT

Assuming (vid, cid) is the primary key of our table test (information missing), I suggest a data-modifying CTE for the UPSERT after taking out an EXCLUSIVE LOCK on the table.

The EXCLUSIVE LOCK still allows concurrent transactions to read from the table, but no writes. It is only necessary if you actually can have concurrent transactions writing to the same table.

BEGIN;

LOCK TABLE test IN EXCLUSIVE MODE;

-- Or use a temp table for new rows. See below.
WITH my_rows(vid, cid, vname, reg_no, name, name_1st) AS (
   VALUES
      (2335, 55, '246BDH', '246BDH', '811', 1::numeric) -- example for explicit cast
     ,(2336, 55, '038THX', '038THX', '831', 1)
     , ...
   )
, upd AS (
    UPDATE test t
    SET   (  vname,   reg_no,   name,   name_1st)
        = (m.vname, m.reg_no, m.name, m.name_1st)
    FROM   my_rows m
    WHERE  t.vid = m.vid        -- match on PK columns
    AND    t.cid = m.cid
    RETURNING t.vid, t.cid      -- return only PK columns
    )
INSERT INTO test
      (  vid,   cid,   vname,   reg_no,   name,   name_1st)
SELECT m.vid, m.cid, m.vname, m.reg_no, m.name, m.name_1st
FROM   my_rows m
LEFT   JOIN upd USING (vid, cid)
WHERE  u.vid IS NULL;           -- only remaining rows

COMMIT;                         -- releases all locks

If the list is long, consider a temp table populated with COPY to replace the CTE my_rows and its VALUES expression.

Call set-returning function multiple times in one SELECT

^{(Before update to the question)}

Assuming we are dealing with a set-returning function (a.k.a. "table-function").

You can use a VALUES expression for literal input of a derived table of parameters (or any other derived or actual table in its place) and conveniently use a LATERAL JOIN (Postgres 9.3+) to get the united results in one query. You may want to order in some way ...

SELECT f.*
FROM  (
   VALUES
     (2335, 55, '246BDH', '246BDH', '811', 1::numeric) -- example for explicit cast
    ,(2336, 55, '038THX', '038THX', '831', 1)
    , ...
   ) t(vid, cid, vname, reg_no, name, name_1st)        -- arbitrary column names
     , merge_vehicles(vid, cid, vname, reg_no, name, name_1st) f;

The LATERAL JOIN is implicit with function calls in the FROM list. More details here:

How to turn JSON array into Postgres array?

Numeric literals in this example default to type integer and string literals become text. Add explicit casts if you need specific types.

Postgresql – CHECK constraint to limit certain rows

The problem however is, that it doesn't allow updating such row. The updated row seems to be considered a doublet.

That's one of the problems.

Is there a way to fix this or is it a conceptual problem CHECK constraints just don't cover?

Yes, use "on insert" and "on update" triggers, and yes "check" constraints are unsuited.

you'll also need an "on update" trigger on "attribute" so that changes of singular are verified against the existing data.

A different approach would be to have partial unique indices generated by a trigger that tracks inserts, deletes, and updates on attribute.

Eg. if you have a attribute day_of_birth which is singular you would do

CREATE UNIQUE INDEX "object_day_of_birth_unique" ON objects(object) WHERE attribute='day_of_birth';

This leverages something that the database engine is good at, and so will give a slight performance boost, but as you're using EAV I suspect perfromance is not important.

when writing such a trigger be sure to use quote_literal and quote_ident where apropriate in execute expressions.

Best Answer

Related Solutions

PostgreSQL – How to Call UPSERT Function Multiple Times with Different Inputs

UPSERT 1 row at a time with function

Bulk UPSERT

Call set-returning function multiple times in one SELECT

Postgresql – CHECK constraint to limit certain rows

Related Question