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'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?

share|improve this question
1  
Why don't you use a recursive CTE? –  a_horse_with_no_name Mar 21 '13 at 13:08
    
Simply because I'm unaware of the functionality :) I'm looking into it! –  Chau Mar 21 '13 at 13:24

2 Answers 2

up vote 6 down vote accepted

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

share|improve this answer
    
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 '13 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. –  a_horse_with_no_name Mar 21 '13 at 14:06

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.

share|improve this answer

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.