Home postgresql assert constraint
Reply: 1

postgresql assert constraint

Adam Gamble
1#
Adam Gamble Published in 2018-02-13 21:55:43Z

Is there a way to ASSERT in PL/pgSQL, but for an exception rather than a condition? i.e. assert that "violates X constraint" will happen.

The closest I could get was:

DECLARE xc integer DEFAULT 0;
BEGIN
    INSERT INTO [...];
    RAISE EXCEPTION 'Should not make it this far';
EXCEPTION
    WHEN integrity_constraint_violation THEN
        RAISE NOTICE 'Caught expected integrity violation';
        xc := xc + 1;
    ASSERT ((SELECT xc) = 1);
END;

This does the job(-ish). If I could specify the type of constraint violation that would be helpful.

Second part of question: how could I wrap this (or similar) into a macro / custom assert function?

Laurenz Albe
2#
Laurenz Albe Reply to 2018-02-14 07:19:41Z

There are more specific SQL states for the specific constraint violations (see the documentation), but essentially that's the best you can get unless you want to parse error messages (which is not a good idea).

You might also want to have a look at the ASSERT statement in PL/pgSQL, which can be used to check conditions.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.329582 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO