How to do recursive select in PostgreSQL with array as an argument

Question

I am trying to implement easy recursive function in PostgreSQL but I cannot finish it...

I have got table MyTable which includes columns Col1 and Col2. Data inside is like this:

Col1 | Col2

1 | 2  
2 | 5 
2 | 6
3 | 7
4 | 5
4 | 2
5 | 3

I would like to write a function which takes as a parameter array of Col1 f.e. (1,2) and gives me back values from Col2 like this :

1 | 2
2 | 5
2 | 6

and after that, does it again with results : (2, 5, 6) so:

(2 is already in, and key '6' does not exist) and again (3):

and for (7) nothing because value '7' does not exist in Col1.

It is an easy recursion but I have no idea how to implement it. I have got so far something like this:

with recursive aaa(params) as (
    select Col1, Col2
    from MyTable
    where Col1 = params -- I need an array here
    union all
    select Col1, Col2
    from aaa
)
select * from aaa;

But it of course does not work

Thanks in advance

Ants Aasma · Accepted Answer · 2012-09-17 13:49:03Z

7

The basic pattern for recursion is to have your base case as the first part of the union and in the second part join the recursion result to what you need to produce the next level of results. In your case it would look like this:

WITH RECURSIVE aaa(col1, col2) AS (
        SELECT col1, col2 FROM mytable
            WHERE col1 = ANY (ARRAY[1,2]) -- initial case based on an array
    UNION -- regular union because we only want new values
        SELECT child.col1, child.col2
            FROM aaa, mytable AS child -- join the source table to the result
            WHERE aaa.col2 = child.col1 -- the recursion condition
) 
SELECT * FROM aaa;

answered Sep 17, 2012 at 13:49

Ants Aasma

55.3k16 gold badges98 silver badges99 bronze badges

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

1 Comment

Autumn Skye Over a year ago

This is a great question and a great answer. This also solves the general problem of querying a table representing a bidirectional, multifurcating, self-referencing graph from any internal node without resorting to using CONNECTBY.

Collectives™ on Stack Overflow

How to do recursive select in PostgreSQL with array as an argument

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related