6

I am having difficulty using the results from a CTE 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 descendants 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 to equal an array

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

I can use an array to select values from the table

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

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

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

The resulting error indicates that there is a type mismatch. 

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

However, I am unsure how to correctly accomplish this.

1 Answers1

3

Use:

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

See detailed explanation in this answer.

Using unnest() in a subquery is a general method for dealing with arrays:

where id = any(select unnest(some_array))

Because array_agg() and unnest() are inverse operations, the query can be as simply as:

SELECT * 
FROM directory 
WHERE id = ANY(SELECT id FROM tree);
Community
  • 1
  • 1
klin
  • 112,967
  • 15
  • 204
  • 232
  • Why does the query also work when explicitly creating an array using `ANY(ARRAY[3, 4])`? –  Jul 29 '15 at 00:23
  • 1
    Because it is another form of the function: `any(array expression)` - gets all **elements** from the array. In the answer there is `any(subquery)`, which gets all **rows** from the subquery. – klin Jul 29 '15 at 07:47