Repository: sqlfluff/sqlfluff

Issue:
psql variable support in Postgres dialect
### Search before asking

- [X] I searched the [issues](https://github.com/sqlfluff/sqlfluff/issues) and found no similar issues. (open issues at least)


### What Happened

There is a previous issue, #1534, but it was closed by PR #1709 which seems to add the desired behavior. However, I am still having issues, hence labeling this a bug rather than enhancement

psql expects variables to be interpolated in sql files in one of three formats
- `:var_name`
- `:'var_name'`
- `:"var_name"`

Per [psql docs](https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-INTERPOLATION). This seems to be what was implemented in #1709, but when linting a file with these variables, I get errors. The following should be valid postgresql, if we're allowing psql variables, then the query
```sql
SELECT :"column" FROM :table WHERE bla = :'my_name'
```
should be valid.

It seems that there might be some overlap between a solution here in the postgres dialect and a solution using "placeholder" template. However, no default `param_style` allows for these types of variables with quotes, only unquoted. I've implemented a solution in [my fork](https://github.com/fvankrieken/sqlfluff/commit/c66b841d0c6bca7016487aab9e157455c05fc7fc) to add a `param_style` case, while locally for now I'm just using the "placeholder" template with the regex in that commit. I'd be happy to open a PR from my fork if it's decided this should be handled via `templater`s rather than as part of the postgres dialect, but upon digging realized that maybe this specific case should already be part of the postgres dialect. Though looking at #3343, it seems like last time something like this came up, it was fixed through a `templater`. I'm still just curious as to why #1709 did not implement this by default for postgres dialect.

Or maybe it's working fine for the dialect, and I'm just missing how to enable it in my `.sqlfluff` file! If it's that, please let me know

### Expected Behaviour

I would expect the above example to be linted with no error

### Observed Behaviour

`sqlfluff lint test.sql --dialect=postgres` outputs
```
== [test.sql] FAIL                                                                                                                                                                                          
L:   1 | P:   7 | LT01 | Unexpected whitespace before colon ':'.
                       | [layout.spacing]
L:   1 | P:   9 | LT01 | Expected single whitespace between colon ':' and
                       | parameter. [layout.spacing]
L:   1 | P:  17 |  PRS | Line 1, Position 17: Found unparsable section: " FROM
                       | :table WHERE bla = :'my_name'"
WARNING: Parsing errors found and dialect is set to 'postgres'. Have you configured your dialect correctly?
All Finished 📜 🎉!
```

### How to reproduce

Lint the above `SELECT :"column" FROM :table WHERE bla = :'my_name'` with the  command `sqlfluff lint test.sql --dialect=postgres`

### Dialect

postgres

### Version

2.3.0

### Configuration

No `.sqlfluff` used for above example, just specifying postgres as dialect via command line.

### Are you willing to work on and submit a PR to address the issue?

- [X] Yes I am willing to submit a PR!

### Code of Conduct

- [X] I agree to follow this project's [Code of Conduct](https://github.com/sqlfluff/sqlfluff/blob/main/CODE_OF_CONDUCT.md)


Discussion:
github-actions[bot]: # Coverage Results ✅
```
Name    Stmts   Miss  Cover   Missing
-------------------------------------
TOTAL   17744      0   100%

225 files skipped due to complete coverage.

```

coveralls: 
[![Coverage Status](https://coveralls.io/builds/67489130/badge)](https://coveralls.io/builds/67489130)

coverage: 99.985%. remained the same
when pulling **d9fb1d12b2ee905ba72558063087b2b56382acff on fvankrieken:main**
into **1d4fd8d288e9d19aa544d3d92885b30788234470 on sqlfluff:main**.


Patch:
diff --git a/docs/source/configuration.rst b/docs/source/configuration.rst
index 95df52c7e..238a6e21f 100644
--- a/docs/source/configuration.rst
+++ b/docs/source/configuration.rst
@@ -896,6 +896,9 @@ A few common styles are supported:
     -- (use with caution as more prone to false positives)
     WHERE bla = table:my_name
 
+    -- colon_optional_quotes
+    SELECT :"column" FROM :table WHERE bla = :'my_name'
+
     -- numeric_colon
     WHERE bla = :2
 
diff --git a/src/sqlfluff/core/templaters/placeholder.py b/src/sqlfluff/core/templaters/placeholder.py
index 3c1cad821..cdd9fd722 100644
--- a/src/sqlfluff/core/templaters/placeholder.py
+++ b/src/sqlfluff/core/templaters/placeholder.py
@@ -21,6 +21,10 @@ templater_logger = logging.getLogger("sqlfluff.templater")
 KNOWN_STYLES = {
     # e.g. WHERE bla = :name
     "colon": regex.compile(r"(?<![:\w\x5c]):(?P<param_name>\w+)(?!:)", regex.UNICODE),
+    # e.g. SELECT :"column" FROM :table WHERE bla = :'name'
+    "colon_optional_quotes": regex.compile(
+        r"(?<!:):(?P<quotation>['\"]?)(?P<param_name>[\w_]+)\1", regex.UNICODE
+    ),
     # e.g. WHERE bla = table:name - use with caution as more prone to false positives
     "colon_nospaces": regex.compile(r"(?<!:):(?P<param_name>\w+)", regex.UNICODE),
     # e.g. WHERE bla = :2
@@ -156,6 +160,9 @@ class PlaceholderTemplater(RawTemplater):
                 replacement = str(context[param_name])
             else:
                 replacement = param_name
+            if "quotation" in found_param.groupdict():
+                quotation = found_param["quotation"]
+                replacement = quotation + replacement + quotation
             # add the literal to the slices
             template_slices.append(
                 TemplatedFileSlice(
diff --git a/src/sqlfluff/utils/reflow/reindent.py b/src/sqlfluff/utils/reflow/reindent.py
index b7e6e550c..f834062c4 100644
--- a/src/sqlfluff/utils/reflow/reindent.py
+++ b/src/sqlfluff/utils/reflow/reindent.py
@@ -825,7 +825,17 @@ def _crawl_indent_points(
 
             # Is the next element a comment? If so - delay the decision until we've
             # got any indents from after the comment too.
-            if "comment" in elements[idx + 1].class_types:
+            #
+            # Also, some templaters might insert custom marker slices that are of zero
+            # source string length as a way of marking locations in the middle of
+            # templated output.  These don't correspond to real source code, so we
+            # can't meaningfully indent before them.  We can safely handle them similar
+            # to the comment case.
+            if "comment" in elements[idx + 1].class_types or (
+                "placeholder" in elements[idx + 1].class_types
+                and cast(TemplateSegment, elements[idx + 1].segments[0]).source_str
+                == ""
+            ):
                 cached_indent_stats = indent_stats
                 # Create parts of a point to use later.
                 cached_point = indent_point


Test patch:
diff --git a/test/core/templaters/placeholder_test.py b/test/core/templaters/placeholder_test.py
index 1045b8c08..ec50af668 100644
--- a/test/core/templaters/placeholder_test.py
+++ b/test/core/templaters/placeholder_test.py
@@ -79,6 +79,24 @@ def test__templater_raw():
                 city_ids="(1, 2, 3, 45)",
             ),
         ),
+        (
+            """
+            SELECT user_mail, city_id, :"custom_column"
+            FROM users_data
+            WHERE userid = :user_id AND date > :'start_date'
+            """,
+            "colon_optional_quotes",
+            """
+            SELECT user_mail, city_id, "PascalCaseColumn"
+            FROM users_data
+            WHERE userid = 42 AND date > '2021-10-01'
+            """,
+            dict(
+                user_id="42",
+                custom_column="PascalCaseColumn",
+                start_date="2021-10-01",
+            ),
+        ),
         (
             """
             SELECT user_mail, city_id
@@ -317,6 +335,7 @@ def test__templater_raw():
         "colon_simple_substitution",
         "colon_accept_block_at_end",
         "colon_tuple_substitution",
+        "colon_quoted",
         "colon_nospaces",
         "colon_nospaces_double_colon_ignored",
         "question_mark",
diff --git a/test/utils/reflow/reindent_test.py b/test/utils/reflow/reindent_test.py
index 661815c50..a4c11e4bc 100644
--- a/test/utils/reflow/reindent_test.py
+++ b/test/utils/reflow/reindent_test.py
@@ -7,11 +7,18 @@ Specifically:
 """
 
 import logging
+import sys
+from typing import Callable, List, Tuple, Type
 
 import pytest
 
-from sqlfluff.core import Linter
+from sqlfluff.core import FluffConfig, Linter
 from sqlfluff.core.linter.fix import apply_fixes, compute_anchor_edit_info
+from sqlfluff.core.plugin import hookimpl
+from sqlfluff.core.plugin.host import get_plugin_manager, purge_plugin_manager
+from sqlfluff.core.templaters import RawTemplater
+from sqlfluff.core.templaters.base import RawFileSlice, TemplatedFileSlice
+from sqlfluff.core.templaters.jinja import JinjaTemplater
 from sqlfluff.utils.reflow.helpers import deduce_line_indent, fixes_from_results
 from sqlfluff.utils.reflow.reindent import (
     _crawl_indent_points,
@@ -28,6 +35,49 @@ def parse_ansi_string(sql, config):
     return linter.parse_string(sql).tree
 
 
+class SpecialMarkerInserter(JinjaTemplater):
+    """Inserts special marker slices in a sliced file.
+
+    Some templater plugins might insert custom marker slices that are of zero source
+    string length, including an empty source string.  This mock templater simulates
+    this behavior by adding a marker slice like this after every block_start slice.
+    """
+
+    name = "special_marker_inserter"
+
+    def slice_file(
+        self, raw_str: str, render_func: Callable[[str], str], config=None, **kwargs
+    ) -> Tuple[List[RawFileSlice], List[TemplatedFileSlice], str]:
+        """Patch a sliced file returned by the superclass."""
+        raw_sliced, sliced_file, templated_str = super().slice_file(
+            raw_str, render_func, config, **kwargs
+        )
+
+        patched_sliced_file = []
+        for templated_slice in sliced_file:
+            patched_sliced_file.append(templated_slice)
+            # Add an EMPTY special_marker slice after every block_start.
+            if templated_slice.slice_type == "block_start":
+                # Note that both the source_slice AND the templated_slice are empty.
+                source_pos = templated_slice.source_slice.stop
+                templated_pos = templated_slice.templated_slice.stop
+                patched_sliced_file.append(
+                    TemplatedFileSlice(
+                        "special_marker",
+                        slice(source_pos, source_pos),
+                        slice(templated_pos, templated_pos),
+                    )
+                )
+
+        return raw_sliced, patched_sliced_file, templated_str
+
+
+@hookimpl
+def get_templaters() -> List[Type[RawTemplater]]:
+    """Return templaters provided by this test module."""
+    return [SpecialMarkerInserter]
+
+
 @pytest.mark.parametrize(
     "raw_sql_in,elem_idx,indent_to,point_sql_out",
     [
@@ -128,11 +178,12 @@ def test_reflow__deduce_line_indent(
 
 
 @pytest.mark.parametrize(
-    "raw_sql_in,points_out",
+    "raw_sql_in,templater,points_out",
     [
         # Trivial
         (
             "select 1",
+            "raw",
             [
                 # No point at the start.
                 # Point after select (not newline)
@@ -159,6 +210,7 @@ def test_reflow__deduce_line_indent(
         ),
         (
             "\nselect 1\n",
+            "raw",
             [
                 # Start point
                 _IndentPoint(
@@ -194,6 +246,7 @@ def test_reflow__deduce_line_indent(
         ),
         (
             "select\n1",
+            "raw",
             [
                 # No point at the start.
                 # Point after select (not newline)
@@ -222,6 +275,7 @@ def test_reflow__deduce_line_indent(
         (
             "SELECT\n    r.a,\n    s.b\nFROM r\nJOIN s\n    "
             "ON\n        r.a = s.a\n        AND true",
+            "raw",
             [
                 # No point at the start.
                 # After SELECT
@@ -335,6 +389,7 @@ def test_reflow__deduce_line_indent(
         ),
         (
             "SELECT *\nFROM t1\nJOIN t2 ON true\nAND true",
+            "raw",
             [
                 # No point at the start.
                 # NOTE: Abbreviated notation given much is the same as above.
@@ -365,6 +420,40 @@ def test_reflow__deduce_line_indent(
                 _IndentPoint(19, -2, -2, 2, 15, False, (1, 2)),
             ],
         ),
+        # Trailing comment case: delays indent until after the comment
+        (
+            "SELECT -- comment\n    1;",
+            "raw",
+            [
+                # No point at the start.
+                # After SELECT
+                _IndentPoint(1, 0, 0, 0, None, False, ()),
+                # After comment
+                _IndentPoint(3, 1, 0, 0, None, True, ()),
+                # After 1
+                _IndentPoint(5, -1, -1, 1, 3, False, ()),
+                # After ;
+                _IndentPoint(7, 0, 0, 0, 3, False, ()),
+            ],
+        ),
+        # Two trailing comments
+        (
+            "SELECT /* first comment */ /* second comment */\n    1;",
+            "raw",
+            [
+                # No point at the start.
+                # After SELECT
+                _IndentPoint(1, 0, 0, 0, None, False, ()),
+                # After first comment
+                _IndentPoint(3, 0, 0, 0, None, False, ()),
+                # After second comment
+                _IndentPoint(5, 1, 0, 0, None, True, ()),
+                # After 1
+                _IndentPoint(7, -1, -1, 1, 5, False, ()),
+                # After ;
+                _IndentPoint(9, 0, 0, 0, 5, False, ()),
+            ],
+        ),
         # Templated case
         (
             "SELECT\n"
@@ -372,6 +461,7 @@ def test_reflow__deduce_line_indent(
             "    {% for c in ['d', 'e'] %}\n"
             "    ,{{ c }}_val\n"
             "    {% endfor %}\n",
+            "jinja",
             [
                 # No initial indent (this is the first newline).
                 _IndentPoint(1, 1, 0, 0, None, True, ()),
@@ -396,6 +486,7 @@ def test_reflow__deduce_line_indent(
             "FROM some_table\n"
             "{{ 'UNION ALL\n' if not loop.last }}\n"
             "{%- endfor %}",
+            "jinja",
             [
                 # No initial indent (this is the first newline).
                 # Importantly this first point - IS a newline
@@ -433,6 +524,7 @@ def test_reflow__deduce_line_indent(
         # Templated case (with templated newline and indent)
         (
             "SELECT\n  {{'1 \n, 2'}}\nFROM foo",
+            "jinja",
             [
                 # After SELECT
                 _IndentPoint(1, 1, 0, 0, None, True, ()),
@@ -445,13 +537,44 @@ def test_reflow__deduce_line_indent(
                 _IndentPoint(11, -1, -1, 1, 7, False, (1,)),
             ],
         ),
+        # Templated case (with special marker slice that has no source string)
+        (
+            # The invisible special marker slice will be inserted immediately after
+            # the first normal template section.
+            "{% if True %}\n    SELECT 1;\n{% endif %}\n",
+            "special_marker_inserter",
+            [
+                # No point at the start.
+                # After the {% if True %} block: this should not yet indent because
+                # there's still the upcoming zero-length special_marker
+                # TemplateSegment.  This is handled similar to the trailing comment
+                # test case.
+                _IndentPoint(1, 0, 0, 0, None, False, ()),
+                # After the zero-length special_marker TemplateSegment inserted by
+                # the special templater: only after this do we want to indent.
+                _IndentPoint(3, 1, 0, 0, None, True, ()),
+                # After SELECT
+                _IndentPoint(5, 1, 0, 1, 3, False, ()),
+                # After 1
+                _IndentPoint(7, -1, -1, 2, 3, False, (2,)),
+                # After ;
+                _IndentPoint(9, -1, -1, 1, 3, True, ()),
+                # After {% endif %}
+                _IndentPoint(11, 0, 0, 0, 9, True, ()),
+            ],
+        ),
     ],
 )
-def test_reflow__crawl_indent_points(raw_sql_in, points_out, default_config, caplog):
+def test_reflow__crawl_indent_points(raw_sql_in, templater, points_out, caplog):
     """Test _crawl_indent_points directly."""
-    root = parse_ansi_string(raw_sql_in, default_config)
+    # Register the mock templater in this module.
+    purge_plugin_manager()
+    get_plugin_manager().register(sys.modules[__name__], name="reindent_test")
+
+    config = FluffConfig(overrides={"dialect": "ansi", "templater": templater})
+    root = parse_ansi_string(raw_sql_in, config)
     print(root.stringify())
-    seq = ReflowSequence.from_root(root, config=default_config)
+    seq = ReflowSequence.from_root(root, config=config)
     with caplog.at_level(logging.DEBUG, logger="sqlfluff.rules.reflow"):
         points = list(_crawl_indent_points(seq.elements))
     assert points == points_out