PostgreSQL how to append multiple results of execute query?

Question

I have a function getitems(id) which gives all rows that are relevant for this id.

I have a function in PostgreSQL named func1 which should return getitems over whole list of items:

CREATE OR REPLACE FUNCTION func1(listof_id integer[])
  RETURNS SETOF newtype AS
$BODY$  

for item in listof_id:
    x=plpy.execute("SELECT * FROM getitems(%s)"%item)

return x;
$BODY$
  LANGUAGE plpythonu VOLATILE
  COST 100;

curently it returns x which contains the values of the last iteration (the getitems result of the last id in listof_id). How do I modify it so it will append each iteration to the last?

i tried to do :

x={}
for item in listof_id:
    x+=plpy.execute("SELECT * FROM getitems(%s)"%item)

and it doesn't work...

How big is listof_id ? You can modify/create a new getitems to accept a list of IDs. — m c
– m c, Commented Jun 24, 2015 at 14:39
unknown. it can have 2 ids or 10000 ids. Modifying existing function is a last resort case... I would prefer to avoid it. — John
– John, Commented Jun 24, 2015 at 15:01

Clodoaldo Neto · Accepted Answer · 2015-06-25 21:23:25Z

2

create or replace function func1(listof_id integer[])
  returns setof func_type as
$body$  

x = []
for item in listof_id:
    query = "select {0} as x, {0} * 2 as y, {0} * 3 as z, {0} * 4 as zz".format(item)
    result_set = plpy.execute(query)
    x.extend([[l['x'], l['y'], l['z'], l['zz']] for l in result_set])

return x
$body$ language plpythonu
;

select * from func1(array[1,2]);
 x | y | z | zz 
---+---+---+----
 1 | 2 | 3 |  4
 2 | 4 | 6 |  8

answered Jun 25, 2015 at 21:23

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL how to append multiple results of execute query?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related