My question is best illustrated with an example:
Let's say we have two tables:
CREATE TABLE usr_static (
id bigint,
dob text,
...
);
CREATE TABLE usr_version (
id bigint,
name text,
gender text,
...
)
And a view:
CREATE VIEW usr AS
SELECT usr_static.id, usr_static.dob, usr_version.name, usr_version.gender, ...
FROM usr_static
LEFT JOIN usr_version ON usr_static.id = usr_version.id;
Now I am trying to create a trigger function for this view that intercepts an INSERT into the view and splits it up into 2 inserts: 1 for the usr_static table and 1 for the usr_version table:
INSERT INTO usr (5, '2023-05-11', 'John', 'male');
-- -->
INSERT INTO usr_static (5, '2023-05-11');
INSERT INTO usr_version ('John', 'male')
My trigger function would then look like:
CREATE FUNCTION my_trigger_func() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
IF (TG_OP = 'INSERT') THEN
INSERT INTO usr_static (
dob
) VALUES (
NEW.dob,
) RETURNING id INTO NEW.id;
INSERT INTO usr_version (
id,
name,
gender
) VALUES (
NEW.id,
NEW.name,
NEW.gender
);
RETURN NEW;
END IF;
END;
$$;
This is a very common pattern in the application that I'm working on. So I was wondering if there is a way to create one trigger that I can apply to multiple views?
So my question is, if it is somehow possible to do something like:
INSERT INTO %_static VALUES (NEW.*) RETURNING id INTO NEW.id;
INSERT INTO %_version VALUES (NEW.*);
Here the % would get replaced with the name of the view (usr in this case). The tricky part here is to somehow dynamically map the values on NEW to the correct table. In other words, without explicitly mapping the columns in the INSERT statement to the values on NEW as in the above function. E.g. the value NEW.dob, would get inserted in the dob column on usr_static.
I hope I have explained my question clearly.
Thank you so much for any help :)
While it's possible to create a trigger function that could be applied to multiple views to perform the described inserts, it's not advisable. A function that worked for the general case would need to query the system catalogs to dynamically create insert statements for each row inserted into a view. This approach would not be very performant. A better approach is to create dedicated functions for each view.
The following demonstates a function that queries the system catalogs to generate a trigger function definition to populate a view's base tables. (This approach can be expanded to handle DELETE and UPDATE as well.)
CREATE OR REPLACE FUNCTION gen_view_to_table_trig_func(source_view regclass, function_name text DEFAULT NULL) RETURNS text
LANGUAGE plpgsql AS
$FUNC$
<<local>>
DECLARE
created_function_name text;
insert_statements text;
view_name text;
view_oid bigint;
view_schema text;
BEGIN
SELECT v.oid, ns.nspname, v.relname
INTO view_oid, view_schema, view_name
FROM pg_class v
JOIN pg_namespace ns
ON v.relnamespace = ns.oid
WHERE v.oid = gen_view_to_table_trig_func.source_view;
created_function_name := COALESCE(function_name, view_name || '_iotf');
WITH tables AS (SELECT DISTINCT t.oid AS table_oid, nt.nspname AS table_schema, t.relname AS table_name
FROM pg_depend dv
JOIN pg_depend dt
ON dv.objid = dt.objid AND dv.refobjid <> dt.refobjid
JOIN pg_class t
ON dt.refobjid = t.oid
JOIN pg_namespace nt
ON t.relnamespace = nt.oid AND t.relkind IN ('r', 'f', 'p')
WHERE dv.refobjid = local.view_oid
AND dv.deptype = 'i'),
INSERTS AS (SELECT FORMAT($$INSERT INTO %I.%I (%s) VALUES (%s)$$,
tables.table_schema,
tables.table_name,
STRING_AGG(FORMAT('%I', ta.attname), ', ' ORDER BY ta.attnum),
STRING_AGG(FORMAT('NEW.%I', ta.attname), ', ' ORDER BY ta.attnum)) AS statement
FROM tables
JOIN pg_attribute ta
ON tables.table_oid = ta.attrelid AND ta.attnum > 0
AND NOT ta.attisdropped
AND ta.attgenerated = ''
JOIN pg_attribute va
ON (ta.attname = va.attname)
WHERE va.attrelid = local.view_oid
GROUP BY tables.table_schema, tables.table_name)
SELECT STRING_AGG(inserts.statement, ';' || CHR(13) || CHR(10))
INTO insert_statements
FROM inserts;
RETURN FORMAT($DEF$
CREATE OR REPLACE FUNCTION %I.%I() RETURNS TRIGGER LANGUAGE plpgsql AS
$BODY$
BEGIN
IF TG_OP = 'INSERT' THEN
%s;
RETURN NEW;
END IF;
IF TG_OP = 'UPDATE' THEN
RETURN NEW;
END IF;
RETURN OLD;
END
$BODY$;
$DEF$,
view_schema,
created_function_name,
insert_statements);
END
$FUNC$;
In addition to matching by column name, it might also be desirable to address foreign key reference columns between a view's base tables. It might also be useful to enhance the INSERTs to handle conflicts, especially if some of the columns are associated with a parent table with multiple child rows.