PostgreSQL view INSERT rule to enforce values from a related table/view

user_1235821 0
Viewed 130

I have 2 views:

CREATE VIEW v_foo AS
SELECT
    foo.id AS id,
    foo.name AS name,
    foo.userId AS userId
FROM foo;

and

CREATE VIEW v_bar AS
SELECT
    bar.id AS id,
    bar.name AS name,
    bar.fooId AS fooId,
    foo.userId AS userId
FROM bar
JOIN foo ON foo.id = bar.fooId;

I'm trying to build an INSERT rule

CREATE OR REPLACE RULE v_bar_INSERT AS ON INSERT TO v_bar
DO INSTEAD(
INSERT INTO bar (name, fooId) VALUES (NEW.id, NEW.fooId)
RETURNING bar.id, bar.name, bar.fooId, NULL::integer
    --we don't care about return values for userId
);

My issue is I need to check if a record exists on foo WHERE id=NEW.fooId AND userId=NEW.userId, and fail the insert if it does not. Essentially, v_bar needs to inherit the userId through its relationship to foo, and ensure that when inserting a new record into bar, the id and userId exist as a pair in foo.

We're retooling a schema to work with an existing application layer, which is why the apparent silliness.

postgresql
2 Answers

Idea: Updateable view with defined using

WITH CHECK OPTION

This option controls the behavior of automatically updatable views. When this option is specified, INSERT and UPDATE commands on the view will be checked to ensure that new rows satisfy the view-defining condition (that is, the new rows are checked to ensure that they are visible through the view). If they are not, the update will be rejected.

First view:

CREATE VIEW v_foo AS
SELECT
    foo.id AS id,
    foo.name AS name,
    foo.userId AS userId
FROM foo;

and second:

CREATE VIEW v_bar AS
SELECT
    bar.id AS id,
    bar.name AS name,
    bar.fooId AS fooId,
    (SELECT foo.UserId FROM foo WHERE foo.id = bar.fooId) AS userId
FROM bar
WHERE EXISTS (SELECT 1 FROM foo WHERE foo.id = bar.fooId)
WITH CHECK OPTION;

Sample data:

INSERT INTO v_foo(id, name, userId) VALUES (1,'A',10);
-- "parent" insert: correct

INSERT INTO v_bar(id, name, fooId) VALUES(1,'C',1);  -- here fooId = 1 exists
-- "child" insert: correct

INSERT INTO v_bar(id, name, fooId) VALUES(1,'B',2);  -- here fooId = 2 does not exists
-- "child" insert: error
-- ERROR:  new row violates check option for view "v_bar"
-- DETAIL:  Failing row contains (1, B, 2).

db<>fiddle demo

user_50708790

If adding a userId column to bar table is not a problem, a possible solution could be:

  1. adding the userId column to bar table;
  2. modifying the JOIN clause for v_bar view as follows:
JOIN foo ON foo.id = bar.fooId AND foo.userId = bar.userId;
  1. modifying the INSERT clause for v_bar_INSERT rule as follows:
INSERT INTO bar (name, fooId, userId) VALUES (NEW.id, NEW.fooId, NEW.userId)
  1. adding a FOREIGN KEY on (fooId, userId) column couple of bar table to the same fields of foo table

This would enforce the check to be respected forever not only at INSERT time.

You didn't specify if once an INSERT is performed successfully the check of the presence should be respected in the future as well or if the referring foo record could be removed.

In the first case, this solution will enforce the check for the future as well, in the second case, this solution is not applicable.

user_46909460
Related