postgresql trigger –> trigger function –> sub trigger function chain

by

The question:

I have a complex trigger function that I wish to break out into multiple sub functions. The trigger would call the master function and the master function would have logic in it to decide which sub function to call. This is as far as I’ve gotten:

create table dbo.thing (thingid int);
create table dbo.thingtwo (thingid int);

create or replace function dbo.tf2(thingid int) returns void as $$
    insert into dbo.thingtwo values (thingid);
$$ language sql; 

create or replace function dbo.tf1() returns trigger as $thinginsert$
begin    
    perform dbo.tf2(new.thingid);
    return null;
end;
$thinginsert$ language plpgsql; 

create trigger thinginsert
after insert on dbo.thing
for each row execute procedure dbo.tf1();

The problem is I want to pass the entire new.* into the second trigger function, not just some columns. Ultimately there would be conditional logic in dbo.tf1() to decide whether to go dbo.tf2() or dbo.tf3() etc.

How can I make this work?

I’m using Postgres 12.8.

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

Inside a trigger on the table thing, NEW is of the composite type thing that has the same attribute names and data types as the table columns. So,

create or replace function dbo.tf2(thing_row thing) returns void as $$
    insert into dbo.thingtwo values (thing_row.thingid);
$$ language sql; 

create or replace function dbo.tf1() returns trigger as $thinginsert$
begin    
    perform dbo.tf2(new);
    return null;
end;
$thinginsert$ language plpgsql;

Fiddle.

Related Q&A on SO.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment