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

Answer 1

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.

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

Answer 2

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;