I am trying to write a trigger function in Postgres 9.4. Something like this (not working, yet):
CREATE FUNCTION set_point_from_coords(source _regclass, target _regclass)
RETURNS trigger AS $func$
BEGIN
NEW.target := ST_SetSRID(ST_Point(NEW.source[1], NEW.source[2]), 4326);
RETURN NEW;
END;
$func$ LANGUAGE plpgsql
In this case, target
is a column of type geometry
and source
is an array of decimals.
When a row is inserted with a coords
array, I want to convert that to a point
. The above would work if I just hard-coded column names, but I want to do it for different tables and different pairs of columns using the same function. And I don't have direct control over the INSERT
itself.
Here's some of my experimentation: http://sqlfiddle.com/#!15/dddcd/1
Found this related blog post which I'm having a really hard time parsing.
I could live with this running after insert/update instead of before, if that makes it easier to code.
Best Answer
Problem
You had to pick the spot where all possible complications come together.
SQL (or PL/pgSQL) does not allow to parameterize identifiers. You need dynamic SQL with
EXECUTE
for that.But the special plpgsql variable
NEW
in trigger functions is not visible inside dynamic code executed withEXECUTE
.And it's further complicated by passing column names as arguments to the trigger function in
CREATE TRIGGER
.And it's not enough to make the target column dynamic, you want to fetch the source value from another dynamic column of the the row.
Unless you know your way around all the involved matters, I would rather try a simpler approach. Write a separate trigger function for each trigger and assign to the target column without dynamic SQL.
Solution
That said, it can be done with one line of code - and many lines of explanation. For the original example in the question, assuming this table definition:
You need the additional module
hstore
installed (once per database) for this to work. Alternatively you could also use an undocumented feature ofjson_populate_record()
/jsonb_populate_record()
for the same purpose:Trigger:
If you don't understand what's happening here, consider my advice above.
Explanation
I formatted the dynamic computation of the
geometry
bold to help you get a grip on this. Compare with the simple case below.db<>fiddle here - with
point
instead ofgeometry
, as PostGIS is not installed.Old sqlfiddle.
Here is an even simpler version, just assigning the bare
text
value (which would require the target columngeom
to be typetext
). The simplified part in bold again:The core feature is the hstore operator
#=
to (per documentation):Both target and source are columns of the new row, which complicates matters. If the source value was a constant we could simply:
Related:
But we need dynamic SQL to resolve the column name and fetch the source value.
Use
format()
to concatenate the query string safely.TG_ARGV[0]
andTG_ARGV[1]
access the first two elements to the (0-based!) text array of arguments passed byCREATE TRIGGER
.%I
concatenates the argument passed toformat()
as identifier (safe against SQL injection).$1
references the value passed toEXECUTE
in theUSING
clause.We need the cast to
::text
after calculating the geometry, becausehstore
takestext
.We need to decompose the row
NEW
for the assignment back, because plpgsql assigns rows as target column-by-column.