CODEWITHSUNDEEP
databasepostgresql
SalysBruoga
SalysBruoga

Reputation: 1045

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.

Upvotes: 3

Views: 352

Answers (2)

mesr
mesr

Reputation: 212

Building on top of @Abelisto's answer, here is a complete example that shows how one can leverage the RETURNING clause to return all of the fields that appear in the target view — not merely those contained in the table named in the INSERT statement:

CREATE TABLE tbl_0 (
    id serial PRIMARY KEY,
    ts timestamp NOT NULL DEFAULT current_timestamp
);

CREATE TABLE tbl_1 (
    id int PRIMARY KEY REFERENCES tbl_0 (id),
    txt text
);

CREATE VIEW vw_0 AS
    SELECT id, ts
    FROM tbl_0;

CREATE VIEW vw_1 AS
    SELECT id, ts, txt
    FROM tbl_1 JOIN tbl_0 USING (id);

CREATE FUNCTION ins_0(_new vw_1) RETURNS SETOF vw_0
LANGUAGE sql AS $$
WITH _0 AS ( INSERT INTO tbl_0 
    DEFAULT VALUES
    RETURNING * )
SELECT _0.* FROM _0
$$;

CREATE FUNCTION ret_1(_id int) RETURNS vw_1 
LANGUAGE sql AS $$
SELECT * FROM vw_1 WHERE vw_1.id = _id
$$;

CREATE OR REPLACE RULE rl_1 AS
    ON INSERT TO vw_1 DO INSTEAD
        INSERT INTO tbl_1 (id, txt)
            -- Using a `SELECT` statement allows referencing the
            -- return value of a set-returning function.
            SELECT _0.id, new.txt
            -- Using a function works around the limitation of
            -- references to `new` not being allowed in the `WITH`
            -- part of the `INSERT` statement.
            FROM ins_0(new) AS _0
            -- Using a function works around the limitation of
            -- references to fields from the above statement not
            -- being accessible from within sub-queries.
            RETURNING (ret_1(id)).*;

INSERT INTO vw_1 (txt) VALUES ('abc') RETURNING *;
INSERT INTO vw_1 (txt) VALUES ('def') RETURNING *;
INSERT INTO vw_1 (txt) VALUES ('ghi') RETURNING *;
-- etc.

Output:

 id |             ts             | txt 
----+----------------------------+-----
  1 | 2024-12-29 23:22:25.561368 | abc
(1 row)

INSERT 0 1
 id |            ts             | txt 
----+---------------------------+-----
  2 | 2024-12-29 23:22:25.56499 | def
(1 row)

INSERT 0 1
 id |             ts             | txt 
----+----------------------------+-----
  3 | 2024-12-29 23:22:25.566791 | ghi
(1 row)

INSERT 0 1

Upvotes: 0

Abelisto
Abelisto

Reputation: 15624

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)

Upvotes: 4

Related Questions