From: | Jason Dusek <jason(dot)dusek(at)gmail(dot)com> |
---|---|
To: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: regclass and format('%I') |
Date: | 2015-03-14 09:09:45 |
Message-ID: | CAO3NbwMPVF6zB5o1DVq2mL02cBmR6LPVfYwrRywLqU-d3LQ7mA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
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.
>
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2015-03-14 09:56:11 | Re: regclass and format('%I') |
Previous Message | Josh Berkus | 2015-03-13 23:29:40 | Re: Regarding pg_stat_statements |