printf-like format strings

On Monday 22 January 2007 12:59 pm, Alexander Presber <aljoscha@weisshuhn.de>
thus communicated:

Hello,

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?

Thanks for any advice!

Sincerely
Alexander Presber

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

If I understand the question correctly, couldn't you use pop @_ in the Perl
function?
--
Terry Lee Tucker
Turbo's IT Manager
Turbo, division of Ozburn-Hessey Logistics
2251 Jesse Jewell Pkwy NE
Gainesville, GA 30501
Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 363-4719
terry@turbocorp.com
www.turbocorp.com

-----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@turnstep.com
PGP Key: 0x14964AC8 200701221423
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

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

I have found the following technique works well for me:

CREATE OR REPLACE FUNCTION audit_log_sprintf(text,integer) RETURNS TEXT as $$
my $fmt = shift;
my $id = shift;
my $msg = spi_exec_query("SELECT array_upper(msg_args,1) FROM audit_logs WHERE id = $id",1);
my $nArgs = $msg->{rows}[0]->{array_upper};
my $i = 1;
my @args;
while ($i <= $nArgs) {
$msg = spi_exec_query("SELECT msg_args[$i] FROM audit_logs WHERE id = $id",1);
push(@args,$msg->{rows}[0]->{msg_args});
$i++;
}
return sprintf $fmt,@args;
$$ LANGUAGE plperl;

The audit_logs table contains at least these columns:

audit_format_id BIGINT NOT NULL,
msg_args TEXT[],

The audit_format_id is a reference into an audit_formats_table of sprintf
format strings. You could easily simplify this to remove that indirection,
if desired.

- Marc