Обсуждение: postgres array quoting
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
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