Thread: casting behavior of oids and relation names
Hello guys,
In some cases when I cast the oid to relation names ('xxxx'::regclass::text) I get schemaname.tablename and in some cases I just get tablename. I thought at the beginning, this is due name duplication of tables in different schemas but it seems not. Also, this seems as a schema option because it happen only in certain schemas. How I can get a consistent names of the casting ?
Thanks in advance
salah jubeh <s_jubeh@yahoo.com> writes: > In some cases when I cast the oid to relation names ('xxxx'::regclass::text) �I get �schemaname.tablename and in some casesI just get tablename. I thought at the�beginning,�this is due name duplication of tables in different�schemas�but itseems not. �Also, this seems as a schema option because it happen only in certain schemas. �How I can get a consistentnames of the casting ?� That's a feature, not a bug --- it doesn't qualify the name if qualification is unnecessary given your current search_path. For user tables, you could force it to always qualify by setting search_path to empty, I think. regards, tom lane
On May 16, 2012, at 9:20 AM, salah jubeh wrote: > > In some cases when I cast the oid to relation names ('xxxx'::regclass::text) I get schemaname.tablename and in some casesI just get tablename. I thought at the beginning, this is due name duplication of tables in different schemas but itseems not. Also, this seems as a schema option because it happen only in certain schemas. How I can get a consistentnames of the casting ? > If tablename exists in search_path then it will not show the schemaname. Its normal behavior. You can create function something like given below for consistent names and can use it: CREATE OR REPLACE FUNCTION pg_get_relname(oid) RETURNS text as $$ SELECT n.nspname||'.'|| c.relname as "Name" FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind='r' and c.oid=$1 $$ language sql; Thanks & Regards, Vibhor Kumar EnterpriseDB Corporation The Enterprise PostgreSQL Company Blog: http://vibhork.blogspot.com