From: | Michael Fuhr <mike(at)fuhr(dot)org> |
---|---|
To: | Reece Hart <reece(at)harts(dot)net> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: text formatting in a query, a la sprintf |
Date: | 2006-08-19 22:50:08 |
Message-ID: | 20060819225008.GA2992@winnie.fuhr.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Fabio Victora Hecht | 2006-08-20 00:56:41 | Re: count and limit |
Previous Message | Guy Rouillier | 2006-08-19 22:38:19 | Re: Cast null string '' to integer 0 |