Re: Updatable Cursors, CTID, and Views

________________________________

From: Mark Dexter [mailto:MDEXTER(at)dexterchaney(dot)com]
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.