Thread: (expert) "insert into VIEW returning" inside an instead of trigger returns nothing
(expert) "insert into VIEW returning" inside an instead of trigger returns nothing
From
Rémi Cura
Date:
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;
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 ;
------------------------------------------------
-- 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 ;
SELECT *
FROM specific_object ;
Re: (expert) "insert into VIEW returning" inside an instead of trigger returns nothing
From
Rémi Cura
Date:
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.
with returnings of INSERT into regular table.
CHeers,
Rémi-C
2015-09-02 13:44 GMT+02:00 Rémi Cura <remi.cura@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 workINSERT INTO editing_specific_object ( orientation,width) VALUES (180, 123) ;
SELECT *
FROM specific_object ;
Re: Re: (expert) "insert into VIEW returning" inside an instead of trigger returns nothing
From
Willy-Bas Loos
Date:
On Wed, Sep 2, 2015 at 2:16 PM, Rémi Cura <remi.cura@gmail.com> wrote:
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.
Yes, the GID is detemined for the table.
But in test.rc_editing_generic_object( ) you are reurning the NEW that you just inserted from test.rc_editing_specific_object( ). Willy-Bas