Hello
attached WIP patch.
I implement only basic format's tags related to SQL: string, value,
literal, sql identifier. These tags are basic, but there are not any
break to implement any other formats or enhance a syntax. The mix with
to_char function is more complex then I expected - so I don't thinking
about it for now (there are more then one to_char function).
I don't found a nice mix for placeholders and positional placeholders
- so I propose a new special function "substitute" (in contrib) where
placeholders are positional. More - we check in function "format" if
all parameters are used - this check isn't related to positional
placeholders, this is reason for separate implementation too:
so some examples:
postgres=# select substitute('second parameter is "$2" and first
parameter is "$1"', 'first parameter', 'second parameter');
substitute
─────────────────────────────────────────────────────────────────────────────────
second parameter is "second parameter" and first parameter is "first parameter"
(1 row)
postgres=# select format('INSERT INTO %i (c1, c2, c3, c4) VALUES
(%v,%v,%v,%v)', 'my tab',1, NULL, true, 'hello');
format
────────────────────────────────────────────────────────────────
INSERT INTO "my tab" (c1, c2, c3, c4) VALUES (1,NULL,t,'hello')
(1 row)
postgres=# select format('SQL identifier %i cannot be a NULL', NULL);
ERROR: SQL identifier cannot be a NULL
postgres=# select format('NULL is %v or empty string "%s"', NULL, NULL);
format
─────────────────────────────────
NULL is NULL or empty string ""
(1 row)
%i ... sql identifier
%v ... sql value
%s ... string --- the most used tag I expect
%l ... literal
I hope so this system is clean, simple, readable and extensible
Regards
Pavel
2010/8/30 Pavel Stehule <pavel.steh...@gmail.com>:
> 2010/8/30 Alvaro Herrera <alvhe...@commandprompt.com>:
>> Excerpts from Pavel Stehule's message of lun ago 30 07:51:55 -0400 2010:
>>> 2010/8/30 Itagaki Takahiro <itagaki.takah...@gmail.com>:
>>> > On Mon, Aug 30, 2010 at 7:58 PM, Pavel Stehule <pavel.steh...@gmail.com>
>>> > wrote:
>>> >> propsals:
>>> >> * "format" function - uses same formatting as PL/pgSQL RAISE statement
>>> >> * "sprintf" function
>>> >>
>>> >> Now I propose a compromise - "format" function with only three tags:
>>> >> %s .. some string
>>> >> %i .. SQL identifier
>>> >> %l .. string literal
>>> >
>>> > These are just ideas:
>>> >
>>> > * Use $n, as like as PREPARE command.
>>> > It allows for us to swap arguments in any order.
>>> > SELECT format('$2 before $1', 'aaa', 'bbb')
>>>
>>> what is use case for this feature? I don't see it.
>>
>> Translations :-) I haven't had a use for that but I've heard people
>> implements gettext of sorts in database tables. Maybe that kind of
>> thing would be of use here.
>>
>>> > * Call to_char() functions for each placeholder.
>>> > For example,
>>> > format('=={YYYY-MM-DD}==', tm::timestamp)
>>> > is equivalent to
>>> > '==' || to_char(tm, 'YYYY-MM-DD') || '=='
>>> > '{}' prints the input with the default format.
>>> >
>>> > New languages' libraries might be of some help. LLs, C#, etc.
>>>
>>> I though about integration with to_char function too. There are not
>>> technical barrier. And I can live with just {to_char_format} too. It
>>> can be or cannot be mixed with basic tags together - there is
>>> specified a NULL value behave. If we allow {format} syntax, then we
>>> have to specify a escape syntax for { and }. Do you have a some idea?
>>
>> What about %{sth}? That way you don't need to escape {. The closing } would
>> need escaping only inside the %{} specifier, so {%{YYYY{\}MM}} prints
>> {2010{}08} So the above example is:
>
> then you need escaping too :)
>
>>
>> format('==%{YYYY-MM-DD}==', tm::timestamp);
>
> I am not sure if this is correct -but why not
>
> so there are possible combinations
>
> %s .. no quoting, NULL is ''
> %{} .. no quoting, NULL is NULL .. like output from to_char
> %{}s .. no quoting with formatting, NULL is ''
>
> now I have not idea about nice syntax for positional parameters - maybe
> %{...}$1s or we can use a two variants for tags - not positional '%'
> and positional '%', so
> $1{...}s, %{...}s, $1, %s, $1s, $1{...}, %{...} can be valid tags
>
> Regards
>
> Pavel Stehule
>
>>
>> Not sure about this to_char stuff though, seems too cute. You can do
>> the case above like this:
>>
>> format('==%s==', to_char(tm::timestamp, 'YYYY-MM-DD'))
>>
>
> I like an using a format like tag - there are not technical problem -
> format can be taken from string and data type parameter can be known
> too. But this feature can be some enhancing. The basic features are
> NULL handling and right quoting.
>
>
>
>> --
>> Álvaro Herrera <alvhe...@commandprompt.com>
>> The PostgreSQL Company - Command Prompt, Inc.
>> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>>
>
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
