From a0de45185bf510fca9e237d9191e89391d118591 Mon Sep 17 00:00:00 2001
From: Mike Bayer <mike_mp@zzzcomputing.com>
Date: Sat, 9 Mar 2013 17:40:06 -0500
Subject: [PATCH] Added support for Postgresql's traditional SUBSTRING function
 syntax, renders as "SUBSTRING(x FROM y FOR z)" when regular
 ``func.substring()`` is used. Also in 0.7.11.  Courtesy Gunnlaugur Por Briem.
 [ticket:2676]

---
 doc/build/changelog/changelog_07.rst       |  9 +++++++++
 doc/build/changelog/changelog_08.rst       |  9 +++++++++
 lib/sqlalchemy/dialects/postgresql/base.py | 12 ++++++++++--
 test/dialect/test_postgresql.py            |  8 ++++++++
 4 files changed, 36 insertions(+), 2 deletions(-)

diff --git a/doc/build/changelog/changelog_07.rst b/doc/build/changelog/changelog_07.rst
index df919dc3d9..a42ef3bb6c 100644
--- a/doc/build/changelog/changelog_07.rst
+++ b/doc/build/changelog/changelog_07.rst
@@ -6,6 +6,15 @@
 .. changelog::
     :version: 0.7.11
 
+    .. change::
+        :tags: feature, postgresql
+        :tickets: 2676
+
+      Added support for Postgresql's traditional SUBSTRING
+      function syntax, renders as "SUBSTRING(x FROM y FOR z)"
+      when regular ``func.substring()`` is used.
+      Courtesy Gunnlaugur Þór Briem.
+
     .. change::
         :tags: bug, tests
         :tickets: 2669
diff --git a/doc/build/changelog/changelog_08.rst b/doc/build/changelog/changelog_08.rst
index 3a93667b6c..1a509089e3 100644
--- a/doc/build/changelog/changelog_08.rst
+++ b/doc/build/changelog/changelog_08.rst
@@ -18,6 +18,15 @@
 
       * :ref:`correlation_context_specific`
 
+    .. change::
+        :tags: feature, postgresql
+        :tickets: 2676
+
+      Added support for Postgresql's traditional SUBSTRING
+      function syntax, renders as "SUBSTRING(x FROM y FOR z)"
+      when regular ``func.substring()`` is used.
+      Also in 0.7.11.  Courtesy Gunnlaugur Þór Briem.
+
     .. change::
         :tags: feature, orm
         :tickets: 2675
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index a7a9e65ce1..c59caff8d2 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -1030,6 +1030,15 @@ class PGCompiler(compiler.SQLCompiler):
             field, self.process(expr))
 
 
+    def visit_substring_func(self, func, **kw):
+        s = self.process(func.clauses.clauses[0], **kw)
+        start = self.process(func.clauses.clauses[1], **kw)
+        if len(func.clauses.clauses) > 2:
+            length = self.process(func.clauses.clauses[2], **kw)
+            return "SUBSTRING(%s FROM %s FOR %s)" % (s, start, length)
+        else:
+            return "SUBSTRING(%s FROM %s)" % (s, start)
+
 class PGDDLCompiler(compiler.DDLCompiler):
     def get_column_specification(self, column, **kwargs):
         colspec = self.preparer.format_column(column)
@@ -1042,8 +1051,7 @@ class PGDDLCompiler(compiler.DDLCompiler):
                 (
                     isinstance(column.default, schema.Sequence) and
                     column.default.optional
-                )
-            ):
+                )):
             if isinstance(impl_type, sqltypes.BigInteger):
                 colspec += " BIGSERIAL"
             else:
diff --git a/test/dialect/test_postgresql.py b/test/dialect/test_postgresql.py
index d9fb9830df..62e8553569 100644
--- a/test/dialect/test_postgresql.py
+++ b/test/dialect/test_postgresql.py
@@ -180,6 +180,14 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
                             'USING hash (data)',
                             dialect=postgresql.dialect())
 
+    def test_substring(self):
+        self.assert_compile(func.substring('abc', 1, 2),
+                            'SUBSTRING(%(substring_1)s FROM %(substring_2)s '
+                            'FOR %(substring_3)s)')
+        self.assert_compile(func.substring('abc', 1),
+                            'SUBSTRING(%(substring_1)s FROM %(substring_2)s)')
+
+
 
     def test_extract(self):
         t = table('t', column('col1', DateTime), column('col2', Date),
-- 
2.47.3