Thread: printf-like format strings
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
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