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?
Best Answer
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 thesearch_path
by default.The current
search_path
is behind this. The text representation of aregclass
value builds on it. The table name is only schema-qualified if the schema is not the first match in thesearch_path
anyway. The manual:You could force Postgres to print the schema name by setting an empty
search_path
(locally):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
andpg_temp
are special. See:Or you avoid the special cast and the
search_path
and get the schema name frompg_class
directly: