Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

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)
share|improve this question
1  
This is a special case of non-repeating directed graph traversal. Thankfully as you discovered, recursive CTEs (used right) help with this. –  Craig Ringer 2 days ago

1 Answer 1

Ok the answer is reasy, i just had to change

union all 

to

union 
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.