On Fri, Aug 2, 2013 at 11:18 PM, Lionel Elie Mamane <lionel@mamane.lu> wrote:
Now that PostgreSQL has updatable views, users (of LibreOffice / native PostgreSQL drivers) want to use them... LibreOffice needs a primary key to "locate" updates (that is, construct the WHERE clause of an UPDATE or DELETE).
[...]
For tables, it does that by reading from pg_constraint, but to use that for views, I'd need to parse the SQL, track renamed columns, etc.
Yeah, parsing the view's SQL to try and extract a primary key sounds pretty ugly. That said, the rules for automatically updatable views are quite restrictive -- in practice, the only things that an updatable view can "do" are:
a) expose a subset of the base table's columns
b) rename columns of the base table
c) exclude some of the base table's rows through a WHERE clause
Given these constraints, it should be possible to derive the primary key without too much trouble. The parser would need to determine the name of the base table, and the mapping from the base table's column names to the view's column names. Then check to ensure that the view exposes all columns of the primary key. This limited amount of parsing could be fairly straightforward. [Since functions and expressions aren't allowed in an automatically updatable view, the parser wouldn't even need to worry about them.]
Then this "for information only" primary key would need to be exposed to the client somehow. Would be nice to have a standard place (in the catalogs? in pg_constraint itself?) to keep this metadata, so that clients which do schema introspection to find the relationships between tables could continue to function in the presence of views and foreign tables.
** Actually, it turns out that if you manually INSERT a new pg_constraint row for the view (with appropriate values for contype, conrelid, conkey, etc.) Postgres will simply ignore it. Updates to the view continue to work without any issue..... I wouldn't recommend hacking around with the catalogs in this manner, but perhaps this would solve your issue?
