4

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

6

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;
  • 1
    Aaah! That is one beautiful solution!! – amacvar Jun 22 '16 at 13:34
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.

  • 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 3:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.