Allow editing of view data in the Edit Grid (RM #3997)

[dpage]

Issue migrated from Redmine: https://redmine.postgresql.org/issues/3997
Originally created by Yuri Cherio at 2019-02-14 18:41:08 UTC.

To reproduce:

• highlight a view and choose "View/Edit Data"
• edit any field and click "OK"

The new value doesn't appear in the grid.

In my mind it should simply allow to edit view values and hit save and then let the server to decide whether the update on the view can go through or not

[dpage]

Comment migrated from Redmine: https://redmine.postgresql.org/issues/3997#note-1
Originally created by Dave Page at 2019-02-20 16:42:16 UTC.

Redmine ticket header update:

Name	Old Value	New Value
Tracker changed	Bug	Feature
Subject changed	"View/Edit Data" doesn't work on a view in 4.2	Allow editing of view data in the Edit Grid
Fixed Version changed	4.3

[dpage]

Comment migrated from Redmine: https://redmine.postgresql.org/issues/3997#note-2
Originally created by Dave Page at 2019-02-22 10:39:11 UTC.

The biggest issue with this is figuring out what to use as a key for doing the updates. If there is no unique key, then editing a single row could cause multiple rows to be updated, so we'd need to add additional checks to warn the user if that were going to happen.

[dpage]

Comment migrated from Redmine: https://redmine.postgresql.org/issues/3997#note-3
Originally created by Khushboo Vashi at 2019-03-01 10:33:44 UTC.

Redmine ticket header update:

Name	Old Value	New Value
Sprint changed		Ready

[dpage]

Comment migrated from Redmine: https://redmine.postgresql.org/issues/3997#note-4
Originally created by Khushboo Vashi at 2020-08-21 10:29:08 UTC.

Redmine ticket header update:

Name	Old Value	New Value
Relationship (duplicated) changed		RM #5743

[adityatoshniwal]

@dpage Can you suggest any idea to identify any unique key for updating a view from pgAdmin Query tool data grid? If not then we should probably close this.

[pms967]

I was about to suggest the same thing. Being able to edit DB data from a view can be extremely useful in many cases (particularly when dealing with highly normalized DB schemes, and/or when dealing with tables using numeric fields which can be mapped to more human-friendly text values via lookup tables, etc).

Indeed, the problem of identifying a unique key in a view is not trivial, if possible at all. OTOH, when a view has no obvious unique key, updating several rows in different tables may be exactly the desired goal.

Perhaps a viable solution could be as follows:

1. add a button (or an option in configuration) to allow data editing in views, with a clear warning about the possible caveats (if using a button, open a dialog with the explanation and ok/cancel options);

2. when the user "submits" the changes (to data from a view) for writing, prepare the resulting insert/update queries, and present the SQL code to the user in a dialog allowing him/her to evaluate the resulting changes and either confirm or cancel the execution (and possibly to copy / edit the code before execution).

My 2¢. :-)

[dpage]

PR up: #10322. Simple automatically-updatable views (single base table, no INSTEAD OF triggers, primary key exposed under its own name) can now be edited via View/Edit Data — UPDATE and DELETE, not row insertion. Delegates the updatability/key-uniqueness question to PostgreSQL's own catalogs rather than trying to reverse-engineer it, with a rows-affected safety net on saves as a backstop.