Search Postgresql Archives

Re: regclass and format('%I')

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Fri, Mar 13, 2015 at 12:18 PM, Jason Dusek <jason.dusek@xxxxxxxxx> 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.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux