On 5 June 2013 08:59, Dean Rasheed <dean.a.rash...@gmail.com> wrote: > I'm still not happy with pg_view_is_updatable() et al. and the > information_schema views. I accept that the information_schema views > have to be the way they are because that's what's defined in the > standard, but as it stands, the distinction between updatable and > trigger-updatable makes it impossible in general to answer the simple > question "does foo support UPDATEs?". > > I'm thinking what we really need is a single function with a slightly > different signature, that can be used to support both the information > schema views and psql's \d+ (and potentially other client apps). > Perhaps something like:- > > pg_relation_is_updatable(include_triggers boolean) > returns int >
OK, here's what it looks like using this approach: FUNCTION pg_relation_is_updatable(reloid oid, include_triggers boolean) RETURNS integer FUNCTION pg_column_is_updatable(reloid oid, attnum integer, include_triggers boolean) RETURNS boolean These replace pg_view_is_updatable() and pg_view_is_insertable(). I think I definitely prefer this over the old API, because it gives much greater flexibility. The information schema views all pass include_triggers = false for compatibility with the standard. The return value from pg_relation_is_updatable() is now an integer bitmask reflecting whether or not the relation is insertable, updatable and/or deletable. psql and other clients can more usefully pass include_triggers = true to determine whether a relation actually supports INSERT, UPDATE and DELETE, including checks for INSTEAD OF triggers on the specified relation or any underlying base relations. I thought about having pg_relation_is_updatable() return text, like the GRANT support functions, but I thought that it would make the information schema views harder to write, using a single call to check for updatable+deletable, whereas integer bit operations are easy. There is a backwards-incompatible change to the information schema, reflected in the regression tests: if a view is updatable but not deletable, the relevant rows in information_schema.columns now say 'YES' --- the columns are updatable, even though the relation as a whole isn't. I've initially defined matching FDW callback functions: int IsForeignRelUpdatable (Oid foreigntableid, bool include_triggers); bool IsForeignColUpdatable (Oid foreigntableid, int attnum, bool include_triggers); but I'm now having second thoughts about whether we should bother passing include_triggers to the FDW. If we regard the foreign table as a black box, we only care about whether it is updatable, not *how* that update is performed. Regards, Dean
pg_relation_is_updatable.patch
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers