2

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?

2
  • 1
    Why don't you use a recursive CTE?
    – user1822
    Mar 21, 2013 at 13:08
  • Simply because I'm unaware of the functionality :) I'm looking into it!
    – Chau
    Mar 21, 2013 at 13:24

2 Answers 2

6

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

2
  • I see that the itemid is a constant. If I were to parameterise your answer (itemid), should this be done using a function? I'm just asking so that I don't wander back on my already-known-paths if I have a chance to learn new stuff.
    – Chau
    Mar 21, 2013 at 13:56
  • 1
    @Chau: depends on how you want to run the SQL. If that is used from inside a program, use a prepared statement (or whatever the equivalent in your programming language is). If you need to run this interactively without wanting to write the full query each time, you can wrap this query into a SQL function that accepts the "starting" ID as a parameter.
    – user1822
    Mar 21, 2013 at 14:06
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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