Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE foo(id INT, name TEXT, userId INT)
CREATE TABLE bar(id SERIAL, name TEXT, fooId INT)
CREATE VIEW v_foo AS
SELECT
    foo.id AS id,
    foo.name AS name,
    foo.userId AS userId
FROM foo;
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;
--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
--);
INSERT INTO v_foo(id, name, userId)
VALUES (1,'A',10);
1 rows affected

INSERT INTO v_bar(id, name, fooId)
VALUES(1,'B',2)
ERROR:  new row violates check option for view "v_bar"
DETAIL:  Failing row contains (1, B, 2).


INSERT INTO v_bar(id, name, fooId)
VALUES(1,'C',1)
1 rows affected

SELECT *
FROM v_foo;

SELECT *
FROM v_bar;


id
name
userid


1
A
10




id
name
fooid
userid


1
C
1
10