Recursive query using postgreSQL

Question

My dataBase contains data (Image for example) and this data can be modified by a program (Image processing for example) so I get a new image derived from the other, and this image could be modified as well, etc...

2 Images could also be used to create a new one, for example: image a + image b = image c

So in my dataBase I have a table call "Derived from" which contains 2 columns (previous_id, new_id), previous_id is the image before an image processing and new_id is the result. So I can have a "change history" like this:

+------------------+------------------+
| id_previous      | id_new           |
+------------------+------------------+
| a                | c                |
| b                | c                |
| c                | d                |
| d                | e                |
+------------------+------------------+

So my questions is:

Is it possible to make a recursive query to have all the history of an data ID ?

Something like this:

Select * from derived_from where id_new = 'e'

Should return (d,c,b,a)

Thank you for your help

pozs · Accepted Answer · 2017-05-15 10:45:11Z

3

Yes, you can achieve this with a recursive CTE:

with recursive r as (
    select id_previous
    from   derived_from
    where  id_new = 'e'
  union
    select d.id_previous
    from   derived_from d
    join   r on id_new = r.id_previous
)
select id_previous
from   r

http://rextester.com/NZKT73800

Notes:

UNION can stop the recursion even when you have loops. With UNION ALL, you should handle loops yourself, unless you are really sure you have no loops.
This will give you separate rows (one for each "ascendant"). You can aggregate this too, but it's typically much more easier to consume than comma separated lists or arrays.

edited May 15, 2017 at 10:45

answered May 15, 2017 at 10:39

pozs

36.6k5 gold badges61 silver badges66 bronze badges

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

1 Comment

Fedour Over a year ago

It's working, thank you for your help and your indications. It really helped me !

Gordon Linoff · Accepted Answer · 2017-05-15 10:40:55Z

1

You can use a recursive CTE:

with recursive cte as (
      select df.id_new, df.id_previous as parent
      from derived_from df
      where df.id_new = 'e'
      union all
      select cte.id_new, df.id_previous
      from cte join
           derived_from df
           on cte.parent = df.id_new
     )
select id_new, array_agg(parent)
from cte
group by id_new;

answered May 15, 2017 at 10:40

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Recursive query using postgreSQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related