How to pass int[] as argument between plpython functions

Question

I'm working with PostgreSQL and plpython functions.

I have a function func1:

CREATE OR REPLACE FUNCTION func1(a integer, b timestamp with time zone, c integer[])
  RETURNS SETOF x AS
$BODY$

parts=plpy.execute("SELECT * FROM func2(%s)"%c)

$BODY$
LANGUAGE plpythonu VOLATILE

func1 calls func2 with the c argument which is integer[].

CREATE OR REPLACE FUNCTION func2(v_c integer[])
RETURNS SETOF y AS
$BODY$
    .
    .
    .
$BODY$
LANGUAGE plpythonu VOLATILE

when running it from SQL query

select *
from func1(3,'14-Feb-2012','{-2,30747,30906}')

I get the following error:

ERROR:  spiexceptions.SyntaxError: syntax error at or near "["
LINE 1: SELECT * FROM func2([-2, 30747, 30906])

something with passing the intger[] isn't working i also tried :

plpy.execute("SELECT * FROM func2(%s)"%str(c))

it doesn't work as well.

Answer 1

Don't know if you solved this, the problem is plpy will not automatically format python to sql types, what I would do is format the call to func2:

Instead of:

parts = plpy.execute("SELECT * FROM func2(%s)"%c)

Try:

c = [-2, 30747, 30906]
c = '{' + ','.join(str(i) for i in c) + '}'
q = "SELECT * FROM func2('{}')".format(c)
plpy.notice(q)
parts = plpy.execute(q)

Answer 2

Probe this:

create or replace function func1(t text[]) returns integer as $$
    p = plpy.prepare("select func2($1) v", ["text[]",])
    r = plpy.execute(p, [t,]) 
    return r[0]["v"]
$$ language 'plpythonu';

create or replace function func2(t text[]) returns integer as $$
    return len(t)
$$ language 'plpythonu';

select func1('{"a", "b"}'::text[]);
> 2

I think the best way is pass the list (postgres array) as parameter in the execute. It is necessary to prepare statement first.