Re: postgres array quoting - Mailing list pgsql-general

On Thu, 31 Aug 2006, marc@softwarehackery.com wrote:

> Hello -
>
> I am attempting to find a way to make use of arrays of text, as demonstraited
> by the following:
>
> CREATE TABLE messages (
>  id SERIAL,
>  format TEXT NOT NULL,
>  arguments TEXT[]
> );
>
> Into that table will be values that you would associate with some form of
> printf. For example:
>
> INSERT INTO messages (format,arguments)
>  VALUES ('hello %s','{"world"}');
> INSERT INTO messages (format,arguments)
>  VALUES ('test 2 %s %s','{"one","two"}');
> INSERT INTO messages (format,arguments)
>  VALUES ('test 3 %s','{"abc","d,e,f","g,\\"h,i\'"}');
>
> A select shows the following:
>
> id |    format    |        arguments
> ----+--------------+--------------------------
>  1 | hello %s     | {world}
>  2 | test 2 %s %s | {one,two}
>  3 | test 3 %s    | {abc,"d,e,f","g,\"h,i'"}
>
> The goal of this table is to extract each row and pass it to some variation
> of printf. My problem is that I can't figure out a good way to do this, and
> am hoping that someone has already found a way.
>
> * If I could figure a way to pass a variable number of arguments to a
>   plperl function, or an array reference, I could use something like this
>   function:
>
> CREATE OR REPLACE FUNCTION audit_log_format(TEXT,...) RETURNS TEXT AS $$
>  return sprintf shift,@_;
> $$ LANGUAGE plperl;
>
> * If I could figure out a way to force select to always apply
>   escaping/quoting logic to each of the elements in the arguments array,
>   then I could probably find a way to do this. This split is non-trivial,
>   but doable.
>
> * If I could perform a select within a plperl function and receive back
>   a perl array for the arguments column, I could use a plperl function
>   kinda like the one above, except taking an ID value as the argument.
>
> Given the number of ways that things could be escaped in text stored in the
> arguments array, such as embedded quotes, commas, back slashes, etc,
> compounded by the lack of quotes in same cases but not others, parsing the
> output of a select is difficult at best. I suspect that it is sadly my only
> option.
>
> Suggestions?

Thanks to all that had feedback. So that the archives contain a functional
(though very ugly) solution, here is what I have found can be made to
work. The key to it is that it retrieves each element of the array one at
a time, building a perl array which it then hands to the sprintf function.

-- The first argument is the format string to hand sprintf. This is
-- seperate to allow easier I18N, e.g. the caller hands this function a
-- localized string.
-- The second argument is the ID of the record to be formatted.
CREATE OR REPLACE FUNCTION 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 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 logs WHERE id = $id",1);
     push(@args,$msg->{rows}[0]->{msg_args});
     $i++;
   }
   return sprintf $fmt,@args;
$$ LANGUAGE plperl;

- Marc