Re: how to find out whether a view is updatable

On 4 June 2013 23:35, Michael Paquier <michael(dot)paquier(at)gmail(dot)com> wrote:
>
>
>
> On Wed, Jun 5, 2013 at 12:59 AM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
>>
>> I was looking for a way in which the average psql user could learn
>> whether a view is updatable. I was expecting something in \d, \d+, \dv,
>> \dv+, or a NOTICE from CREATE VIEW. So far, the only way appears to be
>> through the information schema or the underlying pg_view_is_updatable
>> function. Not even pg_views shows anything. Is this intentional or an
>> oversight?
>
> Just by recalling the thread, an oversight. Having this information in ¥dv+
> would
> be indeed a nice addition.

Yes, agreed -- something like this would be nice. It's not just views
though -- foreign tables may now also be updatable, so I think it
should work for \d+ in general, not just \dv+.

Perhaps we should add a new column to \d+'s list of relations
(provided that doesn't make it too wide) and add an extra line at the
end of the \d+ description for a single relation. Should this also
distinguish between insertable, updatable and deletable (i.e., support
for INSERT, UPDATE and DELETE)?

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

which would work for all relation kinds, returning a bitmask
indicating which of the operations (INSERT, UPDATE and DELETE) are
supported, together with a matching function in the FDW API.

Thoughts?

Dean