Re: printf-like format strings

["Greg Sabino Mullane" <greg@xxxxxxxxxxxx>]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
NotDashEscaped: You need GnuPG to verify this message


Alexander Presber asked:

> does somebody know of an extension for postgres that allows the use
> of printf-like format strings?
> PL/Perl comes to mind, but how could one take care of the variable
> argument count?

You could put all the args into one string, with a delimeter either
explicitly specified or defaulted to whitespace:

CREATE OR REPLACE FUNCTION sprintf(text,text,text)
RETURNS TEXT LANGUAGE plperl AS
$_$
  my ($string,$args,$delim) = @_;
  my $delsplit = defined $delim ? qr{\Q$delim} : qr{\s+};
  return sprintf($string, (split $delsplit, $args));
$_$;

CREATE OR REPLACE FUNCTION sprintf(text,text)
RETURNS TEXT LANGUAGE sql AS
$_$
  SELECT sprintf($1,$2,null);
$_$;

SELECT sprintf('Best language? %s Best database? %s True answer? %d', 'Perl Postgres 42', null);

SELECT sprintf('Total grams: %3.3f Donuts: %s', '101.319472|chocolate and boston cream', '|');

SELECT sprintf('Arguments: %d', (SELECT pow(2,4)));

Output:

                           sprintf
-------------------------------------------------------------
 Best language? Perl Best database? Postgres True answer? 42
(1 row)

                         sprintf
---------------------------------------------------------
 Total grams: 101.319 Donuts: chocolate and boston cream
(1 row)

    sprintf
---------------
 Arguments: 16
(1 row)


--
Greg Sabino Mullane greg@xxxxxxxxxxxx
PGP Key: 0x14964AC8 200701221423
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFFtQ/VvJuQZxSWSsgRAv0/AJ9FKwjNP9JL/dgh8xZ2yUHphcEx8ACfRlbh
OutSP+1F8F8HtgFDyzk4OJE=
=oo1t
-----END PGP SIGNATURE-----