I think I got it, I have to always return something (like NEW) in the instead of trigger, but fill NEW with returnings of INSERT into regular table. CHeers, Rémi-C
2015-09-02 13:44 GMT+02:00 Rémi Cura <remi.c...@gmail.com>: > Hey list, > I'm stuck on a problem that I can't figure out (postgres 9.3). > In short, using an > INSERT INTO __view_with_trigger__ ... RETURNING gid INTO _gid; > returns nothing. > I need this feature because I use views on tables as user interface. > > This must have to do with the postgres order of execution, > because inserting into a table instead of the view of the table returns > the expected result. > > Here is a synthetic example (of course the real use really requires this > kind of architecture), > any help is much appreciated, > because I don't see any work-around (except not using view at all, which > would be terrible data duplication in my case) > > Cheers, > Rémi-C > > > > ------------------------------------------------ > -- test inserting and instead of trigger -- > ----------------------------------------------- > > CREATE SCHEMA IF NOT EXISTS test ; > SET search_path to test, public ; > > DROP TABLE IF EXISTS generic_object CASCADE; > CREATE TABLE generic_object ( > gid SERIAL PRIMARY KEY > , orientation float > ) ; > > DROP VIEW IF EXISTS editing_generic_object ; > CREATE VIEW editing_generic_object AS( > SELECT gid, > degrees(orientation) AS orientation > FROM generic_object > ) ; > > > DROP TABLE IF EXISTS specific_object CASCADE ; > CREATE TABLE specific_object ( > gid int references generic_object (gid) ON DELETE CASCADE > , width float > ) ; > > DROP VIEW IF EXISTS editing_specific_object ; > CREATE VIEW editing_specific_object AS( > SELECT g.gid > , g.orientation > , so.width > FROM specific_object AS so LEFT OUTER JOIN > generic_object AS g USING (gid) > ) ; > > > > > DROP FUNCTION IF EXISTS test.rc_editing_generic_object() CASCADE ; > CREATE OR REPLACE FUNCTION test.rc_editing_generic_object( ) > RETURNS trigger AS $BODY$ > /** @brief : this trigger deals with editing generic object*/ > DECLARE > BEGIN > IF TG_OP = 'DELETE' THEN DELETE FROM test.generic_object WHERE gid > = OLD.gid ; RETURN OLD ; > ELSIF TG_OP = 'INSERT' THEN INSERT INTO test.generic_object > (orientation) VALUES (radians(NEW.orientation) ) ; > ELSE UPDATE test.generic_object SET orientation = > radians(NEW.orientation) ; > END IF ; > > RETURN NEW ; > END ; > $BODY$ LANGUAGE plpgsql VOLATILE; > > > DROP TRIGGER IF EXISTS rc_editing_generic_object ON > test.editing_generic_object ; > CREATE TRIGGER rc_edit_street_object_pedestrian INSTEAD OF UPDATE OR > INSERT OR DELETE > ON test.editing_generic_object > FOR ROW EXECUTE PROCEDURE rc_editing_generic_object( ) ; > > > > > DROP FUNCTION IF EXISTS test.rc_editing_specific_object() CASCADE ; > CREATE OR REPLACE FUNCTION test.rc_editing_specific_object( ) > RETURNS trigger AS $BODY$ > /** @brief : this trigger deals with editing specific object*/ > DECLARE > _gid int; > BEGIN > IF TG_OP = 'DELETE' THEN DELETE FROM test.generic_object WHERE gid > = OLD.gid ; RETURN OLD ; > ELSIF TG_OP = 'INSERT' THEN > --does not works > INSERT INTO test.editing_generic_object (orientation) VALUES ( > NEW.orientation) RETURNING gid INTO _gid; > --does works > --INSERT INTO test.generic_object (orientation) VALUES ( > radians(NEW.orientation) ) RETURNING gid INTO _gid; > > RAISE WARNING 'here is the gid deduced after insertion : %', > _gid ; > INSERT INTO test.specific_object (gid, width) VALUES (_gid, > NEW.width) ; > ELSE > UPDATE test.editing_generic_object AS e SET orientation = > NEW.orientation WHERE e.gid = NEW.gid; > UPDATE test.specific_object AS s SET width = NEW.width WHERE > s.gid = NEW.gid; > END IF ; > RETURN NEW ; > END ; > $BODY$ LANGUAGE plpgsql VOLATILE; > > > DROP TRIGGER IF EXISTS rc_editing_specific_object ON > test.editing_specific_object ; > CREATE TRIGGER rc_editing_specific_object INSTEAD OF UPDATE OR INSERT OR > DELETE > ON test.editing_specific_object > FOR ROW EXECUTE PROCEDURE rc_editing_specific_object( ) ; > > --testing > > --inserting into generic : works > INSERT INTO editing_generic_object ( orientation) VALUES (180) ; > SELECT * > FROM generic_object ; > > -- insert into specific : don't work > INSERT INTO editing_specific_object ( orientation,width) VALUES (180, > 123) ; > SELECT * > FROM specific_object ; >
