1

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?

  • Are you trying to use the same function as a trigger for all these tables? – McNets May 5 at 15:14
  • Yes, for basically always a same simple pre-processing task each time a new row is inserted from any API, isn't this a good idea? – s.k May 5 at 15:15
  • I don't know if it is a good idea because you have not pointed out the purpose of this scenario. You only indicated a specific problem with your trigger. Perhaps adding why could help to get better answers. – McNets May 5 at 15:56
2

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.

| improve this answer | |
  • Thanks. A lot. Does varlat and varlng hold the names of the columns or the values of the row being inserted for these columns? I'm guessing it's the name (I actually do need the 2 values corresponding to the 2 columns found on the table the trigger is fired) because when I try your nice solution, I'm facing an invalid input syntax for type double precision: buildings_latitude_center – s.k May 5 at 16:22
  • Ok, but then how could I use the names found to actually access the values in the row being inserted, namely NEW."varlat" and NEW."varlng" which are float values? – s.k May 5 at 17:03
  • Yes, great! The thing is that I get the error hereafter while trying to grab the lat, lon values into variables 'lat' 'lon' prior to using them for computations that will get exported as into NEW.new_field : EXECUTE format(NEW.'%I', latcol) INTO lat; is raising: ERROR: syntax error at or near "'%I'". It sounds I cannot access NEW.building_latitude_center with this EXECUTE statement. – s.k May 5 at 17:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.