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
Last activity: Apr 15, 2018, 12:24 PM