Re: How to get Relation name from Oid ??

"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