PostGIS if statement inside trigger function not recognising equality of variables

Question

I have an INSTEAD OF INSERT function on a view that is intended to insert data into different tables, based on the SRID of a geometry data column.

Inside the function, I'm unable to successfully execute an IF statement which then executes the INSERT but outside the function, using the same logic the IF statement works as expected.

I've tested the logic outside of the trigger, this returns NOTICE: yes:

DO $$
DECLARE geom geometry;
BEGIN
    geom := cast('srid=3857;Point (1 1)' as geometry);
    IF  ST_SRID(geom) = 3857
        THEN RAISE NOTICE 'yes';
        END IF;
END $$

But inside a function like this:

CREATE OR REPLACE FUNCTION insert_from_view()
    RETURNS trigger
    LANGUAGE 'plpgsql'
AS $$
BEGIN   
    --this notice is here to show that inside the function the SRID is being read correctly
    RAISE NOTICE 'SRID: %',ST_SRID(NEW.geom);
    -- try the if 
    IF ST_SRID(NEW.geom) = 3857 
        THEN raise notice 'yes';
    ELSE raise notice 'SRID % not found', ST_SRID(NEW.geom); END IF; 
    RETURN NEW;
END
$$;

Then executing this query:

INSERT INTO view
VALUES (cast('srid=3857;Point (1 1)' as geometry))

Returns: NOTICE: SRID: 3857 NOTICE: SRID 3857 not found

Is there a reason why inside the function this logic does not work?

I tried to reproduce this, but it worked as it should. – Laurenz Albe Dec 20 '22 at 08:06 — Laurenz Albe, Dec 20 '22 at 08:06

Jim Jones · Accepted Answer · 2022-12-20T16:28:28.787

I was unable to reproduce this error. Perhaps you have different versions of this function (e.g. with different parameters) and you're calling the wrong one. The example below reproduces your environment and the trigger seems to work just fine

CREATE TABLE t (geom geometry(point));
CREATE VIEW sub_view AS SELECT * FROM t;

CREATE OR REPLACE FUNCTION insert_from_view()
RETURNS trigger AS $$
DECLARE srs int := ST_SRID(NEW.geom);
BEGIN   
  IF srs = 3857 THEN 
    RAISE NOTICE 'yes';
  ELSE 
    RAISE EXCEPTION 'invalid SRID > %', srs; 
  END IF; 
  RETURN NEW;
END
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER trg_instead_insert_from_view
INSTEAD OF INSERT ON sub_view 
FOR EACH ROW EXECUTE PROCEDURE insert_from_view();

This works:

INSERT INTO sub_view VALUES ('SRID=3857;POINT(1 1)'::geometry); 

NOTICE:  yes
INSERT 0 1

And this fails:

INSERT INTO sub_view VALUES ('SRID=4326;POINT(2 42)'::geometry); 

ERROR:  invalid SRID > 4326
CONTEXT:  PL/pgSQL function insert_from_view() line 8 at RAISE
SQL state: P0001

Demo: db<>fiddle

My bad, typo in my original question, I've edited now – lachlan_epic Dec 20 '22 at 07:26 — lachlan_epic, Dec 20 '22 at 07:26

PostGIS if statement inside trigger function not recognising equality of variables

1 Answers1