PostgreSQL lateral expressions

Support for PostgreSQL lateral expressions. This is treated as an unary
function applied to a query expression.

Lateral is a separate function to provide interoperability with aliases
and unions. These are also separate node types that wrap
SelectStatements. The lateral option would need to be implemented in
these nodes separately if lateral was an option of SelectStatement.

When building the query, an alias can be given as an argument. This
enables building a lateral query with an table alias without using
either Nodes::TableAlias or Nodes::Lateral directly.

5 files changed, 39 insertions, 0 deletions

diff --git a/lib/arel/nodes/unary.rb b/lib/arel/nodes/unary.rb
index a42744b1d5..60cff1defe 100644
--- a/lib/arel/nodes/unary.rb
+++ b/lib/arel/nodes/unary.rb
@@ -28,6 +28,7 @@ module Arel
       Group
       GroupingElement
       GroupingSet
+      Lateral
       Limit
       Lock
       Not
diff --git a/lib/arel/select_manager.rb b/lib/arel/select_manager.rb
index 0b35176842..73fa5da6ed 100644
--- a/lib/arel/select_manager.rb
+++ b/lib/arel/select_manager.rb
@@ -204,6 +204,11 @@ module Arel
     end
     alias :minus :except
 
+    def lateral table_name = nil
+      base = table_name.nil? ? ast : as(table_name)
+      Nodes::Lateral.new(base)
+    end
+
     def with *subqueries
       if subqueries.first.is_a? Symbol
         node_class = Nodes.const_get("With#{subqueries.shift.to_s.capitalize}")
diff --git a/lib/arel/visitors/depth_first.rb b/lib/arel/visitors/depth_first.rb
index 5416a285f5..b3bbc9bd40 100644
--- a/lib/arel/visitors/depth_first.rb
+++ b/lib/arel/visitors/depth_first.rb
@@ -25,6 +25,7 @@ module Arel
       alias :visit_Arel_Nodes_GroupingElement   :unary
       alias :visit_Arel_Nodes_Grouping          :unary
       alias :visit_Arel_Nodes_Having            :unary
+      alias :visit_Arel_Nodes_Lateral           :unary
       alias :visit_Arel_Nodes_Limit             :unary
       alias :visit_Arel_Nodes_Not               :unary
       alias :visit_Arel_Nodes_Offset            :unary
diff --git a/lib/arel/visitors/postgresql.rb b/lib/arel/visitors/postgresql.rb
index f0991a2f11..bd4421bd58 100644
--- a/lib/arel/visitors/postgresql.rb
+++ b/lib/arel/visitors/postgresql.rb
@@ -5,6 +5,7 @@ module Arel
       CUBE = 'CUBE'
       ROLLUP = 'ROLLUP'
       GROUPING_SET = 'GROUPING SET'
+      LATERAL = 'LATERAL'
 
       private
 
@@ -69,6 +70,23 @@ module Arel
         grouping_array_or_grouping_element o, collector
       end
 
+      def visit_Arel_Nodes_Lateral o, collector
+        collector << LATERAL
+        collector << SPACE
+        grouping_parentheses o, collector
+      end
+
+      # Used by Lateral visitor to enclose select queries in parentheses
+      def grouping_parentheses o, collector
+        if o.expr.is_a? Nodes::SelectStatement
+          collector << "("
+          visit o.expr, collector
+          collector << ")"
+        else
+          visit o.expr, collector
+        end
+      end
+
       # Utilized by GroupingSet, Cube & RollUp visitors to
       # handle grouping aggregation semantics
       def grouping_array_or_grouping_element o, collector
diff --git a/test/visitors/test_postgres.rb b/test/visitors/test_postgres.rb
index 26cc721871..d3cab623c4 100644
--- a/test/visitors/test_postgres.rb
+++ b/test/visitors/test_postgres.rb
@@ -51,6 +51,20 @@ module Arel
         assert_equal 'SELECT DISTINCT', compile(core)
       end
 
+      it 'encloses LATERAL queries in parens' do
+        subquery = @table.project(:id).where(@table[:name].matches('foo%'))
+        compile(subquery.lateral).must_be_like %{
+          LATERAL (SELECT id FROM "users" WHERE "users"."name" ILIKE 'foo%')
+        }
+      end
+
+      it 'produces LATERAL queries with alias' do
+        subquery = @table.project(:id).where(@table[:name].matches('foo%'))
+        compile(subquery.lateral('bar')).must_be_like %{
+          LATERAL (SELECT id FROM "users" WHERE "users"."name" ILIKE 'foo%') bar
+        }
+      end
+
       describe "Nodes::Matches" do
         it "should know how to visit" do
           node = @table[:name].matches('foo%')