Use postgres CTE result in function

Question

I find it difficult to use the results of a CTE function in a function. Given the following Postgres table.

CREATE TABLE directory ( id SERIAL PRIMARY KEY , name TEXT , parent_id INTEGER REFERENCES directory(id) ); INSERT INTO directory (name, parent_id) VALUES ('Root', NULL), ('D1', 1), ('D2', 2), ('D3', 3); 

I have this recursive CTE that returns the children of a directory.

 WITH RECURSIVE tree AS ( SELECT id FROM directory WHERE parent_id = 2 UNION ALL SELECT directory.id FROM directory, tree WHERE directory.parent_id = tree.id ) 

The returned values ​​are what I expect and can be made equal to an array

 SELECT (SELECT array_agg(id) FROM tree) = ARRAY[3, 4]; 

I can use an array to select values ​​from a table

 SELECT * FROM directory WHERE id = ANY(ARRAY[3, 4]); 

However, I cannot use the results of CTE to accomplish the same thing.

 SELECT * FROM directory WHERE id = ANY(SELECT array_agg(id) FROM tree); 

A received error indicates a type mismatch.

 HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. 

However, I'm not sure how to do it right.

Answer 1

Using:

 SELECT * FROM directory WHERE id = ANY(SELECT unnest(array_agg(id)) FROM tree); 

In this answer, see a detailed explanation .

Using unnest() in a subquery is a general method of working with arrays:

 where id = any(select unnest(some_array)) 

Since array_agg() and unnest() are inverse operations, the query can be as simple as:

 SELECT * FROM directory WHERE id = ANY(SELECT id FROM tree);