From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Jason Dusek <jason(dot)dusek(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-13 19:42:54 |
Message-ID: | CAKFQuwbvUf=BModagpFyPZYdq0Vn4L+1__j46YtosvizmywQWQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 | Steven Lembark | 2015-03-13 20:03:38 | Re: Basic Question on Point In Time Recovery |
Previous Message | Jason Dusek | 2015-03-13 19:18:53 | regclass and format('%I') |