I use Postgresql 9.1 with Ubuntu 12.04.
In a plpgsql
function I try to concatenate setof
type returned from another function.
the type pair_id_value
in question is created with create type pair_id_value as (id bigint, value integer);
the function that returns elementary setof pair_id_value
(those that will be concatenated later) is this one:
create or replace function compute_pair_id_value(id bigint, value integer)
returns setof pair_id_value
as $$
listResults = []
for x in range(0,value+1):
listResults.append({ "id": id, "value": x})
return listResults
$$
language plpython3u;
this straigth-forward plpython code should be good, for exemple the query: select * from compute_pair_id_value(1712437,2);
returns nicely:
id | value
---------------+-----------
1712437 | 0
1712437 | 1
1712437 | 2
(3 rows)
this python function is fairly simple for now, for this example, but above all for my proof of concept. It will be more complex in the near future.
The problem arises when I try to concatenate all the result tables from multiples id.
create or replace function compute_all_pair_id_value(id_obj bigint)
returns setof pair_id_value as $$
declare
pair pair_id_value;
begin
for pair in (select compute_pair_id_value(t.id, t.obj_value) from my_obj as t where t.id = id_obj)
loop
return next pair;
end loop;
return;
end; $$ language plpgsql;
I receive the error: invalid input syntax for integer "(1712437,0)"
as if it is no longer seen as a pair_id_value with two columns but as a tuple (1712437,0).
So I changed the output type of the function from setof pair_id_value to setof record… and if I execute this similar concatenation function:
create or replace function compute_all_pair_id_value(id_obj bigint)
returns setof record as $$
declare
pair record;
begin
for pair in (select compute_pair_id_value(t.id, t.obj_value) from my_obj as t where t.id = id_obj)
loop
return next pair;
end loop;
return;
end; $$ language plpgsql;
I get the error: a column definition list is required for functions returning "record"
Trying to follow the answer to this SO question: I have tried defining the column definition in the select this way select compute_pair_id_value(t.id, t.obj_value) as f(id bigint, value integer)
, the complete code is here:
create or replace function compute_all_pair_id_value(id_obj bigint)
returns setof record as $$
declare
pair record;
begin
for pair in (select compute_pair_id_value(t.id, t.obj_value) as f(id bigint, value integer) from my_obj as t where t.id = id_obj)
loop
return next pair;
end loop;
return;
end; $$ language plpgsql;
But when launching the sql script, psql doesn't accept to create the function:
syntax error at or near "(" select compute_pair_id_value(t.id, t.obj_value) as f(id bigint, value integer)
… pointing the finger at the f(
Any idea how to do it properly ?
Should I consider to create temporary table to do the job ?
Best Answer
The approach you're using is unnecessarily complex - and very inefficient. Instead of the first function use:
or better, get rid of it entirely and write the whole operation like this:
Resulting in:
This exploits PostgreSQL's behaviour with set-returning functions called in the
SELECT
list. Once PostgreSQL 9.3 comes out it can be replaced with a standards-compliantLATERAL
query.Since it turns out your question was a simplified version of the real problem, let's tackle that. I'll work with the simplified
compute_pair_id_value
above to avoid the hassle of plpython3. Here's how to do what you want:Result:
but again, be warned that
compute_pair_id_value
will be called more than once. This is a limitation of PostgreSQL's query executor that can be avoided in 9.3 withLATERAL
support, but as far as I know you're stuck with it in 9.2 and below. Observe:output:
See how
compute_pair_id_value
is called once per output column?There is a workaround: Another layer of subquery to unpack the composite type result. See:
You can use the same technique in your code if you really must
LOOP
over the results (it's slow to do that, so avoid it if you can).