Tutorial :Postgres Editable Union View



Question:

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?


Solution:1

Disclaimer: almost no experience with updatable views.

Your RETURNING clause is the problem - the links_data table most probably has 7 colums (as shown by your view definition and the ON INSERT rule) and you're returning their contents for the just inserted row with RETURNING * but your view has 8 columns. Check and sync those two lists.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »