typmod geometry type not changing in before trigger

[robe]

This is the report posted on: ​http://www.postgis.org/pipermail/postgis-users/2011-November/031493.html

I can reproduce on 8.4 and 9.1 running latest build of PostGIS 2.0.

To reproduce:

CREATE TABLE A(gid serial primary key, geom geometry(MultiPolygon, 23030));

CREATE OR REPLACE FUNCTION public.triga() RETURNS trigger AS
$$
BEGIN
NEW.geom = ST_GeometryN(New.geom,1);
RETURN NEW;
END;
$$ language plpgsql
VOLATILE;


CREATE TRIGGER triga_before
  BEFORE INSERT
  ON public.a
  FOR EACH ROW
  EXECUTE PROCEDURE public.triga();

-- this allows  a polygon which should not be legal --
INSERT INTO a(geom) VALUES('SRID=23030;MULTIPOLYGON (((0 0, 10 0, 10 10, 0 0)))'::geometry);

-- st_AsText POLYGON((0 0,10 0,10 10,0 0))

Geography works as expected -see next 2 notes.

[pramsey]

How about w/ varchar?

[robe]

Paul,

Sorry I've been testing two things. One is a bonafied bug in PostGIS because it works fine for geography. I got my tests a bit mixed up. The other is another demonstration of a use case that typmod doesn't work for.

So here is the geography equivalent and it does as expected.

CREATE TABLE b(gid serial primary key, geog geography(MultiPolygon, 4326));
CREATE OR REPLACE FUNCTION public.trigb() RETURNS trigger AS
$$
BEGIN
NEW.geog = ST_GeometryN(New.geog::geometry,1)::geography;
RETURN NEW;
END;
$$ language plpgsql
VOLATILE;


CREATE TRIGGER trigb_before
  BEFORE INSERT
  ON public.b
  FOR EACH ROW
  EXECUTE PROCEDURE public.trigb();

INSERT INTO b(geog) VALUES('MULTIPOLYGON (((0 0, 10 0, 10 10, 0 0)))'::geography);

throws this error — which is the right thing to do:

ERROR:  Geometry type (Polygon) does not match column type (MultiPolygon)
CONTEXT:  PL/pgSQL function "trigb" line 2 at assignment

geometry typmod seems to be missing this secondary check. The other issue which I think is the one bothering the poster is something there by design. I'll test with varchar on that to confirm and that is that PostgreSQL does a signature check to make sure what you are trying to stuff in a table can go there before it even gets to the trigger.