Re: postgres array quoting

On Thu, 31 Aug 2006, marc(at)softwarehackery(dot)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