20230909.database_urn.sql

DROP SCHEMA IF EXISTS playground CASCADE;
CREATE SCHEMA IF NOT EXISTS playground;

CREATE DOMAIN playground.isbn AS text CONSTRAINT valid_isbn CHECK (VALUE ~ '^\d{1,5}-\d{1,7}-\d{1,7}-\d{1}$');
CREATE DOMAIN playground.urn_isbn AS text CONSTRAINT valid_urn_isbn CHECK (VALUE ~ '^urn:ISBN:\d{1,5}-\d{1,7}-\d{1,7}-\d{1}$');

CREATE OR REPLACE FUNCTION playground.extract_isbn(isbn, urn_isbn)
  RETURNS BOOL
AS $$
BEGIN
    RETURN $1 = substring($2 from length('urn:ISBN:') + 1);
END;
$$
LANGUAGE plpgsql STABLE;

CREATE OPERATOR playground.= (
    LEFTARG = isbn,
    RIGHTARG = urn_isbn,
    PROCEDURE = playground.extract_isbn
);

CREATE TABLE playground.products
(
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    external_id isbn NOT NULL UNIQUE
);

INSERT INTO playground.products (external_id) VALUES('12345-6789101-2345678-9');

SELECT * FROM playground.products WHERE external_id = 'urn:ISBN:12345-6789101-2345678-9'::urn_isbn