Thread: How access table by tableoid

Hi,

can I use a given tableoid (instead of the tablename) to select
columns from that table somehow?

SELECT * FROM ??tableoid??

My first approach was to lauch a subquery of the information_schema
like this:

SELECT * FROM (SELECT relname FROM pg_class WHERE oid=999) AS
tablename

However, it turned out that the subquery returns the refered *column*
which is the 'relname' column but not the table's name. (With other
words: The final result is a single 'relname' column instead of all
columns from the tablename inside the relname column.) This is not was I
intended.

So, I worked around that by peforming two queries: The first to retrieve
the table's name from pg_class via its OID, the second to select the
wanted columns from that table using the table's name as usual.

Can I do it in one go using the table´s OID?

Thank You
Felix

On Sun, May 13, 2007 at 09:25:37PM +0200, Felix Kater wrote:
> can I use a given tableoid (instead of the tablename) to select
> columns from that table somehow?
>
> SELECT * FROM ??tableoid??
<snip>
> So, I worked around that by peforming two queries: The first to retrieve
> the table's name from pg_class via its OID, the second to select the
> wanted columns from that table using the table's name as usual.
>
> Can I do it in one go using the table??s OID?

You could possibly do this with a SRF; you'd have to grab the name of
the table and then build a dynamic query off of that. If you cast the
oid to regclass you'll get the name of the table, though you should
probably query pg_class and pg_namespace to build a fully-qualified
table name (schemaname.tablename).
--
Jim Nasby                                      decibel@decibel.org
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

"Jim C. Nasby" <decibel@decibel.org> writes:
> You could possibly do this with a SRF; you'd have to grab the name of
> the table and then build a dynamic query off of that. If you cast the
> oid to regclass you'll get the name of the table, though you should
> probably query pg_class and pg_namespace to build a fully-qualified
> table name (schemaname.tablename).

The regclass cast will take care of that for you.

            regards, tom lane