Обсуждение: Identify primary key in simple/updatable view
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). How can the driver automatically identify the view columns that correspond to the primary key of the underlying table (and more generally the same for keys and indexes)? For "simple" views. Without parsing the SQL that defines the view (unless libpq will serve me a parse tree? Didn't think so.). 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. Thanks in advance for your precious help, -- Lionel
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 clauseGiven 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
** 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
MS-Access lets the user specify which column is the Primary Key to avoid this introspection.
On Fri, Aug 2, 2013 at 8:18 AM, 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).
How can the driver automatically identify the view columns that
correspond to the primary key of the underlying table (and more
generally the same for keys and indexes)? For "simple" views. Without
parsing the SQL that defines the view (unless libpq will serve me a
parse tree? Didn't think so.).
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.
Thanks in advance for your precious help,
--
Lionel
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Regards,
Richard Broersma Jr.