Re: Updatable Cursors, CTID, and Views

 

---

From: Mark Dexter [mailto:MDEXTER@xxxxxxxxxxxxxxxx]
Sent: 28 July 2004 17:10
To: Dave Page
Subject: RE: [ODBC] Updatable Cursors, CTID, and Views

Mr. Page:  Thanks for the response.  It appears that, if I include the CTID and OID columns in the view definition, I can successfully create updatable cursors without getting errors.  However, I then run into another apparent problem -- that normal views in PostgreSQL are not updatable.  I have started reading about using rules to create, in effect, views that can be updated.  However, it appears that using rules is somewhat complex and restrictive.

 

You are correct that they are not always the easiest things to use, however they are pretty powerful - check out http://www.postgresql.org/docs/7.4/static/rules-views.html and http://www.postgresql.org/docs/7.4/static/rules-update.html for some examples of what you can do. There was a guy working on creating simple view update rules automatically where possible, however this is not as easy as you might think at first glance. I don't know if he's still working on it.

 

If I may, I'd like to give you a quick idea of what I'm trying to do and perhaps get your opinion on whether this can be done in PostgreSQL.  In Oracle and SQL Server, I have created a series of views that are very simple subsets of underlying tables.  For example, a customer master table has customers for multiple companies (the first key column).  A single-company view is defined as "SELECT * FROM CUSTOMERS WHERE COMPANY_CODE = ff_company()", where ff_company() is a function that returns the current company being worked on.

 

In these other databases, single-table views are updatable as long as all of the table's columns are either defined in the view, are null-permisive, or have default constraints.  So these views can be treated exactly as if they are tables by our application.

 

My question is:  Is it possible to define a view or rule in PostgreSQL that would be updatable (insert, update, and delete) where it is a simple query of all columns for a selected subset of the rows of an underlying table? 

 

Yes, it is. There is a nice example of this in the article " Table defaults with View Rules " at http://www.varlena.com/varlena/GeneralBits/44.php

 

Thanks so very much for your help.  Mark Dexter (Seattle, WA, USA)

 

No problem.

 

Regards, Dave.