Sample Header Ad - 728x90

PL/pgSQL regclass quoting of table named like keyword

5 votes
2 answers
3357 views
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)?
Asked by tomka (967 rep)
Jun 13, 2016, 03:56 PM
Last activity: Apr 15, 2018, 12:24 PM