PLPGSQL returned array, how to parse in Python?

Question

I'm getting an array from plpgsql that looks like this:

[0:2]={
"(ab023500-ceef-41d6-af33-635964dbffde,Koen,\"\",Schmeets,{koen@heizoo.com},31631205427},\"{\"\"Test categorie\"\"}\",{ff0000})",
"(384cb1e9-58b9-4bdf-9da6-eb3d9355471b,Marc,\"\",Vrijhof,{},{},\"{\"\"Test categorie\"\"}\",{ff0000})",
"(9c19ec5c-6b95-456a-af6f-c3388835b780,Michael,\"\",\"Baas ;)\",{},{},    \"{\"\"Subcategorie test\"\",\"\"Test categorie\"\"}\",\"{NULL,ff0000}\")"}

I have built my own interpreter to get the array in a list of Python, but it doesn't seem to be fail-proof. Does anybody have a clue how to parse this array in Python?

Edit

unnest() made it possible for me to get a nice array with the following function:

CREATE FUNCTION array_to_json_string(in_varchararray character varying[])
  RETURNS character varying LANGUAGE plpythonu AS
$_$
import cjson
   
plan = plpy.prepare("SELECT * FROM unnest($1)", ["varchar[]"])
rv = plpy.execute(plan, [in_varchararray])

retList = []
for r in rv:
    retList .append(r["unnest"])
return cjson.encode(retList)
$_$;

Although... it is pretty slow!

Does anybody know how to turn the plpgsql array into comma separated values?

score 0 · Answer 1 · answered Dec 13 '10 at 18:33

0

Consider unnesting the array before it gets to python, either using unnest in 8.4 or roll your own with:

create or replace function unnest(anyarray) returns setof anyelement as $$
  select $1[i] from generate_series(array_lower($1,1), array_upper($1,1)) i;
$$ language'sql' immutable;

answered Dec 13 '10 at 18:33

Thanks! i got it working with the following function CREATE FUNCTION array_to_json_string(in_varchararray character varying[]) RETURNS character varying LANGUAGE plpythonu AS $_$import cjson plan = plpy.prepare("SELECT * FROM unnest($1)", ["varchar[]"]) rv = plpy.execute(plan, [in_varchararray]) retList = [] for r in rv: retList .append(r["unnest"]) return cjson.encode(retList)$_$; Althoug it is pretty slow! the unnest turns it into rows, but is there a way to make it comma seperated or something? Thanks again! – Koen Dec 13 '10 at 18:52
You want to concatenate the rows into one long string? You could use a custom aggregate `create aggregate concat(basetype = text, sfunc = textcat, stype = text, initcond = '');` but I'm not sure that is really what you are asking for, is it? – Dec 13 '10 at 19:03
Hmmz now i am thinking, it is probably unsafe to have a seperator, is there maybe a way to pass results into json ? – Koen Dec 13 '10 at 19:31

score 0 · Answer 2 · answered Mar 01 '12 at 01:32

Use array_to_string() to convert an array into a comma separated string.
Like this:

SELECT array_to_string(
    $$[0:2]={
    "(ab023500-ceef-41d6-af33-635964dbffde,Koen,\"\",Schmeets,{koen@heizoo.com},{31631205427},\"{\"\"Test categorie\"\"}\",{ff0000})",
    "(384cb1e9-58b9-4bdf-9da6-eb3d9355471b,Marc,\"\",Vrijhof,{},{},\"{\"\"Test categorie\"\"}\",{ff0000})",
    "(9c19ec5c-6b95-456a-af6f-c3388835b780,Michael,\"\",\"Baas ;)\",{},{},\"{\"\"Subcategorie test\"\",\"\"Test categorie\"\"}\",\"{NULL,ff0000}\")"
    }$$::text[]
    , ',');

PLPGSQL returned array, how to parse in Python?

Edit

2 Answers2