Re: How to get the OID of a view

On 5/22/20 9:15 AM, stan wrote:
> I am trying to write a query to return the names, and data types of all the
> columns in a view. It has been pointed out to me that the best approach
> would be using pg_catalog. OK, so I found pg_view, which I can get the names
> of a the views from and pg_attribute which can give me the column names,
> but it looks like i need to join this on OID, and pg_table does not have
> that data.
> 
> 

I'm guessing you mean pg_tables.

In any case, go straight to the source pg_class:

\dv
               List of relations
  Schema |       Name       | Type |  Owner
--------+------------------+------+----------
  public | tag_litem        | view | postgres
  public | tag_short_status | view | postgres

select oid, relname, relkind from pg_class where relname = 'tag_litem';
   oid  |  relname  | relkind
-------+-----------+---------
  60558 | tag_litem | v

Where relkind = 'v' means view:

https://www.postgresql.org/docs/12/catalog-pg-class.html

-- 
Adrian Klaver
adrian.klaver@aklaver.com