postgres array quoting

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

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

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/

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