I have a table which stores 'links' between 2 people. In order prevent further complications down the road on an application I am building, I want to create an editable view, that shows the link records and an inverse copy of the link records.
Meaning if Joe is linked to Sally, then the view should show Joe linked to Sally and Sally linked to Joe.
So I have created a UNION as follows:
CREATE VIEW links AS
SELECT id,
link_origin_id AS person_id,
link_origin_id,
link_rcvd_id,
link_type,
link_summary,
created_at,
updated_at
FROM links_data
UNION
SELECT id,
link_rcvd_id,
link_origin_id,
link_rcvd_id,
link_type,
link_summary,
created_at,
updated_at
FROM links_data
The view works fine. Note that the view creates an additional column 'person_id' which is not in the underlying table.
I am running into trouble creating postgres rules that will edit the underlying table.
Specifically, I can successfully edit a table view when it is not a UNION view. But below is what occurs when I try to write a rule with a UNION view:
CREATE RULE inverse_links AS ON INSERT TO links DO INSTEAD
INSERT INTO links_data
(id, link_origin_id, link_type, link_summary, link_rcvd_id, created_at,
updated_at)
VALUES (nextval('people_id_seq'), new.link_origin_id, new.link_type,
new.link_summary, new.link_rcvd_id, new.created_at, new.updated_at)
RETURNING *;
The above rule should redirect the edits to the underlying table 'links_data'.
But I am getting the following error:
ERROR: RETURNING list's entry 3 has different type from column "link_origin_id"
********** Error **********
ERROR: RETURNING list's entry 3 has different type from column "link_origin_id"
SQL state: 42P17
The 2 things I feel might be the problem is that 1) the view has an additional column which is causing the column types to not match up or 2) there might be something with the fact that the table is a UNION on itself and editing this might be a problem.
Any idea of where I can go with this?