I really like the way of how feldera implemented recursion in SQL: https://docs.feldera.com/sql/recursion

All you have to do is to add a forward declaration of the view and then you can reference it in a recursive query. Makes the syntax part much easier.

This is somewhat common when high performance is needed it seems: e.g., https://github.com/feldera/feldera comes to mind which uses the same approach to compile SQL to type-safe incremental query plans in rust.