PostgreSQL insert all the matching columns on NEW in a INSTEAD OF trigger

Question

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 :)

From here [plpgsql trigger](https://www.postgresql.org/docs/current/plpgsql-trigger.html) you could use `TG_TABLE_NAME` and combine with [Dynamic SQL](https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN). — Adrian Klaver, May 11 '23 at 14:59
You couldn't use `NEW.*` because the number of columns and their order wouldn't match those of the target tables. Dynamically build the column lists by querying the system catalogs. — JohnH, May 11 '23 at 18:10
@JohnH what would be the best way to do this? Should I build the query using "string manipulation" or is there some easier/more semantic way to do this? — Marnix.hoh, May 11 '23 at 18:15

score 1 · Accepted Answer · answered May 13 '23 at 16:47

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.

PostgreSQL insert all the matching columns on NEW in a INSTEAD OF trigger

1 Answers1