11

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.

0

2 Answers 2

19

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 PL/pgSQL variable NEW in trigger functions is not visible inside dynamic code executed with EXECUTE. You have to pass it in.

  • Passing column names as arguments to the trigger function in CREATE TRIGGER further complicates the workflow.

  • And it's not enough to make the target column dynamic, you want to fetch the source value from another dynamic column of the row.

Unless you know your way around the involved matters, 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[]
);

The first version of this answer used the additional module hstore. Since Postgres 13 an equivalent feature of json_populate_record() / jsonb_populate_record() is documented. See:

CREATE OR REPLACE FUNCTION trg_demo()
  RETURNS trigger
  LANGUAGE plpgsql AS 
$func$
BEGIN
   EXECUTE format('SELECT (json_populate_record($1, json_build_object(%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$;

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.

fiddle -- with point instead of geometry, as PostGIS is not installed.
Old sqlfiddle

Here is a simpler version, just assigning the bare text value (would require the target column geom to be type text). The simplified part in bold again:

   EXECUTE format('SELECT (json_populate_record($1, json_build_object(%L, $1.%I))).*'
                , TG_ARGV[0], TG_ARGV[1])  -- target (geom), source (coords)
   USING  NEW
   INTO   NEW;

The core feature is this detail for json_populate_record(), documented since Postgres 13:

However, if base isn't NULL then the values it contains will be used for unmatched columns.

Both target and source are columns of the new row, which complicates matters. If the source value was a constant we could simply:

NEW := json_populate_record(NEW, json_build_object(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.

  • Casting to ::text after calculating the geometry because the intermittent json stores a string value.

  • Decompose the row NEW for the assignment back, because PL/pgSQL assigns rows as target column-by-column.

4
  • 1
    Best answer I've ever received on any SE site! I will follow your advise and settle with a function per table for now as I do not fully understand your code yet.
    – Ivan
    Feb 1, 2016 at 0:48
  • Thanks this helped me realize I don't want to do this.
    – sage88
    May 3, 2018 at 3:41
  • IS this answer still relevant in 2024? Can we dynamically address columns in NEW now? Feb 28 at 19:40
  • 1
    @Slava Yes, and that's not going to change. I only switched out the operator that required the additional module hstore. We can use a built-in JSON function now. Feb 28 at 22:42
0

Just go with different language like pl/tcl. It has support for parameterize identifiers. Here is example from my use case implementing append-only tables.

CREATE EXTENSION IF NOT EXISTS pltcl;

CREATE OR REPLACE FUNCTION handle_historical_records_on_data_insert() RETURNS trigger AS $$
    spi_exec "UPDATE $TG_table_schema.$TG_table_name SET $1=gen_random_uuid() WHERE $1='$NEW($1)'"
    set NEW($2) $NEW($1)

    return [array get NEW]
$$ LANGUAGE pltcl;

CREATE TRIGGER handle_historical_records_on_data_insert
  BEFORE INSERT ON tiers
  FOR EACH ROW
  EXECUTE FUNCTION handle_historical_records_on_data_insert (tier_id, tier_id_historical);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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