Re: string function - "format" function proposal

On Fri, Oct 15, 2010 at 5:42 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Fri, Oct 15, 2010 at 10:54 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> I think we should go into this with the idea that it might only do 10%
>>> of what sprintf can do initially, but there will be pressure to cover a
>>> lot of the other 90% eventually.
>
>> Hmm.  I have a feeling that's going to be a rathole.  Among other
>> problems, what about types other than strings and numbers?
>
> I think the general solution is to split those off as subproblems.
> If you've got a type that has special formatting requirements,
> you can do
>
> sprintf('foo is %s', format_my_type(value, other-arguments))
>
> where format_my_type returns text.  (So, in particular, you could use
> to_char for this if it solved the particular need.)
>
> Having said that, it might make sense to provide special case handling
> of dates and timestamps, since that's definitely the most common case
> where you might not be satisfied with the default conversion to text.
>
>> The specification for sprintf is
>> ridiculously complicated with just the things C has as built-in types,
>> never mind SQL.
>
> Sure, but an awful lot of those bells and whistles turn out to be handy.

No doubt. The problem is that we're going to end up with those bells
and whistles in two places: in to_char or other type-specific
formatting functions, and again in format.

> Personally I think the field width control options are the main thing
> that sprintf has got over to_char, so I think we're going to want those
> sooner rather than later.
>
>> Then again, if I'm not the one who has to spend time in the rathole...
>
> Yeah, I'm not in a hurry to spend time on it either.  I just foresee
> that somebody will want to, and so I don't want a dead-end definition.

Perhaps we could design a family of (heh, heh, undocumented) functions
called _format_helper(type, text), or something like that. format()
would call the format helper for the appropriate type with the datum
to be formatted and the sprintf-like format string as arguments. Or
maybe that isn't feasible, I'm just brainstorming.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company