Hi Heikki
PostgreSQL supports only one-table views, which means that the relational operators are limited to "selection" and "projection." I have provided update methods for these two, plus for two kinds of joins and unions.
I discuss a hierarchical join, when two tables together define an entity. The classic example is an invoice. One table, the Invoice Master table, has a row for each invoice. The daughter table, the Invoice Detail table, has a row for each item on each invoice. The two tables are linked by having the same key -- the invoice number. When updating, a detail row should never be left without a corresponding master row, though master rows without detail rows can exist.
The second type is a foreign key. For example, an invoice detail line will have an item number column, containing a key for the Item table. The key for the Invoice Detail table is unrelated to the key for the Item table. Deleting an Invoice Detail row should never delete an Item row, and adding an Invoice Detail row should never add an Item row.
These two examples have the same relational operator -- join -- that have different semantics -- hierarchical and foreign key -- leading to different update methods. PostgreSQL can determine which type of join is present by examining the primary keys of the two tables, and by examining other clues, such as referential integrity checking.
Adding join and union would allow many more views to be updateable.
Yours,
Terry Brennan
On 01/06/2023 13:18, Terry Brennan wrote:
> Hello all,
>
> I am a researcher in databases who would like to suggest a
> new function. I am writing to you because you have an active developer
> community. Your website said that suggestions for new functions should
> go to this mailing list. If there is another mailing list you prefer,
> please let me know.
You're in the right place.
> My research is in updating views -- the problem of translating an update
> in a view to an update to a set of underlying base tables. This problem
> has been partially solved for many years, including in PostgreSQL, but a
> complete solution hasn't been found.
Yeah, PostgreSQL only supports updating views in some simple cases [1].
Patches to handle more cases welcome.
[1]
https://www.postgresql.org/docs/current/sql-createview.html#SQL-CREATEVIEW-UPDATABLE-VIEWS
--
Heikki Linnakangas
Neon (https://neon.tech)