Hi ,
Yes, i agree with the statement below , a column is updatable if it is
a simple reference to an updatable column of the underlying base relation .
View updatability for composite types (being derived construct) will pose
challenges
Possible temporary work around is:
1.
Avoid composite types in views and use individual columns from base table
2.
using a trigger function to update the view for some use cases
Caution: This email was sent from an external source. Please verify the
sender’s identity before clicking links or opening attachments.
While simple views are automatically updatable [1], this does not extend to
composite types; that is, if a view constructs a composite type for one or more
columns, this column then is not updatable:
> A column is updatable if it is a simple reference to an updatable column of
> the underlying base relation
It would seem simple enough to support composite types if the view column is
constructed using row expression in which each field is "a simple reference to
an updatable column".
As a practical example, this could enable geometric types being exposed at the
view layer, but supported in an underlying table by primitive types.
[1]
https://www.postgresql.org/docs/current/sql-createview.html#SQL-CREATEVIEW-UPDATABLE-VIEWS
Thanks
Dinesh Nair
________________________________
From: Malthe <mbo...@gmail.com>
Sent: Wednesday, July 16, 2025 5:36 PM
To: Postgres hackers <pgsql-hackers@lists.postgresql.org>
Subject: Composite types for updatable views
You don't often get email from mbo...@gmail.com. Learn why this is
important<https://aka.ms/LearnAboutSenderIdentification>
Caution: This email was sent from an external source. Please verify the
sender’s identity before clicking links or opening attachments.
While simple views are automatically updatable [1], this does not extend to
composite types; that is, if a view constructs a composite type for one or more
columns, this column then is not updatable:
> A column is updatable if it is a simple reference to an updatable column of
> the underlying base relation
It would seem simple enough to support composite types if the view column is
constructed using row expression in which each field is "a simple reference to
an updatable column".
As a practical example, this could enable geometric types being exposed at the
view layer, but supported in an underlying table by primitive types.
[1]
https://www.postgresql.org/docs/current/sql-createview.html#SQL-CREATEVIEW-UPDATABLE-VIEWS
