Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question
    
How is pair_id_value defined? With CREATE TYPE as a composite type? –  Craig Ringer Mar 26 '13 at 0:12
    
I used the line create type pair_id_value as (id bigint, value integer); –  Stephane Rolland Mar 26 '13 at 0:19
    
Argh, it's right there in the question text. Sorry, I missed it. –  Craig Ringer Mar 26 '13 at 0:21
    
I tried your answer with return query and the .* after the setof record returned by the function. But it gives me a syntax error because at or near the . of .* –  Stephane Rolland Mar 26 '13 at 0:30
    
Fixed based on better understanding of the underlying problem you're trying to solve. –  Craig Ringer Mar 26 '13 at 0:32
add comment

1 Answer

up vote 2 down vote accepted

The approach you're using is unnecessarily complex - and very inefficient. Instead of the first function use:

create or replace function compute_pair_id_value(id bigint, value integer)
    returns setof pair_id_value
as $$
SELECT $1, generate_series(0,$2);
$$                          
language sql;

or better, get rid of it entirely and write the whole operation like this:

-- Sample data creation:
CREATE TABLE my_obj(id bigint, obj_value integer);
insert into my_obj(id,obj_value) VALUES (1712437,2),(17000,5);

-- and the query:
SELECT id, generate_series(0,obj_value) FROM my_obj;

Resulting in:

regress=> SELECT id, generate_series(0,obj_value) FROM my_obj;
   id    | generate_series 
---------+-----------------
 1712437 |               0
 1712437 |               1
 1712437 |               2
   17000 |               0
   17000 |               1
   17000 |               2
   17000 |               3
   17000 |               4
   17000 |               5
(9 rows)

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-compliant LATERAL 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:

SELECT (compute_pair_id_value(id,obj_value)).* FROM my_obj;

Result:

regress=> SELECT (compute_pair_id_value(id,obj_value)).* FROM my_obj;
   id    | value 
---------+-------
 1712437 |     0
 1712437 |     1
 1712437 |     2
   17000 |     0
   17000 |     1
   17000 |     2
   17000 |     3
   17000 |     4
   17000 |     5
(9 rows)

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 with LATERAL support, but as far as I know you're stuck with it in 9.2 and below. Observe:

create or replace function compute_pair_id_value(id bigint, value integer)
    returns setof pair_id_value
as $$
BEGIN
  RAISE NOTICE 'compute_pair_id_value(%,%)',id,value;
  RETURN QUERY SELECT $1, generate_series(0,$2);
END;
$$             
language plpgsql;

output:

regress=> SELECT (compute_pair_id_value(id,obj_value)).* FROM my_obj;
NOTICE:  compute_pair_id_value(1712437,2)
NOTICE:  compute_pair_id_value(1712437,2)
NOTICE:  compute_pair_id_value(17000,5)
NOTICE:  compute_pair_id_value(17000,5)
   id    | value 
---------+-------
 1712437 |     0
 1712437 |     1
 1712437 |     2
   17000 |     0
   17000 |     1
   17000 |     2
   17000 |     3
   17000 |     4
   17000 |     5
(9 rows)

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:

regress=> SELECT (val).* FROM (SELECT compute_pair_id_value(id,obj_value) FROM my_obj) x(val);
NOTICE:  compute_pair_id_value(1712437,2)
NOTICE:  compute_pair_id_value(17000,5)
   id    | value 
---------+-------
 1712437 |     0
 1712437 |     1
 1712437 |     2
   17000 |     0
   17000 |     1
   17000 |     2
   17000 |     3
   17000 |     4
   17000 |     5
(9 rows)

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).

share|improve this answer
    
I have simplified the python function, later it will perform more complex work, I need to be able to concatenate setof types/ record generated by plpython. –  Stephane Rolland Mar 26 '13 at 0:34
    
@StephaneRolland Ah. Things like "this is a simplified version of the real problem" are handy to mention in the original question. –  Craig Ringer Mar 26 '13 at 0:35
    
as I show in the question, the output of the plpython function looks really ok. –  Stephane Rolland Mar 26 '13 at 0:35
    
ok I'll mention it, however, the plpython function is in the state it IS right now. It will be more complex in the near future, when this will work ;-) –  Stephane Rolland Mar 26 '13 at 0:36
1  
@StephaneRolland Honestly, hard to say. In cases like that I tend to build test cases with dummy data and examine their performance. psql's \timing command is useful for this but it also records data transfer from, so the server side setting log_min_duration_statement (to log just server-side execution time) and the EXPLAIN ANALYZE command are very handy. BTW, answer updated with a workaround for the double execution of the function; the extra subquery appears to get optimized away so it shouldn't affect performance. –  Craig Ringer Mar 26 '13 at 0:49
show 4 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.