Thread: text formatting in a query, a la sprintf

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!

Is there a way to get more flexible formatting as in these examples within a backend query, even if not with sprintf per se?

Thanks,
Reece

-- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

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

On Sun, Aug 20, 2006 at 08:21:18PM -0700, Reece Hart wrote:
> On Sat, 2006-08-19 at 16:50 -0600, Michael Fuhr wrote:
> > Here's a trivial (and only minimally tested) PL/Ruby function:
>
> Thanks, Michael.  That's a solution I can use.  Now I just have to
> compile postgresql for ruby support...

You don't have to recompile PostgreSQL; PL/Ruby isn't included with
the source code as PL/Perl, PL/Python, and PL/Tcl are.  You can get
it from here:

http://raa.ruby-lang.org/project/pl-ruby

Unpack the tarball and see README.en for instructions.

--
Michael Fuhr