I suggested that you use trigger arguments, but it's actually not necessary. You can use the automatic variables TG_TABLE_SCHEMA
and TG_TABLE_NAME
, or use TG_RELID
. These, alongside EXECUTE
for dynamic SQL, let you do what you want:
BEGIN
EXECUTE format('SELECT colname FROM %I', TG_RELID)
END;
or
BEGIN
EXECUTE format('SELECT colname FROM %I.%I', TG_TABLE_SCHEMA, TG_TABLE_NAME)
END;
(Of course these won't work as-is, since the SELECT
has no destination for the data. You have to use EXECUTE format(..) INTO ...
to store the result into a DECLARE
d variable), e.g.
DECLARE
_colvar integer;
BEGIN
EXECUTE format('SELECT colname FROM %I.%I', TG_TABLE_SCHEMA, TG_TABLE_NAME) INTO _colvar;
RAISE NOTICE 'colname value was %',_colvar;
END;
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 with EXECUTE
.
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:
CREATE TABLE tbl (
tbl_id serial PRIMARY KEY,
geom geometry,
coords double precision[]
);
You need the additional module hstore
installed (once per database) for this to work. Alternatively you could also use an undocumented feature of json_populate_record()
/ jsonb_populate_record()
for the same purpose:
CREATE OR REPLACE FUNCTION trg_demo()
RETURNS trigger AS
$func$
BEGIN
EXECUTE format('SELECT ($1 #= hstore(%L, ST_SetSRID(ST_Point($1.%2$I[1], $1.%2$I[2]), 4326)::text)).*'
, TG_ARGV[0], TG_ARGV[1]) -- target (geom), source (coords)
USING NEW
INTO NEW;
RETURN NEW;
END
$func$ LANGUAGE plpgsql;
Trigger:
CREATE TRIGGER demo
BEFORE INSERT OR UPDATE ON tbl
FOR EACH ROW EXECUTE PROCEDURE trg_demo('geom', 'coords');
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 of geometry
, as PostGIS is not installed.
Old sqlfiddle.
Here is an even simpler version, just assigning the bare text
value (which would require the target column geom
to be type text
). The simplified part in bold again:
EXECUTE format('SELECT ($1 #= hstore(%L, $1.%I::text)).*'
, TG_ARGV[0], TG_ARGV[1]) -- target (geom), source (coords)
USING NEW
INTO NEW;
The core feature is the hstore operator #=
to (per documentation):
replace fields in record
with matching values from hstore
Both target and source are columns of the new row, which complicates matters. If the source value was a constant we could simply:
NEW := NEW #= hstore(TG_ARGV[0], 'POINT(123.0 456.0)');
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]
and TG_ARGV[1]
access the first two elements to the (0-based!) text array of arguments passed by CREATE TRIGGER
.
%I
concatenates the argument passed to format()
as identifier (safe against SQL injection).
$1
references the value passed to EXECUTE
in the USING
clause.
We need the cast to ::text
after calculating the geometry, because hstore
takes text
.
We need to decompose the row NEW
for the assignment back, because plpgsql assigns rows as target column-by-column.
Best Answer
Your description contradicts the trigger definition with
BEFORE INSERT OR UPDATE
. Either justBEFORE INSERT
or the description is wrong.Next, your whole trigger function is a misunderstanding. Would just be:
Just assign the desired value before the row is inserted. (No
SET
here.)But really, I would use the
VIEW
Evan suggested.As for your comment, if QGIS somehow messes with the automatic feature of updatable views (like you state in the related question on gis.SE), just keep writing to the table directly. The
VIEW
is for representation only in this case, to display the additional, functionally dependent column.