Use result of postgres CTE in function

Question

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.

Community · Accepted Answer · 2017-05-23 12:16:19Z

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);

edited May 23, 2017 at 12:16

CommunityBot

11 silver badge

answered Jul 28, 2015 at 20:40

klin

123k15 gold badges241 silver badges263 bronze badges

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

2 Comments

user3509195 Over a year ago

Why does the query also work when explicitly creating an array using ANY(ARRAY[3, 4])?

klin Over a year ago

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.

Collectives™ on Stack Overflow

Use result of postgres CTE in function

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related