Use a recursive query in a PostgreSQL function

Question

I have a table folder with (id, name, parent_id ) columns. The folder which is parent it's parent_id = -1. I need to get top level parent Id of each folder. The following query works fine, but it doesn't work with spring data jpa.

WITH RECURSIVE nodes_cte AS (
SELECT tn.id, tn.parent_id, tn.name, tn.id top_id
FROM folder AS tn 
WHERE tn.parent_id =-1
UNION ALL
SELECT c.id, c.parent_id, c.name, p.top_id
FROM nodes_cte AS p, folder AS c 
WHERE c.parent_id = p.id
)
SELECT distinct * FROM nodes_cte

How can I create postgresql function which will return same result as above query?

klin · Accepted Answer · 2017-06-05 12:05:21Z

4

Create an SQL function returning a table:

CREATE OR REPLACE FUNCTION my_function()
RETURNS TABLE (id int, parent_id int, name text, top_id int)
LANGUAGE SQL AS $$
    WITH RECURSIVE nodes_cte AS (
    SELECT tn.id, tn.parent_id, tn.name, tn.id top_id
    FROM folder AS tn 
    WHERE tn.parent_id =-1
    UNION ALL
    SELECT c.id, c.parent_id, c.name, p.top_id
    FROM nodes_cte AS p, folder AS c 
    WHERE c.parent_id = p.id
    )
    SELECT distinct * FROM nodes_cte
$$;

--use:
SELECT * 
FROM my_function();

Note, the name column in a returned table should have the same type as in the table folder.

answered Jun 5, 2017 at 12:05

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Use a recursive query in a PostgreSQL function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related