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
)?
Best Answer
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
1.overlay
is quoted is not because it's a function name, but because it's a reserved word.Also,
overlay()
is not a "PL/pgSQL function" (nor PL/pgSQL keyword), it's a standard SQL function built into the Postgres core (LANGUAGE internal
, so C behind the curtains). In fact, PL/pgSQL has nothing to do with any of this.A simple logic error.
This would escape any non-standard identifier with surrounding double quotes before '_new' is appended and would fail even with
text
as input type. You have to append '_new' before quoting the whole of it:But that still won't work for
2.regclass
. Keep reading.It's nonsense to add quotes to a
3.regclass
variable with%I
, since its text representation is already quoted automatically where needed. (Similar toquote_ident()
, but cannot be NULL; since the input isregclass
it cannot be NULL to begin with.) You would apply it twice. Always use%s
forregclass
input (the string as is).
4.format()
is overkill for this. Just usequote_ident()
:Most importantly, as mentioned above, the
text
representation of aregclass
variable is automatically escaped. That's built into the cast. To get to the raw text, retrieve it from system catalogpg_class
directly. Aregclass
value is just theoid
of this table internally.Solution
This does what you are looking for:
Test
Note that
overlay_new
is a perfectly legal identifier so it's not quoted.If you want the name unescaped (no double quotes) just use: