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