Postgres Pro
Downloads
Home   >   mailing lists

Re: regclass and format('%I') - Mailing list pgsql-general

From David G. Johnston
Subject Re: regclass and format('%I')
Date
Msg-id CAKFQuwbvUf=BModagpFyPZYdq0Vn4L+1__j46YtosvizmywQWQ@mail.gmail.com
Whole thread Raw
In response to regclass and format('%I')  (Jason Dusek <jason.dusek@gmail.com>)
Responses Re: regclass and format('%I')
List pgsql-general
Tree view
On Fri, Mar 13, 2015 at 12:18 PM, Jason Dusek <jason.dusek@gmail.com> wrote:
Hi All,

The difference in how format handles `regclass` and `name` seems like an
inconsistency:

    WITH conversions(casts, format, result) AS (
    VALUES (ARRAY['name']::regtype[],             '%I', format('%I',
name('select'))),
           (ARRAY['name']::regtype[],             '%L', format('%L',
name('select'))),
           (ARRAY['name']::regtype[],             '%s', format('%s',
name('select'))),
           (ARRAY['regclass']::regtype[],         '%I', format('%I',
regclass('select'))),
           (ARRAY['regclass']::regtype[],         '%L', format('%L',
regclass('select'))),
           (ARRAY['regclass']::regtype[],         '%s', format('%s',
regclass('select'))),
           (ARRAY['regclass', 'name']::regtype[], '%I', format('%I',
name(regclass('select')))),
           (ARRAY['regclass', 'name']::regtype[], '%L', format('%L',
name(regclass('select')))),
           (ARRAY['regclass', 'name']::regtype[], '%s', format('%s',
name(regclass('select'))))
    ) SELECT * FROM conversions;
          casts      | format |    result
    -----------------+--------+--------------
     {name}          | %I     | "select"
     {name}          | %L     | 'select'
     {name}          | %s     | select
     {regclass}      | %I     | """select"""
     {regclass}      | %L     | '"select"'
     {regclass}      | %s     | "select"
     {regclass,name} | %I     | """select"""
     {regclass,name} | %L     | '"select"'
     {regclass,name} | %s     | "select"

My assumption is that they both represent valid SQL identifiers, so it stands
to reason that `%I` should result in a valid identifier for both of them (or
neither one).

​All three of the %I results are valid identifiers.

regclass performs the same conversion that %I performs.  But since the output of the regclass conversion is a valid identifier, with double-quotes, the %I adds another pair of double-quotes and doubles-up the existing pair thus leaving you with 6.

<select> is a reserved word and thus can only be used as an identifier if it is surrounded in double-quotes.  name() doesn't care (not that it is user-documented that I can find) about making its value usable as an identifier so when its output goes through %I you get the expected value.

​If you are going to use regclass you want to use %s to insert the result into your string; not %I​.

David J.

pgsql-general by date:

Previous
From: Steve Boyle
Date:
Subject: bdr replication latency monitoring
Next
From: Jason Dusek
Date:
Subject: Re: regclass and format('%I')