Postgres Insert Into View Rule with Returning Clause

Question

I am attempting to allow insert statements with a returning clause into a view in Postgres v9.4, but am struggling with the syntax. This is how I want to call the insert statement:

CREATE VIEW MyView AS SELECT a.*, b.someCol1
    FROM tableA a JOIN tableB b USING(aPrimaryKey);
INSERT INTO MyView (time, someCol) VALUES (someTime, someValue) RETURNING *;
INSERT INTO MyView (someCol) VALUES (someValue) RETURNING *;

Note that the default for time is NOW(). This is what I have so far:

CREATE RULE MyRuleName AS ON INSERT TO MyView DO INSTEAD (
    INSERT INTO tableA (time) VALUES COALESCE(NEW.time, NOW());
    INSERT INTO tableB (aPrimaryKey, someCol)
        VALUES (CURRVAL('tableA_aPrimaryKey_seq'), NEW.someValue);
);

The above works to insert the value, but I am struggling to try and figure out how to add the returning statement. I have tried the following without success:

CREATE RULE MyRuleName AS ON INSERT TO MyView DO INSTEAD (
    INSERT INTO tableA (time) VALUES COALESCE(NEW.time, NOW())
        RETURNING *, NEW.someValue;
    INSERT INTO tableB (aPrimaryKey, someCol)
        VALUES (CURRVAL('tableA_aPrimaryKey_seq'), NEW.someValue);
);
-- ERROR:  invalid reference to FROM-clause entry for table "new"

CREATE RULE MyRuleName AS ON INSERT TO MyView DO INSTEAD (
    WITH a AS (INSERT INTO tableA (time)
        VALUES COALESCE(NEW.time, NOW()) RETURNING *)
    INSERT INTO tableB (aPrimaryKey, someCol)
        SELECT aPrimaryKey, NEW.someValue FROM a RETURNING *;
);
-- ERROR:  cannot refer to NEW within WITH query

Argh! Does anyone know of a way to add a returning statement that gets the primary key (SERIAL) and time (TIMESTAMP WITH TIME ZONE) added to the database in the first insert, along with the value of someCol in the second insert? Thanks!

Fogy

I very strongly recommend that you use ON UPDATE DO INSTEAD triggers for this, instead of rules. – Overcritical

Answer 1

You are much better off using an INSTEAD OF INSERT trigger here:

CREATE FUNCTION MyFuncName() RETURNS trigger AS $$
DECLARE
  id integer;
BEGIN
  INSERT INTO tableA (time) VALUES COALESCE(NEW.time, NOW()) RETURNING aPrimaryKey INTO id;
  INSERT INTO tableB (aPrimaryKey, someCol1) VALUES (id, NEW.someValue);
  RETURN NEW;
END; $$ LANGUAGE PLPGSQL;

CREATE TRIGGER MyView_on_insert INSTEAD OF INSERT ON MyView
  FOR EACH ROW EXECUTE PROCEDURE MyFuncName();

Checking the current value of a sequence to see what was inserted in another table is bad bad bad practice. Even while you are here in a single transaction, don't do it.

You are confused about the issue of RETURNING information, because I am confused too when I read your question. Inside of a function use the INTO clause to populate locally declared variables to hold record values which you can then use in subsequent statements. Outside of a function, use the RETURNING clause as you do in your top-most code snippet.

Tremayne

Sorry it took so long to accept this as the answer, but I didn't have time to test your suggestion until today. After researching it a bit, it seems most people suggest using triggers instead of rules due to readability, maintainability, and ease of use. Thanks. – Fogy

currval() isn't just checking the current value of a sequence to see what was inserted in another table like the name might suggest. The manual: Return the value most recently obtained by nextval for this sequence in the current session. (An error is reported if nextval has never been called for this sequence in this session.) Because this is returning a session-local value, it gives a predictable answer whether or not other sessions have executed nextval since the current session did. – Specious

Answer 2

I don't agree with the the hint ("use triggers instead of rules"), because triggers don't allow RETURNING. As written in the Postgresql docu it is a little bit tedious to write the right return list. If you keep the following in mind, it works: You can only use columns from the original table to form a list which returns columns for the view (!). This means that you have to repeat the view expressions including all subqueries. (using WHERE instead of JOIN ... ON). Additionally you have to replace the NEW table by the original table name.

Glamorize