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?