Repository: sqlfluff/sqlfluff
Pull request: Postgres - Support parameters and identifiers prepended with _ and containing $
### Brief summary of the change made
Patched code lexer to allow $ (as long as it doesn't start with this)
Patched Parameter and NakedIdentifier segments to allow them to start with _, and contain $

Fixes #1742

### Are there any other side effects of this change that we should be aware of?
No

### Pull Request checklist
- [ ] Please confirm you have completed any of the necessary steps below.

- Included test cases to demonstrate any code changes, which may be one or more of the following:
Added test cases

Problem statement:
Postgres - Parsing failure for identifiers starting with _
Thanks for this great linter and actively developing and maintaining it!

## Expected Behaviour

Linter should run through and not fail.

## Observed Behaviour

```
L:   1 | P:   1 |  PRS | Line 1, Position 1: Found unparsable section: 'CREATE OR REPLACE FUNCTION public.foo(_a...'
```

## Steps to Reproduce

```sql
CREATE OR REPLACE FUNCTION public.foo(_a TEXT, _b INT)
RETURNS FLOAT AS
$$
  RETURN 0.0
$$ LANGUAGE plpgsql STABLE PARALLEL SAFE;

```

## Dialect

PostgreSQL

## Version

* sqlfluff, version 0.7.1
* Python 3.9.7

Hints from issue discussion:
Hey @xamanu, root cause isn't a lack of support for functions, it's a parsing issue with parameters prepended with `_`, PR up to fix, will rename appropriately

Commit message: Fixed greediness of naked identifier parser

Patch to review:
diff --git a/src/sqlfluff/dialects/dialect_postgres.py b/src/sqlfluff/dialects/dialect_postgres.py
index b52609d59c2..1c0598ba6d0 100644
--- a/src/sqlfluff/dialects/dialect_postgres.py
+++ b/src/sqlfluff/dialects/dialect_postgres.py
@@ -11,12 +11,14 @@
     BaseSegment,
     Delimited,
     RegexLexer,
+    RegexParser,
     CodeSegment,
     NamedParser,
     SymbolSegment,
     StartsWith,
     CommentSegment,
     Dedent,
+    SegmentGenerator,
 )
 
 from sqlfluff.core.dialects import load_raw_dialect
@@ -114,6 +116,7 @@
         ),
         # In Postgres, there is no escape character for double quote strings
         RegexLexer("double_quote", r'(?s)".+?"', CodeSegment),
+        RegexLexer("code", r"[0-9a-zA-Z_]+[0-9a-zA-Z_$]*", CodeSegment),
     ]
 )
 
@@ -145,6 +148,19 @@
 )
 
 postgres_dialect.replace(
+    NakedIdentifierSegment=SegmentGenerator(
+        # Generate the anti template from the set of reserved keywords
+        lambda dialect: RegexParser(
+            r"([A-Z_]+|[0-9]*[A-Z_$])[A-Z0-9_$]*",
+            CodeSegment,
+            name="naked_identifier",
+            type="identifier",
+            anti_template=r"^(" + r"|".join(dialect.sets("reserved_keywords")) + r")$",
+        )
+    ),
+    ParameterNameSegment=RegexParser(
+        r"[A-Z_][A-Z0-9_$]*", CodeSegment, name="parameter", type="parameter"
+    ),
     QuotedLiteralSegment=OneOf(
         NamedParser("single_quote", CodeSegment, name="quoted_literal", type="literal"),
         NamedParser(
diff --git a/test/fixtures/dialects/postgres/postgres_create_function.sql b/test/fixtures/dialects/postgres/postgres_create_function.sql
index 6434e0f2213..194ba84a38e 100644
--- a/test/fixtures/dialects/postgres/postgres_create_function.sql
+++ b/test/fixtures/dialects/postgres/postgres_create_function.sql
@@ -101,3 +101,9 @@ AS $function$
 select unnest(array['hi', 'test'])
 $function$
 ;
+
+CREATE OR REPLACE FUNCTION public.foo(_a TEXT, _$b INT)
+RETURNS FLOAT AS
+$$
+  RETURN 0.0
+$$ LANGUAGE plpgsql STABLE PARALLEL SAFE;
diff --git a/test/fixtures/dialects/postgres/postgres_create_function.yml b/test/fixtures/dialects/postgres/postgres_create_function.yml
index cee466f92f0..73796520894 100644
--- a/test/fixtures/dialects/postgres/postgres_create_function.yml
+++ b/test/fixtures/dialects/postgres/postgres_create_function.yml
@@ -3,7 +3,7 @@
 # computed by SQLFluff when running the tests. Please run
 # `python test/generate_parse_fixture_yml.py`  to generate them after adding or
 # altering SQL files.
-_hash: eac5ff2a6ac7137c4ecd4296a967e807932fb33ec25b347b8676442e7e7ceec1
+_hash: 207220496dc0af0254ddebfdca35ea6e961feb092d80b417c0d5da7c140fe5ec
 file:
 - statement:
     create_function_statement:
@@ -538,3 +538,36 @@ file:
       - keyword: AS
       - literal: "$function$\nselect unnest(array['hi', 'test'])\n$function$"
 - statement_terminator: ;
+- statement:
+    create_function_statement:
+    - keyword: CREATE
+    - binary_operator: OR
+    - keyword: REPLACE
+    - keyword: FUNCTION
+    - function_name:
+        identifier: public
+        dot: .
+        function_name_identifier: foo
+    - base:
+        bracketed:
+        - start_bracket: (
+        - parameter: _a
+        - data_type:
+            data_type_identifier: TEXT
+        - comma: ','
+        - parameter: _$b
+        - data_type:
+            data_type_identifier: INT
+        - end_bracket: )
+    - keyword: RETURNS
+    - data_type:
+        data_type_identifier: FLOAT
+    - base:
+      - keyword: AS
+      - literal: "$$\n  RETURN 0.0\n$$"
+      - keyword: LANGUAGE
+      - parameter: plpgsql
+      - keyword: STABLE
+      - keyword: PARALLEL
+      - keyword: SAFE
+- statement_terminator: ;


Code review comments:
diff_hunk: @@ -145,6 +148,19 @@
 )
 
 postgres_dialect.replace(
+    NakedIdentifierSegment=SegmentGenerator(
+        # Generate the anti template from the set of reserved keywords
+        lambda dialect: RegexParser(
+            r"([A-Z_]+|[0-9]*[A-Z_$])[A-Z0-9_$]*",
path: src/sqlfluff/dialects/dialect_postgres.py
text: @user1:
This still seems a little weird to me.

So it's either:
- Beginning with `A-Z` or `_`
- Or beginning with `0-9` (maybe - as it's a star) followed by `A-Z` or `_` or `$`

So questions from me:

Is it OK to begin with 0-9 or A-Z or `_` or `$`? If so why not say so?


@author:
Ah you're right - off my game today it seems. The only one it isn't okay to begin with is $