Hi i want to select objects with postgres recursive. But i cannot guarantee that every data in the database is correct. So i'ts not impossible that an object has for example itself as parent id.
Is it possible to stop when an object already exists? What i've tried:
with recursive sumthis(myobjectid,parent_myobjectid) as (
select myobjectid, parent_myobjectid
from myobjectentity
where myobjectid = 243358
union all
select C.myobjectid, C.parent_myobjectid
from sumthis P
inner join myobjectentity C on P.myobjectid = C.parent_myobjectid AND (P.myobjectid NOT IN (SELECT myobjectid FROM sumthis))
)
SELECT * FROM myobjectentity WHERE myobjectid IN (select myobjectid from sumthis)
AND
with recursive sumthis(myobjectid,parent_myobjectid) as (
select myobjectid, parent_myobjectid
from myobjectentity
where myobjectid = 243358
union all
select C.myobjectid, C.parent_myobjectid
from sumthis P
inner join myobjectentity C on P.myobjectid = C.parent_myobjectid
Limit 100
)
SELECT * FROM myobjectentity WHERE myobjectid IN (select myobjectid from sumthis)