Re: Identify primary key in simple/updatable view

On Fri, Aug 2, 2013 at 11:18 PM, Lionel Elie Mamane <lionel(at)mamane(dot)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?

Regards,
Andrew Tipton