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?