Skip site navigation (1) Skip section navigation (2)

postgres array quoting

From: marc(at)softwarehackery(dot)com
To: pgsql-general(at)postgresql(dot)org
Subject: postgres array quoting
Date: 2006-08-31 18:19:40
Message-ID: 20060831135607.V22986@me.softwarehackery.com (view raw or flat)
Thread:
Lists: pgsql-general
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

Responses

pgsql-general by date

Next:From: Roman NeuhauserDate: 2006-08-31 18:22:46
Subject: Re: UUID as primary key
Previous:From: Joshua D. DrakeDate: 2006-08-31 18:18:27
Subject: Re: Thought provoking piece on NetBSD

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group