2015-03-14 10:09 GMT+01:00 Jason Dusek <jason.du...@gmail.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.g.johns...@gmail.com> > wrote: > > On Fri, Mar 13, 2015 at 12:18 PM, Jason Dusek <jason.du...@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. > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >