Re: printf-like format strings

[Marc Evans <Marc@xxxxxxxxxxxxxxxxxxx>]

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