Skip to main content
14 votes

How to get the schema name of a table of type regclass in PostgreSQL?

To further simplify, you could use a cast to regnamespace - another object identifier type introduced with Postgres 9.5 SELECT relnamespace::regnamespace::text FROM pg_catalog.pg_class WHERE oid = ...
Erwin Brandstetter's user avatar
11 votes
Accepted

How to get the schema name of a table of type regclass in PostgreSQL?

Can I just implement this function with e.g. split_part(_tbl::text, '.', 1)? I'm not sure that would be safe. Instead, I would use, SELECT nspname FROM pg_catalog.pg_class AS c JOIN pg_catalog....
Evan Carroll's user avatar
  • 63.7k
8 votes
Accepted

PL/pgSQL regclass quoting of table named like keyword

Explanation I'm going to step through the multiple layers of misunderstandings one by one - arriving at a simple, secure solution. 0. The reason why overlay is quoted is not because it's a function ...
Erwin Brandstetter's user avatar
5 votes
Accepted

PostgreSQL: Do regclass/oid columns persist across backups?

I wouldn't rely on the specific values of OIDs to be portable at all. Even if you try it and you find it to work with the current version of PostgreSQL, it might not do so in newer releases. If you ...
joanolo's user avatar
  • 13.5k
3 votes
Accepted

Missing "public" schema when converting a "regclass" value to text

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 ...
Erwin Brandstetter's user avatar
2 votes

PostgreSQL: Do regclass/oid columns persist across backups?

Whether or not a OID persists across backups depends on whether you use a “raw” oid type or one of its subcatalog-specific aliases. The reason has everything to do with the fact that when you cast oid ...
BigSmoke's user avatar
  • 684
2 votes

PostgreSQL: Do regclass/oid columns persist across backups?

The contrib module pg_stat_statements uses OIDs to fingerprint queries. So, the stability guarantees you'll get by doing this yourself are essentially the same as the documented guarantees for that ...
peterg's user avatar
  • 21
1 vote

PL/pgSQL regclass quoting of table named like keyword

The only way I could think was to wrap the call to the format function in a translate as follows: RETURN translate(format('%I_new',old_name),'"',''); This post processes the output of the format and ...
smbennett1974's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible