Postgresql – Set var=“NEW.this_word_column” in a trigger function if “word” is present in a column name

pattern matchingplpgsqlpostgresqltrigger

Context

I'm using PostgreSQL 10.12.

I want to be able to fire a trigger function on some specific tables with geographic data.

This trigger function is designed to create a PotGIS point into a column geom from lat and lon values.
I want to apply this same trigger function over several tables each time a new row is inserted.
The naming of columns holding latitude and longitude values is not consistent across tables but it follows a pattern…

What I am 100% sure is that:
1. there are always two columns containing latitude and longitude values,
2. the name of theses columns are not always the same in the
different tables (otherwise it would have been too simple), but the word 'latitude' and the word 'longitude'
always appear in their name and never appear in other column names.

E.g. in table A:

_loc_longitude_ and _loc_latitude_

and in table B:

_building_longitude_center and _building_latitude_center

for example (+ many others).

The trigger function is as follows:

CREATE OR REPLACE FUNCTION make_point_with_latlon()
  RETURNS trigger AS
  $$
  DECLARE
      varlon := NULL;
      varlat := NULL;
  BEGIN
      IF to_jsonb(NEW) SIMILAR TO '.*longitude.*' THEN
          varlon := NEW.the_column_whith_longitude_in_its_name;
      END IF;
      IF to_jsonb(NEW) SIMILAR TO '.*latitude.*' THEN
          varlat := NEW.the_column_whith_latitude_in_its_name;
      END IF;
      NEW.geom = ST_MakePoint(varlon, varlat);
  END
  $$
LANGUAGE 'plpgsql';

With the following triggers:

CREATE TRIGGER make_point_with_latlon_but
    BEFORE INSERT OR UPDATE ON schema.table1
    FOR EACH ROW
    EXECUTE PROCEDURE schema.make_point_with_latlon();

CREATE TRIGGER make_point_with_latlon_but
    BEFORE INSERT OR UPDATE ON schema.table2
    FOR EACH ROW
    EXECUTE PROCEDURE schema.make_point_with_latlon();

CREATE TRIGGER make_point_with_latlon_but
    BEFORE INSERT OR UPDATE ON schema.table3
    FOR EACH ROW
    EXECUTE PROCEDURE schema.make_point_with_latlon();

-- (...and many more other tables that need the same trigger).

I don't know how to search for the pattern .*latitude.* in the columns names passed to the function, and how to get their value into two variables that I can use to build the point with.
And my googling skills for searching these kind of stuff are far from perfect, hence returning noisy results.

Question

Is there any possibility to do that?

Best Answer

You can use INFORMATION_SCHEMA views and special variables: TG_TABLE_SCHEMA, TG_TABLE_NAME

Quoted from docs:

When a PL/pgSQL function is called as a trigger, several special variables are created automatically in the top-level block. They are:

TG_TABLE_NAME: Data type name; the name of the table that caused the trigger invocation.

TG_TABLE_SCHEMA: Data type name; the name of the schema of the table that caused the trigger invocation.

Given next example:

create table a (id int primary key, latitude int, longitude int);
create table b (id int primary key, _loc_latitude int, _loc_longitude int);
create table c (result text);

create function trg_tables()
returns trigger as
$$
declare
  varlat text;
  varlng text;
  cmd text;
  vallat int;
  vallng int;
begin  
  varlat := (select column_name
            from   information_schema.columns
            where  table_schema = TG_TABLE_SCHEMA
                   and table_name = TG_TABLE_NAME
                   and column_name like '%latitude%');

  varlng := (select column_name
            from   information_schema.columns
            where  table_schema = TG_TABLE_SCHEMA
                   and table_name = TG_TABLE_NAME
                   and column_name like '%longitude%');

  execute 'select $1.' || varlat
  using NEW
  into vallat;

  execute 'select $1.' || varlng
  using NEW
  into vallng;

  execute format('insert into c values (''lat: '' || ''%s'' || ''  lng '' || ''%s'' )',
                 vallat::text, vallng::text);

  return NEW;
end;
$$
language plpgsql;

create trigger trg after insert on a
  for each row execute procedure trg_tables();
create trigger trg after insert on b
  for each row execute procedure trg_tables(); 

insert into a values (1,11,1);
insert into b values (1,20,2);
select * from c;

| result         |
| :------------- |
| lat: 11  lng 1 |
| lat: 20  lng 2 |

db<>fiddle here

It's just an example on how to use INFORMATION_SCHEMA and special variables inside a trigger, but maybe a Postgres pro-user can give you a better solution.

Related Question