Thread

1. Composite types for updatable views

   Malthe <mborch@gmail.com> — 2025-07-16T12:06:48Z

   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
   

2. Re: Composite types for updatable views

   DINESH NAIR <dinesh_nair@iitmpravartak.net> — 2025-07-16T20:10:16Z

   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 <mborch@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 mborch@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