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 ;
