Thread: How to get Relation name from Oid ??

Hello ,

Can somebody please tell me how to get the name of the
relation (Attribute also) from the Oid and the
otherway back (Oid from name) ??

thanks
-Ramu

=====
" Karyathuranaam na Sukham na Nidhra "

www.it.iitb.ac.in/~ramu

________________________________________________________________________
Yahoo! India Insurance Special: Be informed on the best policies, services, tools and more. 
Go to: http://in.insurance.yahoo.com/licspecial/index.html

On Friday 27 February 2004 12:01 pm, Halasipuram seshadri ramanujam wrote:
> Hello ,
>
> Can somebody please tell me how to get the name of the
> relation (Attribute also) from the Oid and the
> otherway back (Oid from name) ??
>

There is a document on the system tables in the PostgreSQL documentation.

http://www.postgresql.org/docs/7.4/static/catalogs.html

pg_class is the relation you are looking for.

-- 
Jonathan Gardner
jgardner@jonathangardner.net

"Jonathan Gardner" <jgardner@jonathangardner.net> wrote in message
news:200403011039.18227.jgardner@jonathangardner.net...
> On Friday 27 February 2004 12:01 pm, Halasipuram seshadri ramanujam wrote:
> > Hello ,
> >
> > Can somebody please tell me how to get the name of the
> > relation (Attribute also) from the Oid and the
> > otherway back (Oid from name) ??
> >
>
> There is a document on the system tables in the PostgreSQL documentation.
>
> http://www.postgresql.org/docs/7.4/static/catalogs.html
>
> pg_class is the relation you are looking for.
>
> -- 
> Jonathan Gardner
> jgardner@jonathangardner.net
>

You can also use 'path.totable'::regclass::oid to find the oid of a table,
and 123456::regclass to find the path of a table given an oid. There is no
similar functionality for attributes AFAIK.

If you need to use the path returned from the regclass cast as text, you
will need to create a cast from regclass to text - this can be achieved
using the following functions - making use of the cstring type that the
return/input functions for these types have in common. I'd not sure how safe
an approach this is - and would appreciate any comments.

CREATE OR REPLACE FUNCTION utilities.text(regclass) RETURNS text STRICT
STABLE AS '  SELECT pg_catalog.textin(pg_catalog.regclassout($1::regclass));'
LANGUAGE 'SQL';

CREATE OR REPLACE FUNCTION utilities.regclass(text) RETURNS regclass STRICT
STABLE AS '  SELECT pg_catalog.regclassin(pg_catalog.textout($1::text));'
LANGUAGE 'SQL';

CREATE CAST (regclass AS text) WITH FUNCTION utilities.text(regclass);
CREATE CAST (text AS regclass) WITH FUNCTION utilities.regclass(text);

Once you have created these functions/casts (here in the utilities schema)
you can use 3245342::oid::regclass::text to find the path of a table given
it's oid.This does take into account the current schema_path settings, so
use of this cast may or may not schema-qualify the table name depending on
the schema_path setting.

-- 
Tom Hebbron
www.hebbron.com

"Tom Hebbron" <news_user@hebbron.com> writes:
> If you need to use the path returned from the regclass cast as text, you
> will need to create a cast from regclass to text - this can be achieved
> using the following functions - making use of the cstring type that the
> return/input functions for these types have in common. I'd not sure how safe
> an approach this is - and would appreciate any comments.

This works, and is safe in versions where cstring is a full-fledged type
(I forget whether that was in 7.3 or 7.4).

But it might be notationally cleaner to use plpgsql.  plpgsql's idea of
type coercion is to do exactly this output-to-string-and-input-again
trick, so the functionality would be the same, but you'd only need to
write

CREATE OR REPLACE FUNCTION utilities.text(regclass) RETURNS text STRICT
STABLE AS 'begin; return $1; end' LANGUAGE plpgsql;

I am not sure about speed considerations.  Pre-7.4 the SQL function
method would certainly be slower, but as of 7.4 you can probably inline
the SQL function and it might come out ahead.
        regards, tom lane