Обсуждение: 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