[postgis-users] Typmod and triggers

[Paragon Corporation]

Jose,

You've demonstrated two things.

One is a bug which I have ticketed here:
http://trac.osgeo.org/postgis/ticket/1320#comment:2

But the other your ability to change the type of a geometry within a trigger
when its defined as a typmod typed geometry in the table, I'm afriad is by
design
and I suspect by design of a lot of databases.  Let me demonstrate with
dates.


--observe in this case it shouldn't matter what I stuff in date since I
always want to return october 10.
-- 
CREATE TABLE test_dates(gid serial primary key, dt date);
CREATE OR REPLACE FUNCTION public.trigtest_dates() RETURNS trigger AS
$$
BEGIN
NEW.dt = '2011-10-10'::date;
RETURN NEW;
END;
$$ language plpgsql
VOLATILE;


CREATE TRIGGER trigtest_date_before
  BEFORE INSERT
  ON public.test_dates
  FOR EACH ROW
  EXECUTE PROCEDURE public.trigtest_dates();

  INSERT INTO test_dates(dt) VALUES ('hello');

ERROR:  invalid input syntax for type date: "hello"
LINE 1:   INSERT INTO test_dates(dt) VALUES ('hello');

If what you wanted to happen were possible -- I'd be able to get away with
this trick.  The reason I can't is because

NEW record holder itself is typed.  So any data that comes even before the
trigger has to abide by the data type constraints
of the table structure or be able to be unambiguously cast to it since its
got to fit in the NEW structure.

Sine NEW.geom in your case can't hold a POLYGON because it is defined as a
typed MULTIPOLYGON type, you can't fix it in the trigger.

---Now what to do --

Sadly I think your best bet is to not use typmod in this case.  So
--to get it back to an unadorned geometry
ALTER TABLE a ALTER COLUMN geom geometry;

-- to put back constraints on it --
SELECT populate_geometry_columns('a'::regclass);

--if it were a completely new table with no data, you'd have to use
AddGeometryColumn (... passing in false for the last arg)
so it doesn't use typmod.

That will get you back to what you were able to do in 1.5.

I'll document this as a know limitation once I figure out where to put that.

Hope that helps,
Regina
http://www.postgis.us


 

> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net 
> [mailto:postgis-users-bounces at postgis.refractions.net] On 
> Behalf Of Jose Carlos Martinez
> Sent: Tuesday, November 22, 2011 1:53 PM
> To: PostGIS Users Discussion
> Subject: [postgis-users] Typmod and triggers
> 
> Hi all,
> Im using PostgreSQL 9.1 and PG 2.0 under MS Windows.
> 
> Lets say I have a tabla A with a geometry column 'geom' of 
> type geometry(MultiPolygon, 23030).
> then I wrote a trigger (BEFORE INSERT ON, FOR EACH ROW) that changes: 
> New.geom:= ST_GeometryN (New.geom, 1).
> 
> After inserting a row:
> insert into A (geom) values ('SRID=23030;MULTIPOLYGON (((0 0, 
> 10 0, 10 10, 0 0)))'::geometry);
> 
> I got a row inserted in this table with a POLYGON type!!
> 
> 
> And Viceversa:
> I wrote a trigger that change: New.geom:= ST_Multi(geom); 
> then I insert:
> insert into A (geom) values ('SRID=23030; POLYGON ((0 0, 10 
> 0, 10 10, 0 0))'::geometry);
> 
> and then PostgreSQL checks again the typmod geometry before 
> the trigger and it is not possible to insert such a row.
> 
> In PG8.4 without typmod everything worked fine.
> 
> If this is the expected behaviour then I think typmod is 
> adding some important limitations that PostGIS didnt have before.
> If not then what Im doing wrong? can i fix it?
> 
> PD: Sorry if this problem was already post in the email 
> list..i couldnt find it though.
> 
> Thanks in advance,
> Best,
> Jose
> 
> 
> 
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>