High Performance PostgreSQL for Rails

Using Common Table Expressions (CTE)

Common Table Expressions (CTE) are a technique in PostgreSQL to combine two or more standalone queries into a single statement. Each statement gets a name, helping you better organize your multiquery statements.

Support for PostgreSQL CTE queries was added in Active Record 7.1. Active Record added a .with method (see “Rails 7.1 Construct Common Table Expressions”^[141]) which generates a CTE. Prior to Rails 7.1, a CTE could be integrated in Active Record using the activerecord-cte^[142] gem.

A CTE can be used recursively by combining a nonrecursive portion and a recursive portion, then using a UNION to combine the parts. A Recursive CTE will not be covered here, but PostgreSQL Tutorial has a nice example ...