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 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.
1.
A simple logic error.
format('%I_new', old_name)
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:
format('%I', old_name || '_new')
But that still won't work for regclass
. Keep reading.
2.
It's nonsense to add quotes to a regclass
variable with %I
, since its text representation is already quoted automatically where needed. (Similar to quote_ident()
, but cannot be NULL; since the input is regclass
it cannot be NULL to begin with.) You would apply it twice. Always use %s
for regclass
input (the string as is).
3.
format()
is overkill for this. Just use quote_ident()
:
quote_ident(old_name || '_new')
4.
Most importantly, as mentioned above, the text
representation of a regclass
variable is automatically escaped. That's built into the cast. To get to the raw text, retrieve it from system catalog pg_class
directly. A regclass
value is just the oid
of this table internally.
SELECT relname FROM pg_class WHERE oid = $1;
Solution
This does what you are looking for:
CREATE OR REPLACE FUNCTION make_name(old_name regclass)
RETURNS text AS
$func$
SELECT quote_ident(relname || '_new') FROM pg_class WHERE oid = $1;
$func$ LANGUAGE sql STABLE;
Test
CREATE TEMP TABLE "sUICiDAL' namE"();
CREATE TEMP TABLE overlay();
SELECT make_name('overlay') AS n1
, make_name('"sUICiDAL'' namE"') AS n2;
n1 | n2
-------------+----------------------
overlay_new | "sUICiDAL' namE_new"
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:
SELECT relname || '_new' FROM pg_class WHERE oid = $1;
Best Answer
I'm not sure that would be safe. Instead, I would use,
That is guaranteed to work.
So you could do something like,
Update
If you have a fully-qualified identifier, one with the table name, and schema name inside, You can use
parse_ident
to parse it safely.