Repository: sqlfluff/sqlfluff
Pull request: Improve function support in Postgres dialect
### Brief summary of the change made
This is an enhancement of the FunctionDefinitionGrammar class for Postgres. It is comprehensive to allow all of the ending keywords, regardless of ordering.

Fixes #1288.

Includes all function endings as specified at https://www.postgresql.org/docs/9.1/sql-createfunction.html 
...

### Are there any other side effects of this change that we should be aware of?
No

### Pull Request checklist
Tests have been enhanced in postgres_create_function.sql to cover the changes. 


Problem statement:
Add support for function endings in dialect `postgres`
Defining functions in Postgres and ending the with some typical indications:

```
[...]
LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE
```
or variations, such as:
```
[...]
LANGUAGE plpgsql STABLE PARALLEL SAFE
```


Executed with

```
sqlfluff lint --dialect postgres
```

causes following error:

```
L:  71 | P:  17 |  PRS | Line 71, Position 17: Found unparsable section: '
                       | IMMUTABLE PARALLEL SAFE'
```

It would be nice to get this added. Many thanks for the nice linter!

Hints from issue discussion:

Commit message: Upgraded postgres dialect functions to allow more complex inputs and outputs

Patch to review:
diff --git a/src/sqlfluff/dialects/dialect_ansi.py b/src/sqlfluff/dialects/dialect_ansi.py
index a163aad4b5f..38e5d8e95bf 100644
--- a/src/sqlfluff/dialects/dialect_ansi.py
+++ b/src/sqlfluff/dialects/dialect_ansi.py
@@ -87,7 +87,7 @@
         RegexLexer("double_quote", r'"([^"\\]|\\.)*"', CodeSegment),
         RegexLexer("back_quote", r"`[^`]*`", CodeSegment),
         # See https://www.geeksforgeeks.org/postgresql-dollar-quoted-string-constants/
-        RegexLexer("dollar_quote", r"\$(\w*)\$[^\1]*\$\1\$", CodeSegment),
+        RegexLexer("dollar_quote", r"\$(\w*)\$[^\1]*?\$\1\$", CodeSegment),
         RegexLexer(
             "numeric_literal", r"(\d+(\.\d+)?|\.\d+)([eE][+-]?\d+)?", CodeSegment
         ),
diff --git a/src/sqlfluff/dialects/dialect_postgres.py b/src/sqlfluff/dialects/dialect_postgres.py
index 974f1cb440c..f332234b73b 100644
--- a/src/sqlfluff/dialects/dialect_postgres.py
+++ b/src/sqlfluff/dialects/dialect_postgres.py
@@ -2,6 +2,7 @@
 
 from sqlfluff.core.parser import (
     OneOf,
+    AnyNumberOf,
     Ref,
     Sequence,
     Bracketed,
@@ -20,7 +21,6 @@
 
 postgres_dialect = ansi_dialect.copy_as("postgres")
 
-
 postgres_dialect.insert_lexer_matchers(
     # JSON Operators: https://www.postgresql.org/docs/9.5/functions-json.html
     [
@@ -33,7 +33,6 @@
     before="not_equal",
 )
 
-
 # https://www.postgresql.org/docs/current/sql-keywords-appendix.html
 # SPACE has special status in some SQL dialects, but not Postgres.
 postgres_dialect.sets("unreserved_keywords").remove("SPACE")
@@ -50,10 +49,13 @@
         "XML",
     ]
 )
-postgres_dialect.sets("reserved_keywords").add("WITHIN")
+postgres_dialect.sets("reserved_keywords").update(["WITHIN", "VARIADIC"])
 # Add the EPOCH datetime unit
 postgres_dialect.sets("datetime_units").update(["EPOCH"])
 
+postgres_dialect.sets("unreserved_keywords").update(
+    ["COST", "LEAKPROOF", "PARALLEL", "SUPPORT", "SAFE", "UNSAFE", "RESTRICTED"]
+)
 
 postgres_dialect.add(
     JsonOperatorSegment=NamedParser(
@@ -64,7 +66,6 @@
     ),
 )
 
-
 postgres_dialect.replace(
     PostFunctionGrammar=OneOf(
         Ref("WithinGroupClauseSegment"),
@@ -83,20 +84,135 @@
         # Add JSON operators
         Ref("JsonOperatorSegment"),
     ),
+    FunctionParameterGrammar=Sequence(
+        OneOf("IN", "OUT", "INOUT", "VARIADIC", optional=True),
+        OneOf(
+            Ref("DatatypeSegment"),
+            Sequence(Ref("ParameterNameSegment"), Ref("DatatypeSegment")),
+        ),
+        Sequence(
+            OneOf("DEFAULT", Ref("EqualsSegment")), Ref("LiteralGrammer"), optional=True
+        ),
+    ),
 )
 
 
+@postgres_dialect.segment(replace=True)
+class CreateFunctionStatementSegment(BaseSegment):
+    """A `CREATE FUNCTION` statement.
+
+    This version in the ANSI dialect should be a "common subset" of the
+    structure of the code for those dialects.
+    postgres: https://www.postgresql.org/docs/13/sql-createfunction.html
+    """
+
+    type = "create_function_statement"
+
+    match_grammar = Sequence(
+        "CREATE",
+        Sequence("OR", "REPLACE", optional=True),
+        Ref("TemporaryGrammar", optional=True),
+        "FUNCTION",
+        Anything(),
+    )
+
+    parse_grammar = Sequence(
+        "CREATE",
+        Sequence("OR", "REPLACE", optional=True),
+        Ref("TemporaryGrammar", optional=True),
+        "FUNCTION",
+        Sequence("IF", "NOT", "EXISTS", optional=True),
+        Ref("FunctionNameSegment"),
+        Ref("FunctionParameterListGrammar"),
+        Sequence(  # Optional function return type
+            "RETURNS",
+            OneOf(
+                Sequence(
+                    "TABLE",
+                    Bracketed(
+                        Delimited(
+                            OneOf(
+                                Ref("DatatypeSegment"),
+                                Sequence(
+                                    Ref("ParameterNameSegment"), Ref("DatatypeSegment")
+                                ),
+                            ),
+                            delimiter=Ref("CommaSegment"),
+                        )
+                    ),
+                    optional=True,
+                ),
+                Ref("DatatypeSegment"),
+            ),
+            optional=True,
+        ),
+        Ref("FunctionDefinitionGrammar"),
+    )
+
+
 @postgres_dialect.segment(replace=True)
 class FunctionDefinitionGrammar(BaseSegment):
-    """This is the body of a `CREATE FUNCTION AS` statement."""
+    """This is the body of a `CREATE FUNCTION AS` statement.
+
+    Options supported as defined in https://www.postgresql.org/docs/13/sql-createfunction.html
+    """
 
     match_grammar = Sequence(
-        "AS",
-        OneOf(Ref("QuotedLiteralSegment"), Ref("DollarQuotedLiteralSegment")),
+        AnyNumberOf(
+            Sequence("LANGUAGE", Ref("ParameterNameSegment")),
+            Sequence("TRANSFORM", "FOR", "TYPE", Ref("ParameterNameSegment")),
+            Ref.keyword("WINDOW"),
+            OneOf("IMMUTABLE", "STABLE", "VOLATILE"),
+            Sequence(Ref.keyword("NOT", optional=True), "LEAKPROOF"),
+            OneOf(
+                Sequence("CALLED", "ON", "NULL", "INPUT"),
+                Sequence("RETURNS", "NULL", "ON", "NULL", "INPUT"),
+                "STRICT",
+            ),
+            Sequence(
+                Ref.keyword("EXTERNAL", optional=True),
+                "SECURITY",
+                OneOf("INVOKER", "DEFINER"),
+            ),
+            Sequence("PARALLEL", OneOf("UNSAFE", "RESTRICTED", "SAFE")),
+            Sequence("COST", Ref("NumericLiteralSegment")),
+            Sequence("ROWS", Ref("NumericLiteralSegment")),
+            Sequence("SUPPORT", Ref("ParameterNameSegment")),
+            Sequence(
+                "SET",
+                Ref("ParameterNameSegment"),
+                OneOf(
+                    Sequence(
+                        OneOf("TO", Ref("EqualsSegment")),
+                        Delimited(
+                            OneOf(
+                                Ref("ParameterNameSegment"),
+                                Ref("LiteralGrammar"),
+                            ),
+                            delimiter=Ref("CommaSegment"),
+                        ),
+                    ),
+                    Sequence("FROM", "CURRENT"),
+                ),
+            ),
+            Sequence(
+                "AS",
+                OneOf(
+                    Ref("QuotedLiteralSegment"),
+                    Ref("DollarQuotedLiteralSegment"),
+                    Sequence(
+                        Ref("QuotedLiteralSegment"),
+                        Ref("CommaSegment"),
+                        Ref("QuotedLiteralSegment"),
+                    ),
+                ),
+            ),
+        ),
         Sequence(
-            "LANGUAGE",
-            # Not really a parameter, but best fit for now.
-            Ref("ParameterNameSegment"),
+            "WITH",
+            Bracketed(
+                Delimited(Ref("ParameterNameSegment"), delimiter=Ref("CommaSegment"))
+            ),
             optional=True,
         ),
     )
diff --git a/test/fixtures/parser/postgres/postgres_create_function.sql b/test/fixtures/parser/postgres/postgres_create_function.sql
index 7a412b65e9e..e3c7c38f91e 100644
--- a/test/fixtures/parser/postgres/postgres_create_function.sql
+++ b/test/fixtures/parser/postgres/postgres_create_function.sql
@@ -8,4 +8,87 @@ CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS '
     BEGIN
         RETURN i + 1;
     END;
-' LANGUAGE plpgsql;
+' LANGUAGE plpgsql VOLATILE;
+
+CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS '
+    BEGIN
+        RETURN i + 1;
+    END;
+' LANGUAGE plpgsql WINDOW IMMUTABLE STABLE LEAKPROOF RETURNS NULL ON NULL INPUT EXTERNAL SECURITY DEFINER
+ROWS 5 SET test_param = 3;
+
+CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS 'C:\\my_file.c', 'symlink_c'
+LANGUAGE plpgsql WINDOW IMMUTABLE STABLE NOT LEAKPROOF CALLED ON NULL INPUT EXTERNAL SECURITY DEFINER COST 123
+ROWS 5 SET test_param = 3 WITH (isStrict);
+
+CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer PARALLEL UNSAFE AS $$
+    BEGIN
+        RETURN i + 1;
+    END;
+$$ LANGUAGE plpgsql SUPPORT my_function;
+
+CREATE FUNCTION add(integer, integer) RETURNS integer
+    AS 'select $1 + $2;'
+    LANGUAGE SQL
+    IMMUTABLE
+    RETURNS NULL ON NULL INPUT;
+
+CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS $$
+        BEGIN
+                RETURN i + 1;
+        END;
+$$ LANGUAGE plpgsql;
+
+
+CREATE FUNCTION dup(in int, out f1 int, out f2 text)
+    AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
+    LANGUAGE SQL;
+
+SELECT * FROM dup(42);
+
+
+CREATE TYPE dup_result AS (f1 int, f2 text);
+
+CREATE FUNCTION dup(int) RETURNS dup_result
+    AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
+    LANGUAGE SQL;
+
+SELECT * FROM dup(42);
+
+CREATE FUNCTION dup(int) RETURNS TABLE(f1 int, f2 text)
+    AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
+    LANGUAGE SQL;
+
+SELECT * FROM dup(42);
+
+CREATE FUNCTION check_password(uname TEXT, pass TEXT)
+RETURNS BOOLEAN AS $$
+DECLARE passed BOOLEAN;
+BEGIN
+        SELECT  (pwd = $2) INTO passed
+        FROM    pwds
+        WHERE   username = $1;
+
+        RETURN passed;
+END;
+$$  LANGUAGE plpgsql
+    SECURITY DEFINER
+    SET search_path = admin, pg_temp;
+
+
+BEGIN;
+CREATE FUNCTION check_password(uname TEXT, pass TEXT)
+RETURNS BOOLEAN AS $$
+DECLARE passed BOOLEAN;
+BEGIN
+        SELECT  (pwd = $2) INTO passed
+        FROM    pwds
+        WHERE   username = $1;
+
+        RETURN passed;
+END;
+$$  LANGUAGE plpgsql
+    SECURITY DEFINER;
+REVOKE ALL ON FUNCTION check_password(uname TEXT, pass TEXT) FROM PUBLIC;
+GRANT EXECUTE ON FUNCTION check_password(uname TEXT, pass TEXT) TO admins;
+COMMIT;
\ No newline at end of file
diff --git a/test/fixtures/parser/postgres/postgres_create_function.yml b/test/fixtures/parser/postgres/postgres_create_function.yml
index 81d5791a7fe..c1d914959bd 100644
--- a/test/fixtures/parser/postgres/postgres_create_function.yml
+++ b/test/fixtures/parser/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: 88b9dce529a554c7650abe72b389538057a83f3854f5f84d3e4b3261829ce83c
+_hash: 5d503776245af71e84cb00d1487c4f06f0b6ab9d6307478e0a84d9207a693262
 file:
 - statement:
     create_function_statement:
@@ -52,4 +52,463 @@ file:
       - literal: "'\n    BEGIN\n        RETURN i + 1;\n    END;\n'"
       - keyword: LANGUAGE
       - parameter: plpgsql
+      - keyword: VOLATILE
+- statement_terminator: ;
+- statement:
+    create_function_statement:
+    - keyword: CREATE
+    - binary_operator: OR
+    - keyword: REPLACE
+    - keyword: FUNCTION
+    - function_name:
+        function_name_identifier: increment
+    - base:
+        bracketed:
+          start_bracket: (
+          parameter: i
+          data_type:
+            data_type_identifier: integer
+          end_bracket: )
+    - keyword: RETURNS
+    - data_type:
+        data_type_identifier: integer
+    - base:
+      - keyword: AS
+      - literal: "'\n    BEGIN\n        RETURN i + 1;\n    END;\n'"
+      - keyword: LANGUAGE
+      - parameter: plpgsql
+      - keyword: WINDOW
+      - keyword: IMMUTABLE
+      - keyword: STABLE
+      - keyword: LEAKPROOF
+      - keyword: RETURNS
+      - keyword: 'NULL'
+      - keyword: 'ON'
+      - keyword: 'NULL'
+      - keyword: INPUT
+      - keyword: EXTERNAL
+      - keyword: SECURITY
+      - keyword: DEFINER
+      - keyword: ROWS
+      - literal: '5'
+      - keyword: SET
+      - parameter: test_param
+      - comparison_operator: '='
+      - literal: '3'
+- statement_terminator: ;
+- statement:
+    create_function_statement:
+    - keyword: CREATE
+    - binary_operator: OR
+    - keyword: REPLACE
+    - keyword: FUNCTION
+    - function_name:
+        function_name_identifier: increment
+    - base:
+        bracketed:
+          start_bracket: (
+          parameter: i
+          data_type:
+            data_type_identifier: integer
+          end_bracket: )
+    - keyword: RETURNS
+    - data_type:
+        data_type_identifier: integer
+    - base:
+      - keyword: AS
+      - literal: "'C:\\\\my_file.c'"
+      - comma: ','
+      - literal: "'symlink_c'"
+      - keyword: LANGUAGE
+      - parameter: plpgsql
+      - keyword: WINDOW
+      - keyword: IMMUTABLE
+      - keyword: STABLE
+      - keyword: NOT
+      - keyword: LEAKPROOF
+      - keyword: CALLED
+      - keyword: 'ON'
+      - keyword: 'NULL'
+      - keyword: INPUT
+      - keyword: EXTERNAL
+      - keyword: SECURITY
+      - keyword: DEFINER
+      - keyword: COST
+      - literal: '123'
+      - keyword: ROWS
+      - literal: '5'
+      - keyword: SET
+      - parameter: test_param
+      - comparison_operator: '='
+      - literal: '3'
+      - keyword: WITH
+      - bracketed:
+          start_bracket: (
+          parameter: isStrict
+          end_bracket: )
+- statement_terminator: ;
+- statement:
+    create_function_statement:
+    - keyword: CREATE
+    - binary_operator: OR
+    - keyword: REPLACE
+    - keyword: FUNCTION
+    - function_name:
+        function_name_identifier: increment
+    - base:
+        bracketed:
+          start_bracket: (
+          parameter: i
+          data_type:
+            data_type_identifier: integer
+          end_bracket: )
+    - keyword: RETURNS
+    - data_type:
+        data_type_identifier: integer
+    - base:
+      - keyword: PARALLEL
+      - keyword: UNSAFE
+      - keyword: AS
+      - literal: "$$\n    BEGIN\n        RETURN i + 1;\n    END;\n$$"
+      - keyword: LANGUAGE
+      - parameter: plpgsql
+      - keyword: SUPPORT
+      - parameter: my_function
+- statement_terminator: ;
+- statement:
+    create_function_statement:
+    - keyword: CREATE
+    - keyword: FUNCTION
+    - function_name:
+        function_name_identifier: add
+    - base:
+        bracketed:
+        - start_bracket: (
+        - data_type:
+            data_type_identifier: integer
+        - comma: ','
+        - data_type:
+            data_type_identifier: integer
+        - end_bracket: )
+    - keyword: RETURNS
+    - data_type:
+        data_type_identifier: integer
+    - base:
+      - keyword: AS
+      - literal: "'select $1 + $2;'"
+      - keyword: LANGUAGE
+      - parameter: SQL
+      - keyword: IMMUTABLE
+      - keyword: RETURNS
+      - keyword: 'NULL'
+      - keyword: 'ON'
+      - keyword: 'NULL'
+      - keyword: INPUT
+- statement_terminator: ;
+- statement:
+    create_function_statement:
+    - keyword: CREATE
+    - binary_operator: OR
+    - keyword: REPLACE
+    - keyword: FUNCTION
+    - function_name:
+        function_name_identifier: increment
+    - base:
+        bracketed:
+          start_bracket: (
+          parameter: i
+          data_type:
+            data_type_identifier: integer
+          end_bracket: )
+    - keyword: RETURNS
+    - data_type:
+        data_type_identifier: integer
+    - base:
+      - keyword: AS
+      - literal: "$$\n        BEGIN\n                RETURN i + 1;\n        END;\n\
+          $$"
+      - keyword: LANGUAGE
+      - parameter: plpgsql
+- statement_terminator: ;
+- statement:
+    create_function_statement:
+    - keyword: CREATE
+    - keyword: FUNCTION
+    - function_name:
+        function_name_identifier: dup
+    - base:
+        bracketed:
+        - start_bracket: (
+        - keyword: in
+        - data_type:
+            data_type_identifier: int
+        - comma: ','
+        - keyword: out
+        - parameter: f1
+        - data_type:
+            data_type_identifier: int
+        - comma: ','
+        - keyword: out
+        - parameter: f2
+        - data_type:
+            data_type_identifier: text
+        - end_bracket: )
+    - base:
+      - keyword: AS
+      - literal: "$$ SELECT $1, CAST($1 AS text) || ' is text' $$"
+      - keyword: LANGUAGE
+      - parameter: SQL
+- statement_terminator: ;
+- statement:
+    select_statement:
+      select_clause:
+        keyword: SELECT
+        select_clause_element:
+          wildcard_expression:
+            wildcard_identifier:
+              star: '*'
+      from_clause:
+        keyword: FROM
+        from_expression:
+          from_expression_element:
+            table_expression:
+              function:
+                function_name:
+                  function_name_identifier: dup
+                bracketed:
+                  start_bracket: (
+                  expression:
+                    literal: '42'
+                  end_bracket: )
+- statement_terminator: ;
+- statement:
+    create_type_statement:
+    - keyword: CREATE
+    - keyword: TYPE
+    - object_reference:
+        identifier: dup_result
+    - keyword: AS
+    - bracketed:
+      - start_bracket: (
+      - raw: f1
+      - raw: int
+      - comma: ','
+      - raw: f2
+      - raw: text
+      - end_bracket: )
+- statement_terminator: ;
+- statement:
+    create_function_statement:
+    - keyword: CREATE
+    - keyword: FUNCTION
+    - function_name:
+        function_name_identifier: dup
+    - base:
+        bracketed:
+          start_bracket: (
+          data_type:
+            data_type_identifier: int
+          end_bracket: )
+    - keyword: RETURNS
+    - data_type:
+        data_type_identifier: dup_result
+    - base:
+      - keyword: AS
+      - literal: "$$ SELECT $1, CAST($1 AS text) || ' is text' $$"
+      - keyword: LANGUAGE
+      - parameter: SQL
+- statement_terminator: ;
+- statement:
+    select_statement:
+      select_clause:
+        keyword: SELECT
+        select_clause_element:
+          wildcard_expression:
+            wildcard_identifier:
+              star: '*'
+      from_clause:
+        keyword: FROM
+        from_expression:
+          from_expression_element:
+            table_expression:
+              function:
+                function_name:
+                  function_name_identifier: dup
+                bracketed:
+                  start_bracket: (
+                  expression:
+                    literal: '42'
+                  end_bracket: )
+- statement_terminator: ;
+- statement:
+    create_function_statement:
+    - keyword: CREATE
+    - keyword: FUNCTION
+    - function_name:
+        function_name_identifier: dup
+    - base:
+        bracketed:
+          start_bracket: (
+          data_type:
+            data_type_identifier: int
+          end_bracket: )
+    - keyword: RETURNS
+    - keyword: TABLE
+    - bracketed:
+      - start_bracket: (
+      - parameter: f1
+      - data_type:
+          data_type_identifier: int
+      - comma: ','
+      - parameter: f2
+      - data_type:
+          data_type_identifier: text
+      - end_bracket: )
+    - base:
+      - keyword: AS
+      - literal: "$$ SELECT $1, CAST($1 AS text) || ' is text' $$"
+      - keyword: LANGUAGE
+      - parameter: SQL
+- statement_terminator: ;
+- statement:
+    select_statement:
+      select_clause:
+        keyword: SELECT
+        select_clause_element:
+          wildcard_expression:
+            wildcard_identifier:
+              star: '*'
+      from_clause:
+        keyword: FROM
+        from_expression:
+          from_expression_element:
+            table_expression:
+              function:
+                function_name:
+                  function_name_identifier: dup
+                bracketed:
+                  start_bracket: (
+                  expression:
+                    literal: '42'
+                  end_bracket: )
+- statement_terminator: ;
+- statement:
+    create_function_statement:
+    - keyword: CREATE
+    - keyword: FUNCTION
+    - function_name:
+        function_name_identifier: check_password
+    - base:
+        bracketed:
+        - start_bracket: (
+        - parameter: uname
+        - data_type:
+            data_type_identifier: TEXT
+        - comma: ','
+        - parameter: pass
+        - data_type:
+            data_type_identifier: TEXT
+        - end_bracket: )
+    - keyword: RETURNS
+    - data_type:
+        data_type_identifier: BOOLEAN
+    - base:
+      - keyword: AS
+      - literal: "$$\nDECLARE passed BOOLEAN;\nBEGIN\n        SELECT  (pwd = $2) INTO\
+          \ passed\n        FROM    pwds\n        WHERE   username = $1;\n\n     \
+          \   RETURN passed;\nEND;\n$$"
+      - keyword: LANGUAGE
+      - parameter: plpgsql
+      - keyword: SECURITY
+      - keyword: DEFINER
+      - keyword: SET
+      - parameter: search_path
+      - comparison_operator: '='
+      - parameter: admin
+      - comma: ','
+      - parameter: pg_temp
+- statement_terminator: ;
+- statement:
+    transaction_statement:
+      keyword: BEGIN
+- statement_terminator: ;
+- statement:
+    create_function_statement:
+    - keyword: CREATE
+    - keyword: FUNCTION
+    - function_name:
+        function_name_identifier: check_password
+    - base:
+        bracketed:
+        - start_bracket: (
+        - parameter: uname
+        - data_type:
+            data_type_identifier: TEXT
+        - comma: ','
+        - parameter: pass
+        - data_type:
+            data_type_identifier: TEXT
+        - end_bracket: )
+    - keyword: RETURNS
+    - data_type:
+        data_type_identifier: BOOLEAN
+    - base:
+      - keyword: AS
+      - literal: "$$\nDECLARE passed BOOLEAN;\nBEGIN\n        SELECT  (pwd = $2) INTO\
+          \ passed\n        FROM    pwds\n        WHERE   username = $1;\n\n     \
+          \   RETURN passed;\nEND;\n$$"
+      - keyword: LANGUAGE
+      - parameter: plpgsql
+      - keyword: SECURITY
+      - keyword: DEFINER
+- statement_terminator: ;
+- statement:
+    access_statement:
+    - keyword: REVOKE
+    - keyword: ALL
+    - keyword: 'ON'
+    - keyword: FUNCTION
+    - object_reference:
+        identifier: check_password
+    - base:
+        bracketed:
+        - start_bracket: (
+        - parameter: uname
+        - data_type:
+            data_type_identifier: TEXT
+        - comma: ','
+        - parameter: pass
+        - data_type:
+            data_type_identifier: TEXT
+        - end_bracket: )
+    - keyword: FROM
+    - object_reference:
+        identifier: PUBLIC
+- statement_terminator: ;
+- statement:
+    access_statement:
+    - keyword: GRANT
+    - keyword: EXECUTE
+    - keyword: 'ON'
+    - keyword: FUNCTION
+    - object_reference:
+        identifier: check_password
+    - base:
+        bracketed:
+        - start_bracket: (
+        - parameter: uname
+        - data_type:
+            data_type_identifier: TEXT
+        - comma: ','
+        - parameter: pass
+        - data_type:
+            data_type_identifier: TEXT
+        - end_bracket: )
+    - keyword: TO
+    - object_reference:
+        identifier: admins
+- statement_terminator: ;
+- statement:
+    transaction_statement:
+      keyword: COMMIT
 - statement_terminator: ;


Code review comments:
diff_hunk: @@ -8,4 +8,87 @@ CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS '
     BEGIN
         RETURN i + 1;
     END;
-' LANGUAGE plpgsql;
+' LANGUAGE plpgsql VOLATILE;
+
+CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS '
+    BEGIN
+        RETURN i + 1;
+    END;
+' LANGUAGE plpgsql WINDOW IMMUTABLE STABLE LEAKPROOF RETURNS NULL ON NULL INPUT EXTERNAL SECURITY DEFINER
+ROWS 5 SET test_param = 3;
+
+CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS 'C:\\my_file.c', 'symlink_c'
+LANGUAGE plpgsql WINDOW IMMUTABLE STABLE NOT LEAKPROOF CALLED ON NULL INPUT EXTERNAL SECURITY DEFINER COST 123
+ROWS 5 SET test_param = 3 WITH (isStrict);
+
+CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer PARALLEL UNSAFE AS $$
+    BEGIN
+        RETURN i + 1;
+    END;
+$$ LANGUAGE plpgsql SUPPORT my_function;
+
+CREATE FUNCTION add(integer, integer) RETURNS integer
+    AS 'select $1 + $2;'
+    LANGUAGE SQL
+    IMMUTABLE
+    RETURNS NULL ON NULL INPUT;
+
+CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS $$
+        BEGIN
+                RETURN i + 1;
+        END;
+$$ LANGUAGE plpgsql;
+
+
+CREATE FUNCTION dup(in int, out f1 int, out f2 text)
+    AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
+    LANGUAGE SQL;
+
+SELECT * FROM dup(42);
+
+
+CREATE TYPE dup_result AS (f1 int, f2 text);
+
+CREATE FUNCTION dup(int) RETURNS dup_result
+    AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
+    LANGUAGE SQL;
+
+SELECT * FROM dup(42);
+
+CREATE FUNCTION dup(int) RETURNS TABLE(f1 int, f2 text)
+    AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
+    LANGUAGE SQL;
+
+SELECT * FROM dup(42);
+
+CREATE FUNCTION check_password(uname TEXT, pass TEXT)
+RETURNS BOOLEAN AS $$
+DECLARE passed BOOLEAN;
+BEGIN
+        SELECT  (pwd = $2) INTO passed
+        FROM    pwds
+        WHERE   username = $1;
+
+        RETURN passed;
+END;
+$$  LANGUAGE plpgsql
+    SECURITY DEFINER
+    SET search_path = admin, pg_temp;
+
+
+BEGIN;
+CREATE FUNCTION check_password(uname TEXT, pass TEXT)
+RETURNS BOOLEAN AS $$
+DECLARE passed BOOLEAN;
+BEGIN
+        SELECT  (pwd = $2) INTO passed
+        FROM    pwds
+        WHERE   username = $1;
+
+        RETURN passed;
+END;
+$$  LANGUAGE plpgsql
+    SECURITY DEFINER;
+REVOKE ALL ON FUNCTION check_password(uname TEXT, pass TEXT) FROM PUBLIC;
+GRANT EXECUTE ON FUNCTION check_password(uname TEXT, pass TEXT) TO admins;
+COMMIT;
path: test/fixtures/parser/postgres/postgres_create_function.sql
text: @user1:
Nit:

```suggestion
COMMIT;

```