Skip site navigation (1) Skip section navigation (2)

Re: string function - "format" function proposal

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
Cc: Itagaki Takahiro <itagaki(dot)takahiro(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: string function - "format" function proposal
Date: 2010-08-30 16:08:54
Message-ID: AANLkTikQ8RSBPTqAfW+1DWASVLsJjkXLKDh4zVMD3bN2@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
2010/8/30 Alvaro Herrera <alvherre(at)commandprompt(dot)com>:
> Excerpts from Pavel Stehule's message of lun ago 30 07:51:55 -0400 2010:
>> 2010/8/30 Itagaki Takahiro <itagaki(dot)takahiro(at)gmail(dot)com>:
>> > On Mon, Aug 30, 2010 at 7:58 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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 <alvherre(at)commandprompt(dot)com>
> The PostgreSQL Company - Command Prompt, Inc.
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>

In response to

Responses

pgsql-hackers by date

Next:From: Pavel StehuleDate: 2010-08-30 16:21:00
Subject: thousand unrelated data files in pg_default tablespace
Previous:From: Markus WannerDate: 2010-08-30 15:30:59
Subject: Re: bg worker: patch 1 of 6 - permanent process

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group