I have a large table (audit) in which I store relation ID of the schema and table. This is conveniently populated by trigger using TG_RELID. It is also convenient because I can index it and the index would be smaller than if I were to store "schema"(text) and "table"(text) columns separately and index them. Also, I can then do queries using WHERE relation_id = 'myschema:mytable'::regclass and it just works. My question is if this is "safe" to do in terms of backup/restore (will the schema.table have the same regclass/oid in target server and it did in the source server? Any other issue I should be aware of?

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?

In writing a function to test if a column col_name exists in a table _tbl, I'd like to extract the table's schema name, which is passed into the function as a regclass parameter (for security??). CREATE OR REPLACE FUNCTION column_exists(_tbl regclass, col_name text) RETURNS bool AS $func$ SELECT EXISTS ( SELECT 1 FROM information_schema.columns WHERE table_schema=get_schema($1) AND table_name=get_table($1) AND column_name=$2 ); $func$ LANGUAGE sql; So if the table name is 'staging.my_table'::regclass, I'd like to get staging from an imaginary function get_schema. *Can I just implement this function with e.g. split_part(_tbl::text, '.', 1)?* *In addition, is it guaranteed that the table name _tbl, when converted to text, will always have a schema name? (i.e. not omitting things such as public.)* I'm not very familiar with the regclass type. I searched but couldn't find how to extract the schema name, and just wanted to ask first before re-inventing wheels.

I want to create a new table name based on an existing one by appending a suffix to it. A Postgres 9.5 PL/pgSQL function gets the existing table name passed in as regclass type and returns the new name as a string. I am using format() to construct the new name, and would typically use the %I placeholder. This works as long as the passed in table name doesn't match any PL/pgSQL keyword. In this case, no matter what type component I choose (%I or %s), the quoting is wrong. Consider the following function: CREATE OR REPLACE FUNCTION make_name(old_name regclass) RETURNS text LANGUAGE plpgsql AS $$ BEGIN RETURN format('%I_new', old_name); END; $$; Further assume that there are two tables: treenode and overlay. Calling this function for both results in the following new names: SELECT make_name('overlay'::regclass); make_name ------------------- """overlay"""_new (1 row) SELECT make_name('treenode'::regclass); make_name -------------- treenode_new (1 row) As it turns out overlay is also a PL/pgSQL function (just like format, but treenode is not), which seems to change quoting behavior. If %s is used with format(), the result would be "overlay"_new. The same happens when I use the || operator. If I use text as input parameter type, everything works as expected, but I would prefer using regclass. Is there a way to format a string with a keyword-matching regclass table name (e.g. overlay) without quotes, just like it is the case for a non-keyword matching regclass table name (e.g. treenode)?

I'm just wondering if there is an established method for testing if a string can be used as an *unquoted* PostgreSQL identifier? (unquoted because almost any string can be a quoted identifier). I ask because as shown in a previous question (https://dba.stackexchange.com/questions/200419) , there are times when I would need to specify an identifier (such as the name of a table to be created) that does not yet exist, as string values (text) instead of a safer type such as regclass. Quoting the string/name can be problematic as shown there and probably else where. Without quoting, it's susceptible to SQL injection. I guess if one programs it hard enough, a string parsing function can be written ultimately. Just wanted to check if there are existing solutions. Related: https://dba.stackexchange.com/questions/45589 https://dba.stackexchange.com/questions/196015 (sql-server)