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?