Thread: postgres array quoting

postgres array quoting

From

marc@softwarehackery.com

Date:

31 August 2006, 18:40:18

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?

- Marc

Re: postgres array quoting

From

Alvaro Herrera

Date:

31 August 2006, 19:19:27

marc@softwarehackery.com wrote:
> Hello -
>
> I am attempting to find a way to make use of arrays of text, as
> demonstraited by the following:

Interesting problem.  Apparently plperl is not cool about parsing arrays
in the arguments to Perl arrays -- at least I couldn't make it work, and
I don't find any mention in my (admittedly a bit outdated) local copy of
the docs.

I remember we did parse arrays in PL/php to native PHP arrays with which
you could do this, but that code was more than a bit buggy, so I'm wary
of recommending it.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Re: postgres array quoting

From

Tom Lane

Date:

31 August 2006, 19:24:32

marc@softwarehackery.com writes:
>   * 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:

Doesn't plperl already contain a feature to convert a Postgres array
into a Perl array?  It may be documented poorly or not at all, but I
definitely see code in there for going the other direction, and one
would think that whoever coded it would have catered for both
conversions.

            regards, tom lane

Re: postgres array quoting

From

"Joshua D. Drake"

Date:

31 August 2006, 20:03:12

Alvaro Herrera wrote:
> marc@softwarehackery.com wrote:
>> Hello -
>>
>> I am attempting to find a way to make use of arrays of text, as
>> demonstraited by the following:
>
> Interesting problem.  Apparently plperl is not cool about parsing arrays
> in the arguments to Perl arrays -- at least I couldn't make it work, and
> I don't find any mention in my (admittedly a bit outdated) local copy of
> the docs.
>
> I remember we did parse arrays in PL/php to native PHP arrays with which
> you could do this, but that code was more than a bit buggy, so I'm wary
> of recommending it.
>

Yeah I would second that... our array stuff needs some work (after 8.2).

Sincerely,

Joshua D. Drake




--

    === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
    Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/

Re: postgres array quoting

From

Marc Evans

Date:

01 September 2006, 12:40:04

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