pg_validator

The extension allows you to check the data before COMMIT and sends an error response in the form of json. The data is checked for unique indexes, unique constraints, foreign keys. The data type is not checked.

README in Russian

Error output example

[22000] ERROR: {"id": ["unique"], "email": ["exists"], "title": ["require"]}
Context: PL/pgSQL function trigger_validate() line 125 at RAISE

Getting Started

Install

Download the files from dist to your extension folder PostgreSQL and run the following command.

CREATE EXTENSION "pg_validator"
    SCHEMA "validator"
    VERSION '1.0';

More about the extension and the control file

Usage

To perform the check, you need to add trigger_validate() trigger from the extension to the table.

CREATE TRIGGER "validate"
    BEFORE INSERT OR UPDATE
    ON "public"."users"
    FOR EACH ROW
EXECUTE FUNCTION trigger_validate();

Domains

The extension has popular domains and checks them as separate features.

• alpha
• email
• nickname
• unsigned_bigint
• unsigned_int
• url

-- Example
CREATE TABLE "users"
(
    "id"       SERIAL PRIMARY KEY,
    "email"    EMAIL        NOT NULL UNIQUE,
    "nickname" NICKNAME     NOT NULL UNIQUE,
    "site"     URL          NOT NULL,
    "password" VARCHAR(255) NOT NULL,
    "age"      UNSIGNED_INT NOT NULL
);

FAQ

Why is the data type not checked?

Basic data types such as date, time, datetime, IP address are validated before triggers run and cannot be validated. Another option is to make a function to insert and update data in each table and prevent the table from being edited directly. However, this is a time-consuming task for developers and unnecessary power consumption on the part of the database server. Therefore, I recommend using domains.

Where do checks come from?

trigger_validate() trigger retrieves all constraints and indexes from info tables. If constraint or index checks overlap, or if a similar check has already been performed, then no check will be made. You can read more about this for yourself here.

You can use the test files here to see if it works. To view all messages, you can use the client_min_messages setting.

-- Setting the message output level
SELECT pg_catalog.set_config('client_min_messages'::TEXT, 'WARNING'::TEXT, FALSE);
SELECT pg_catalog.set_config('client_min_messages'::TEXT, 'NOTICE'::TEXT, FALSE);
SELECT pg_catalog.set_config('client_min_messages'::TEXT, 'DEBUG'::TEXT, FALSE);
SELECT pg_catalog.current_setting('client_min_messages'::TEXT);

Files

• helpers/*.sql - helper functions
  • array_is_unique
  • array_overlap_count
  • array_unique
  • is_distinct_from
  • jsonb_array_append
  • jsonb_except
  • to_columns
• types/*.sql - helper types
  • constraint_def - constraint definition
  • set - mathematical set
  • constraint_type - emun('f','u')
  • fk_mode - emun('full','simple')
  • sort_direction - emun('ASC','DESC')
• rules/*.sql - popular rules
  • alpha
  • email
  • exists
  • nickname
  • require
  • unique
  • url
• domains/*.sql - popular domains
  • alpha
  • email
  • nickname
  • unsigned_bigint
  • unsigned_int
  • url
• validate/validate.sql - validate trigger
• test/*.sql - test files

Useful links

• Pseudotypes
• Functions with Variable Numbers of Arguments
• Object Identifier Types