INSERT RETURNING rule for joined view
От | Sava Chankov |
---|---|
Тема | INSERT RETURNING rule for joined view |
Дата | |
Msg-id | 8834b7bc0906011054p7ff6888clb51aefc0facb7538@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: INSERT RETURNING rule for joined view
|
Список | pgsql-general |
I have a view that joins several tables and want to create unconditional INSERT RETURNING rule for it. I succeeded by specifying the RETURNING clause for the first INSERT in the rule, casting NULL for columns that are not present in that table to the correct type:
CREATE TABLE a (id SERIAL PRIMARY KEY, name TEXT);
CREATE TABLE b (id INTEGER REFERENCES a, surname TEXT);
CREATE VIEW j AS (SELECT a.id,a.name, b.surname FROM a NATURAL JOIN b);
CREATE RULE _insert AS ON INSERT TO j DO INSTEAD(
INSERT INTO a (id,name) VALUES (NEW.id, NEW.name) RETURNING id, name, NULL::text;
INSERT INTO b (id,surname) VALUES (NEW.id,NEW.surname) );
Is there a way to make RETURNING return all view columns? If not, I'd like to submit a documentation patch that clarifies the behaviour of CREATE RULE for INSERT RETURNING in that case.
--
cheers,
Sava Chankov
CREATE TABLE a (id SERIAL PRIMARY KEY, name TEXT);
CREATE TABLE b (id INTEGER REFERENCES a, surname TEXT);
CREATE VIEW j AS (SELECT a.id,a.name, b.surname FROM a NATURAL JOIN b);
CREATE RULE _insert AS ON INSERT TO j DO INSTEAD(
INSERT INTO a (id,name) VALUES (NEW.id, NEW.name) RETURNING id, name, NULL::text;
INSERT INTO b (id,surname) VALUES (NEW.id,NEW.surname) );
Is there a way to make RETURNING return all view columns? If not, I'd like to submit a documentation patch that clarifies the behaviour of CREATE RULE for INSERT RETURNING in that case.
--
cheers,
Sava Chankov
В списке pgsql-general по дате отправления: