1

I am writing a script in which I need to parse the name of a table (in regclass). The parsing (with parse_ident()) works so far. However, the script fails when the table is in the public schema because PostgreSQL (10.3) automatically removes the schema name.

For example, if a table tt is in a non-public schema ex, the text value of the regclass is the same as the original:

=> select 'ex.tt'::regclass::text;
 text  
-------
 ex.tt

When it's in public, the schema name is lost:

=> select 'public.tt'::regclass::text;
 text 
------
 tt

Is there an way to disable this behavior, or to convert to text without losing the schema name?

Erwin Brandstetter
  • 185,527
  • 28
  • 463
  • 633
tinlyx
  • 3,810
  • 14
  • 50
  • 79

1 Answers1

3

This is not related to the schema public per se - which is just another schema with no special powers except that it's created by default - and included in the search_path by default.

The current search_path is behind this. The text representation of a regclass value builds on it. The table name is only schema-qualified if the schema is not the first match in the search_path anyway. The manual:

The regclass input converter handles the table lookup according to the schema path setting, and so it does the “right thing” automatically. Similarly, casting a table's OID to regclass is handy for symbolic display of a numeric OID.

You could force Postgres to print the schema name by setting an empty search_path (locally):

SET LOCAL search_path = '';
SELECT 'public.tt'::regclass::text;

But that forces you to schema-qualify everything for the rest of the transaction or until you set the search_path again. Well, most everything, pg_catalog and pg_temp are special. See:

Or you avoid the special cast and the search_path and get the schema name from pg_class directly:

SELECT relnamespace::regnamespace::text
FROM   pg_catalog.pg_class
WHERE  oid = 'public.tt'::regclass;
Erwin Brandstetter
  • 185,527
  • 28
  • 463
  • 633