example.sql

BEGIN;
CREATE OR REPLACE FUNCTION throw_error ()
    RETURNS TRIGGER
    AS $$
BEGIN
    IF (TG_NARGS = 1) THEN
        RAISE EXCEPTION '% (%)', TG_ARGV[0], TG_TABLE_NAME;
    END IF;
    IF (TG_NARGS > 1) THEN
        RAISE EXCEPTION '% (%, %)', TG_ARGV[0], TG_TABLE_NAME, TG_ARGV[1];
    END IF;
    RAISE EXCEPTION 'THROWN_ERROR (%)', TG_TABLE_NAME;
END;
$$
LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION get_curent_user_id ()
    RETURNS uuid
    AS $$
    SELECT
        nullif (current_setting('jwt.claims.user_id', TRUE), '')::uuid;
$$
LANGUAGE 'sql'
STABLE;
DROP TABLE IF EXISTS posts CASCADE;
CREATE TABLE posts (
    id serial PRIMARY KEY,
    title text,
    content text,
    published boolean DEFAULT FALSE,
    approved boolean DEFAULT FALSE,
    author_id uuid NOT NULL DEFAULT get_curent_user_id (),
    publisher_id uuid NOT NULL DEFAULT '85d770e6-7c18-4e98-bbd5-160b512e6c23'
);
CREATE TRIGGER ensure_only_publisher_can_publish
    AFTER UPDATE ON posts
    FOR EACH ROW
    WHEN (
        NEW.publisher_id <> get_curent_user_id ()
            AND
        OLD.published IS DISTINCT FROM NEW.published
    )
EXECUTE PROCEDURE throw_error ('OWNED_COLUMNS', 'published');

CREATE TRIGGER ensure_only_publisher_can_publish_insert
    AFTER INSERT ON posts
    FOR EACH ROW
    WHEN (
        NEW.publisher_id <> get_curent_user_id ()
            AND
        NEW.published IS TRUE
    )
EXECUTE PROCEDURE throw_error ('OWNED_COLUMNS', 'published');

-- author
SELECT
    set_config('jwt.claims.user_id', '79887bd4-73b0-4d7a-bf02-b5320909b9c1', TRUE);

INSERT INTO posts (title, content)
    VALUES ('first post', 'hello world!');

-- publisher
SELECT
    set_config('jwt.claims.user_id', '85d770e6-7c18-4e98-bbd5-160b512e6c23', TRUE);

UPDATE posts SET
    published = TRUE
WHERE id = 1;

COMMIT;

-- INSERT INTO posts (title, content, published)
--     values
-- ('first post', 'hello world!', true);