Re: text formatting in a query, a la sprintf

[Michael Fuhr <mike@xxxxxxxx>]

On Sat, Aug 19, 2006 at 01:59:17PM -0700, Reece Hart wrote:
> I often desire text and number formatting that's not available with
> functions like to_char. I'd really like to have functionality akin to
> sprintf, such as:
> 
> $ select no_hits,term,sprintf('http://google.com?q=%
> s',replace(queryterm,' ','+')) as url from queries;
> $ select event,sprintf("%.2g",probability) as prob from event_probs;
> $ select sprintf('<td color="%s">%s</td>',color,content) as td_cell from
> cells;
> 
> I considered a plperl function to do this, but plperl can't take
> anyarray and that means that I'd have to resort to ugliness like coding
> for specific numbers of args or encoding args them within in a single
> string... ick!

Here's a trivial (and only minimally tested) PL/Ruby function:

CREATE FUNCTION sprintf(format text, args anyarray) RETURNS text AS $$
return format % args
$$ LANGUAGE plruby IMMUTABLE STRICT;

SELECT sprintf('<td color="%s">%s</td>', array['pink', 'elephants']::text[]);
             sprintf             
---------------------------------
 <td color="pink">elephants</td>
(1 row)

SELECT sprintf('pi=%.2f e=%.3f', array[pi(), exp(1)]);
     sprintf     
-----------------
 pi=3.14 e=2.718
(1 row)

-- 
Michael Fuhr