Recursive query using plpgsql

Question

I'm trying to write a plpgsql function that recursively returns a set of columns from records in a tree structure.

I have a data table and a table to link the data together:

DATATABLE
-----------
id integer
value text
info text

LINKTABLE
-----------
link integer
parent integer

My thought was to do like in the following function:

CREATE OR REPLACE FUNCTION my_function(itemID integer)
  RETURNS TABLE(id integer, value text) AS
$BODY$
BEGIN    
    RETURN QUERY SELECT my_function(A.link) FROM linktable A, datatable B 
        WHERE A.parent = B.id AND B.id = itemID) C;

    RETURN QUERY SELECT id, value FROM datatable WHERE id = itemID;            
    RETURN;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

But that doesn't work, I get an error in my first query:

ERROR: structure of query does not match function result type

My Just-In-Brain compiler detects no problems, so what am I doing wrong here?

Answer 1

You don't need a the function at all, this can be done with a single SQL statement:

with recursive tree as (id, parent) (
    select link as id, 
           parent
    from linktable
    where id = itemid

    union all

    select c.link as id,
           c.parent
    from linktable c
      join tree p on p.id = c.parent
) 
select dt.id, dt.value
from tree
  join datatable dt on dt.id = tree.id

Please see the manual for an introduction to recursive queries: http://www.postgresql.org/docs/current/static/queries-with.html

Answer 2

If you are sure you want to do this in a plpgsql function, than a few modifications will make it going:

CREATE OR REPLACE FUNCTION my_function(itemID integer)
  RETURNS TABLE(id integer, value text) AS
$BODY$
BEGIN
    RETURN QUERY
    SELECT (my_function(A.link)).*
    FROM linktable A
    JOIN datatable B ON A.parent = B.id AND B.id = itemID;

    RETURN QUERY 
    SELECT d.id, d.value 
    FROM datatable d
    WHERE d.id = itemID;

END;
$BODY$
LANGUAGE plpgsql;

• regarding the error you indicated, enclose the function call in parentheses and add .* after
• because the returning table has the same column names as your datatable, you have to qualify the column names in the second query
• remove ) C in order to have proper syntax

Anyway, I agree with a_horse_with_no_name about using CTEs.