5

I wanted to have an explanation on triggers of Postgres views.

To make clear what I want to ask, I'll give you a very simplified example of my case. In this example we have two tables (table_a, table_b) that joined together make the view in the example (vw_table_ab).

In this example I will use trivial names and simple DDLs/DMLs.

-- TABLE table_a
CREATE TABLE table_a
(
    id              serial PRIMARY KEY,
    timestamp_field timestamp DEFAULT now() NOT NULL,
    boolean_field   boolean   DEFAULT FALSE NOT NULL
);

-- TABLE table_b
CREATE TABLE table_b
(
    id              serial PRIMARY KEY,
    timestamp_field timestamp DEFAULT now() NOT NULL,
    boolean_field   boolean   DEFAULT FALSE NOT NULL,
    id_table_a      integer                 NOT NULL,
    CONSTRAINT "fk_table_a" FOREIGN KEY (id_table_a) REFERENCES table_a (id) ON DELETE CASCADE NOT DEFERRABLE,
    CONSTRAINT "u_table_a" UNIQUE (id_table_a)
);

-- VIEW vw_table_ab
CREATE VIEW vw_table_ab AS (
    SELECT a.timestamp_field AS timestamp_a,
           a.boolean_field   AS boolean_a,
           b.timestamp_field AS timestamp_b,
           b.boolean_field   AS boolean_b
    FROM table_a a
    JOIN table_b b ON a.id = b.id_table_a
);

A trigger function on standard actions (INSERT, UPDATE and DELETE) is linked to this view through an INSTEAD OF trigger.

-- TRIGGER FUNCTION fn_trigger
CREATE FUNCTION fn_trigger() RETURNS trigger LANGUAGE plpgsql AS
$_$
DECLARE
    sql TEXT;
BEGIN
    sql = 'SELECT ' || TG_TABLE_NAME || '_' || lower(TG_OP) || '($1);';

    IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
        EXECUTE (sql) USING NEW;
        RAISE NOTICE '%', sql;
        RETURN NEW;
    ELSE
        EXECUTE (sql) USING OLD;
        RAISE NOTICE '%', sql;
        RETURN OLD;
    END IF;
END;
$_$;

-- TRIGGER tr_table_ab
CREATE TRIGGER tr_table_ab
INSTEAD OF INSERT OR UPDATE OR DELETE ON vw_table_ab
FOR EACH ROW EXECUTE PROCEDURE fn_trigger();

The example I bring has a trigger called only on the insert action, and the function that is executed is this:

-- INSERT FUNCTION vw_table_ab_insert
CREATE FUNCTION vw_table_ab_insert(new vw_table_ab) RETURNS void LANGUAGE plpgsql AS
$_$
DECLARE
    id_table_a integer;
BEGIN
    INSERT INTO table_a (timestamp_field, boolean_field) VALUES (new.timestamp_a, new.boolean_a) 
    RETURNING id 
    INTO id_table_a;

    INSERT INTO table_b (timestamp_field, boolean_field, id_table_a) VALUES (new.timestamp_a, new.boolean_b, id_table_a);
END;
$_$;

Now we can get to my problem. I make an insert on the view, and when the action is triggered, I get a "Not null violation" error becouse I have some NOT NULL constraints on table_a and table_b like in this case:

INSERT INTO vw_table_ab (timestamp_a, boolean_a, timestamp_b, boolean_b) VALUES (now(), NULL, now(), NULL);

Suppose that the previous statement is generated through a programming language framework and I don't want to handle this case in backend code, but I want handle this case in PostgreSQL in the insert function vw_table_ab_insert. So at this point my problem is bound to the new parameter of the function because I have fields of the view that are NULL. But these fields have a DEFAULT value in the definition of the base table, and I want to use that.

...
timestamp_field timestamp DEFAULT now() NOT NULL,
boolean_field   boolean   DEFAULT FALSE NOT NULL
...

My question: How can I manage the NULL values ​​in trigger of the views using the DEFAULT defined in the tables?

Initially I thought of putting IF ... THEN ... inside the function and override null values ​​with DEFAULT expression but I do not really like that. For example, the function would become like this:

CREATE FUNCTION vw_table_ab_insert(new vw_table_ab) RETURNS void LANGUAGE plpgsql AS
$_$
DECLARE
    id_table_a integer;
BEGIN
    IF new.timestamp_a IS NULL THEN
        new.timestamp_a = DEFAULT;
    END IF;

    IF new.boolean_a IS NULL THEN
        new.boolean_a = DEFAULT;
    END IF;

    IF new.timestamp_b IS NULL THEN
        new.timestamp_b = DEFAULT;
    END IF;

    IF new.boolean_b IS NULL THEN
        new.boolean_b = DEFAULT;
    END IF;

    INSERT INTO table_a (timestamp_field, boolean_field)
    VALUES (new.timestamp_a, new.boolean_a) 
    RETURNING id
    INTO id_table_a;

    INSERT INTO table_b (timestamp_field, boolean_field, id_table_a)
    VALUES (new.timestamp_a, new.boolean_b, id_table_a);
END;
$_$;

Someone can help me? Is there another method for handling this case?

1 Answer 1

2

The easiest way would be to use ALTER VIEW ... ALTER col SET DEFAULT to define default values on the view that are the same as the default values on the base table.

Then instead of inserting explicit NULLs, omit the columns from the INSERT statement or insert DEFAULT explicitly. Your resulting view will behave just like a real table.

Sign up to request clarification or add additional context in comments.

2 Comments

What would be the difficult choice? XD Thank you.
Well, something like the complicated function you planned to use.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.