(expert) "insert into VIEW returning" inside an instead of trigger returns nothing

From: Rémi Cura <remi(dot)cura(at)gmail(dot)com>
To: PostgreSQL General <pgsql-general(at)postgresql(dot)org>
Subject: (expert) "insert into VIEW returning" inside an instead of trigger returns nothing
Date: 2015-09-02 11:44:44
Message-ID: CAJvUf_tuGGTYJO1sm1MvFD0=RHseGhTO29-bQ3Z8hL82R0gb9Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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 ;

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Rémi Cura 2015-09-02 12:16:42 Re: (expert) "insert into VIEW returning" inside an instead of trigger returns nothing
Previous Message Willy-Bas Loos 2015-09-02 11:20:29 Re: FDW and BDR