CREATE TABLE the_log (
    ts TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "user" TEXT NOT NULL DEFAULT current_user,
    action TEXT NOT NULL,
    table_schema TEXT NOT NULL,
    table_name TEXT NOT NULL,
    old_row JSONB,
    new_row JSONB,
    CHECK(
        CASE action
            WHEN 'INSERT' THEN old_row IS NULL AND new_row IS NOT NULL
            WHEN 'UPDATE' THEN old_row IS NOT NULL AND new_row IS NOT NULL
            ELSE /*DELETE, and maybe TRUNCATE, if that's supported by access to old rows */
                old_row IS NOT NULL AND new_row IS NULL
        END
    )
) PARTITION BY LIST(table_schema);

CREATE TABLE public_log
    PARTITION OF the_log FOR VALUES IN ('public')
    PARTITION BY LIST(table_name);

CREATE TABLE public_city_log
    PARTITION OF public_log FOR VALUES IN ('city');

CREATE FUNCTION log_change()
RETURNS TRIGGER
LANGUAGE plpgsql
STRICT
AS $$
BEGIN
    INSERT INTO the_log(
        action,
        table_schema,
        table_name,
        old_row,
        new_row)
    VALUES (
        TG_OP,
        TG_TABLE_SCHEMA,
        TG_TABLE_NAME, 
        CASE TG_OP WHEN 'INSERT' THEN NULL ELSE row_to_json(OLD)::jsonb END,
        CASE TG_OP WHEN 'DELETE' THEN NULL ELSE row_to_json(NEW)::jsonb END
    );
    IF TG_OP = 'DELETE' THEN
        RETURN OLD;
    END IF;
    RETURN NEW;
END;
$$;

CREATE TABLE city (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    CHECK(name !~ '^\s*$'),
    population BIGINT NOT NULL, /* Over 2 BILLION people in *one* city?!? */
    CHECK(population >= 0)
);

CREATE TRIGGER city_log
    AFTER INSERT OR UPDATE OR DELETE ON city
    FOR EACH ROW
        EXECUTE PROCEDURE log_change();

INSERT INTO city (name, population)
VALUES('Oakland',419267);

SELECT * FROM the_log;
