[elpa] externals/sql-indent 5656cfb 8/9: Document limitations of recognizing DECLARE statements (#92)

[Alex Harsanyi]

branch: externals/sql-indent
commit 5656cfb0a6eebd5ac159cbdc5489c2d8fcaaaccf
Author: Alex Harsanyi <address@hidden>
Commit: Alex Harsanyi <address@hidden>

    Document limitations of recognizing DECLARE statements (#92)
    
    The current `sql-indent` implementation is unable to recognize DECLARE 
blocks
    in all situations.  Documented this limitation, for now.
---
 sql-indent.org | 78 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 78 insertions(+)

diff --git a/sql-indent.org b/sql-indent.org
index d951729..049508e 100644
--- a/sql-indent.org
+++ b/sql-indent.org
@@ -480,6 +480,15 @@ clause (select, from, where, etc) in which the current 
point is.
  * ~(in-update-clause CLAUSE)~ -- line is inside an update CLAUSE, which can
    be "update", "set" or "where"
 * Limitations
+
+The sql-indent package does not contain a full SQL parser, instead it relies
+on various heuristics to determine the context of each line in a SQL program.
+Relying on heuristics means that sometimes valid SQL code is not detected
+correctly, and therefore the indentation will be wrong.
+
+This section contains some of the known cases that are incorrectly detected,
+and provides some workarounds for them.
+
 ** Parsing expressions
 
 There is no support for parsing SQL expressions, so if an expression is broken
@@ -553,4 +562,73 @@ workarounds:
     from some_table;
 #+END_SRC
 
+** DECLARE statements in Postgres
+
+The DECLARE statement in Postgres can start a block of declarations or declare
+a single item.  Unfortunately, the sql-indent package is not always able to
+differentiate between the two.  Curently, DECLARE blocks are only recognized
+at the start of another enclosing block, such as $$, BEGIN, THEN or ELSE, but
+they can occur on other situations.  The workaround is to enclose the DECLARE
+block inside a BEGIN/END block or to use individual DECLARE statements.
+
+For more info see https://github.com/alex-hhh/emacs-sql-indent/issues/92
+
+DECLARE blocks are not recognized when the follow normal statements,
+sql-indent considers them single statements instead.  In the example below,
+DECLARE is considered a statement, and the ~local_b~ declaration is anchored
+off the previous BEGIN statement:
+
+#+BEGIN_SRC sql
+  -- NOT SUPPORTED: `local_b` is not recognized as a declaration
+  create function less_than(a text, b text) returns boolean as $$
+    begin
+      raise debug 'less_than(%, %)', a, b;
+      declare
+        local_a text := a;
+      local_b text := b;
+      begin
+        return local_a < local_b;
+      end;
+    end;
+  end;
+  $$ language plpgsql;
+#+END_SRC sql
+
+The workaround is to either surround the DECLARE block with a BEGIN/END
+statement, or to prefix each variable declaration with DECLARE, as in the two
+examples below:
+
+#+BEGIN_SRC sql
+  -- WORKAROUND 1: surround the DECLARE/BEGIN/END with another BEGIN/END block
+  create function less_than(a text, b text) returns boolean as $$
+    begin
+      raise debug 'less_than(%, %)', a, b;
+      begin
+        declare
+          local_a text := a;
+          local_b text := b;
+        begin
+          return local_a < local_b;
+        end;
+      end;
+    end;
+  end;
+  $$ language plpgsql;
+
+  -- WORKAROUND 2: declare each variable individually
+  create function less_than(a text, b text) returns boolean as $$
+    begin
+      raise debug 'less_than(%, %)', a, b;
+      declare
+        local_a text := a;
+      declare
+        local_b text := b;
+      begin
+        return local_a < local_b;
+      end;
+    end;
+  end;
+  $$ language plpgsql;
+#+END_SRC sql
+
 .