PL/pgSQL regclass quoting of table named like keyword

Question

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)?

Erwin Brandstetter · Accepted Answer · 2018-04-15T12:24:41.420

Explanation

I'm going to step through the multiple layers of misunderstandings one by one - arriving at a simple, secure solution.

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.

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.

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).

format() is overkill for this. Just use quote_ident():

quote_ident(old_name || '_new')

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;

Aaah! That is one beautiful solution!! – amacvar Jun 22 '16 at 13:34 — amacvar, Jun 22 '16 at 13:34

score 1 · Answer 2 · answered Jun 17 '16 at 10:10

1

The only way I could think was to wrap the call to the format function in a translate as follows:

RETURN translate(format('%I_new',old_name),'"','');

This post processes the output of the format and strips the " characters out.

I don't know if anybody has a more elegant solution. I thought of btrim, rtrim etc...but it would need a call to each.

answered Jun 17 '16 at 10:10

smbennett1974

106
4

Thanks, this is of course a usable work-around, but I want to wait a little bit more before I accept this answer. Maybe someone else has a better solution that wouldn't involve find/replace on the format result. – tomka Jun 17 '16 at 15:13
Unless you pass it as a text, make_name('overlay'::text), and do a separate check for the table within the function, I think @smbennett1974 has the answer. – amacvar Jun 17 '16 at 19:37
@amacvar: No, no, no. This is backwards in multiple ways. I added detailed explanation in another answer. – Erwin Brandstetter Jun 20 '16 at 03:10

PL/pgSQL regclass quoting of table named like keyword

2 Answers2

Explanation

Solution

Test

Linked