Re: (expert) "insert into VIEW returning" inside an instead of trigger returns nothing - Mailing list 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 ;