databasepostgresql

PostgreSQL: Cannot refer to NEW in an inner query in a rule


This is a follow up question to PostgreSQL: Inserting into a View made from two tables

I've changed my rule as so:

CREATE RULE Insert_Post AS ON INSERT TO abcd1234.Posts DO INSTEAD
(
    WITH Temp AS
    (
        INSERT INTO abcd1234.Ratable_Entity VALUES
            (NEW.Id, NEW.User_Id, NEW.Publish_Date)
            RETURNING Id
    )
    INSERT INTO abcd1234.Post
        (SELECT Id, NEW.Title, NEW.Content FROM Temp)
);

However now I'm gettint the following error:

ERROR:  cannot refer to NEW within WITH query

Is there any way to do this differently? I've also tried doing RETURNING Id INTO temp_id without a WITH, but got a syntax error.


Solution

  • Create function for that:

    /*
    drop rule if exists ri_vt on vt;
    drop function if exists fn_ivt(text, text);
    drop view if exists vt;
    drop table if exists t2;
    drop table if exists t1;
    */
    create table t1(i serial, x text);
    create table t2(i serial, t1_i int, y text);
    insert into t1(x) values('foo');
    
    create view vt as select t1.i, x, y from t1 left join t2 on (t1.i = t2.t1_i);
    
    create function fn_ivt(p_x text, p_y text) returns setof vt language sql as $$
      with ins_t1 as (insert into t1(x) values(p_x) returning *)
        insert into t2(t1_i, y) select ins_t1.i, p_y from ins_t1 returning t1_i as i, p_x, p_y
    $$;
    
    create rule ri_vt as on insert to vt do instead select * from fn_ivt(new.x, new.y);
    
    insert into vt(x,y) values('a','b'),('c','d');
    select * from vt;
    
    ╔═══╤═════╤══════╗
    ║ i │  x  │  y   ║
    ╠═══╪═════╪══════╣
    ║ 1 │ foo │ ░░░░ ║
    ║ 2 │ a   │ b    ║
    ║ 3 │ c   │ d    ║
    ╚═══╧═════╧══════╝
    (3 rows)