How to get the OID of a view

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

How to get the OID of a view

stan-9
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.


--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                                                -- Benjamin Franklin


Reply | Threaded
Open this post in threaded view
|

Re: How to get the OID of a view

Tom Lane-2
stan <[hidden email]> writes:
> 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.

Yeah, pg_tables is just a user-friendly view, it's not really that
useful for programmatic work.  I'd look directly at pg_class and
pg_attribute for this problem.

A good way to learn what to do is to see what psql does for its
various \d commands --- if you start it with the -E option you'll
see the underlying SQL it issues.  It'll likely be more complicated
than you want, but you can strip away what's not useful for you.

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: How to get the OID of a view

Charles Clavadetscher
In reply to this post by stan-9
Hello
--------------

On 22.05.2020, at 18:15, stan <[hidden email]> 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.


--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                       -- Benjamin Franklin



You find the oid of the views in pg_catalog.pg_class (relkind 'v').

Regards
Charles
Reply | Threaded
Open this post in threaded view
|

Re: How to get the OID of a view

Adrian Klaver-4
In reply to this post by stan-9
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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: How to get the OID of a view

David G Johnston
In reply to this post by stan-9
On Fri, May 22, 2020 at 9:15 AM stan <[hidden email]> 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.


The table you want is pg_class:


If the system views listed in [1] don't provide you what you need you need to fall-back to the system tables listed in [2].


David J.