Implement support for functions as FROM with columns clause support

Implemented support for "table valued functions" along with additional
syntaxes supported by PostgreSQL, one of the most commonly requested
features. Table valued functions are SQL functions that return lists of
values or rows, and are prevalent in PostgreSQL in the area of JSON
functions, where the "table value" is commonly referred towards as the
"record" datatype. Table valued functions are also supported by Oracle and
SQL Server.

Moved from I5b093b72533ef695293e737eb75850b9713e5e03 due
to accidental push

Fixes: #3566
Change-Id: Iea36d04c80a5ed3509dcdd9ebf0701687143fef5

2 files changed, 29 insertions, 0 deletions

diff --git a/lib/sqlalchemy/testing/requirements.py b/lib/sqlalchemy/testing/requirements.py
index f5286c85d..d8da9c818 100644
--- a/lib/sqlalchemy/testing/requirements.py
+++ b/lib/sqlalchemy/testing/requirements.py
@@ -58,6 +58,18 @@ class SuiteRequirements(Requirements):
         return exclusions.open()
 
     @property
+    def table_value_constructor(self):
+        """Database / dialect supports a query like::
+
+             SELECT * FROM VALUES ( (c1, c2), (c1, c2), ...)
+             AS some_table(col1, col2)
+
+        SQLAlchemy generates this with the :func:`_sql.values` function.
+
+        """
+        return exclusions.closed()
+
+    @property
     def standard_cursor_sql(self):
         """Target database passes SQL-92 style statements to cursor.execute()
         when a statement like select() or insert() is run.
diff --git a/lib/sqlalchemy/testing/suite/test_select.py b/lib/sqlalchemy/testing/suite/test_select.py
index 0d9f08848..c6dacd423 100644
--- a/lib/sqlalchemy/testing/suite/test_select.py
+++ b/lib/sqlalchemy/testing/suite/test_select.py
@@ -32,6 +32,7 @@ from ... import true
 from ... import tuple_
 from ... import union
 from ... import util
+from ... import values
 from ...exc import DatabaseError
 from ...exc import ProgrammingError
 
@@ -151,6 +152,22 @@ class OrderByLabelTest(fixtures.TablesTest):
         self._assert_result(stmt, [(1, 3), (1, 5), (1, 7)])
 
 
+class ValuesExpressionTest(fixtures.TestBase):
+    __requires__ = ("table_value_constructor",)
+
+    __backend__ = True
+
+    def test_tuples(self, connection):
+        value_expr = values(
+            column("id", Integer), column("name", String), name="my_values"
+        ).data([(1, "name1"), (2, "name2"), (3, "name3")])
+
+        eq_(
+            connection.execute(select(value_expr)).all(),
+            [(1, "name1"), (2, "name2"), (3, "name3")],
+        )
+
+
 class FetchLimitOffsetTest(fixtures.TablesTest):
     __backend__ = True