CODEWITHSUNDEEP
sqlpostgresqlrecursive-query
Lusi
Lusi

Reputation: 411

Use a recursive query in a PostgreSQL function

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?

Upvotes: 0

Views: 2526

Answers (1)

klin
klin

Reputation: 121774

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.

Upvotes: 4

Related Questions