Hey Nikolay, On Wed, Sep 11, 2013 at 10:26 AM, Nicholas Ustinov <[email protected]> wrote: > Thank you for sharing experience! > Have you ever experienced troubles with recursive CTEs in Postgres or MSSQL?
I haven't experienced specific troubles with recursive CTEs, but to put this into context, I was talking about an alternative to recursive CTEs and CONNECT BY. I modified the recursive table using a closure tree pattern, which boils down to some pretty plain jane SQL-90 joins, hence avoiding any constructs that don't have middle of the road support across different DBs. > I worked only with Oracle's connect by and it works good enough, but I have > no experience with scalability of recursive CTEs in Postgres. I imagine that the closure tree pattern would have similar algorithmic complexities across different DBs. Whether it will be good enough for what you are doing could only be established by testing it. Also, it generally means that you need to change your table layout somewhat. Another option you have is to programmatically construct DB specific SQL based on the run time dialect. Whilst this might mean more maintenance, JOOQ at least provides a neat API to manage SQL statement building blocks, so you've at least got a chance of keeping your code both well factored and portable. Does that answer your question? Ben -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
