Re: Request for new function in view update

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 Thu, Jun 1, 2023 at 8:43 PM Heikki Linnakangas <hlinnaka(at)iki(dot)fi> wrote:

> 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)
>
>