Re: regclass and format('%I')

2015-03-14 10:09 GMT+01:00 Jason Dusek <jason(dot)dusek(at)gmail(dot)com>:

> It honestly seems far more reasonable to me that %s and %I should do
> the exact same thing with regclass. My reasoning is as follows:
>
> ‘%I’ formats a something such that it is a valid identifier,
>
> regclass is already a valid identifier,
>
> therefore, do nothing.
>
> Another line of reasoning:
>
> If you format with ‘%s’ you are saying: I don’t care whether it’s a
> valid identifier or literal or whatever, just put the string there,
>
> but when we sub a regclass into a string, we want it to be a valid
> identifier,
>
> therefore we should write ‘%I’ when subbing it, so as not to confuse
> our readers,
>
> therefore ‘%I’ should do nothing.
>

yes, it is true, when you use a safe type: regclass, regtype, you should
not to use %I due double quoting.

postgres=# select 16398::regclass ;
-[ RECORD 1 ]-------
regclass | "omega a"

postgres=# select format('>>%I<<<', 16398::regclass );
-[ RECORD 1 ]--------------
format | >>"""omega a"""<<<

Should be fixed

Regards

Pavel

Regards

Pavel

> On 13 March 2015 at 12:42, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
> wrote:
> > On Fri, Mar 13, 2015 at 12:18 PM, Jason Dusek <jason(dot)dusek(at)gmail(dot)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.
> >
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>