5

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

Improve this question

2 Answers 2

Reset to default
8

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;
Improve this answer
1
  • 1
    Aaah! That is one beautiful solution!! Commented Jun 22, 2016 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.

Improve this answer
3
  • 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. Commented Jun 17, 2016 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. Commented Jun 17, 2016 at 19:37
  • @amacvar: No, no, no. This is backwards in multiple ways. I added detailed explanation in another answer. Commented Jun 20, 2016 at 3:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.