[BUG] JSQLParser 5.4-SNAPSHOT : Postgresql : Parser considers subsequent "CREATE FUNCTION" statements as a single statement

[LouizFC]

Always check against the Latest SNAPSHOT of JSQLParser and the Syntax Diagram

Failing SQL Feature:

• What I encountered is not exactly an error, because the parser does not throw an exception, but subsequent CREATE FUNCTION statements are put into a single CreateFunction object, with everything in-between the start of the first one until the end of the last one becoming part of CreateFunction#getFunctionDeclarationParts.

SQL Example:

• Simplified Query Example, focusing on the failing feature

create table if not exists test_table (
  id bigint not null
);

create or replace function test_fn_1(
  target text,
  characters text
) returns boolean as $$
  select trim($2 from $1) <> $1
$$ language sql immutable;

create or replace function test_fn_2(
  target text,
  characters text
) returns boolean as $$
  select position(repeat(first_char, 2) in translate(
    $1, $2, repeat(first_char, length($2))
  )) > 0
  from (values (left($2, 1))) params(first_char)
$$ language sql immutable;

create table if not exists test_table_2 (
  id bigint not null
);

Software Information:

• JSqlParser version 5.4-SNAPSHOT
• Database: PostgreSQL

Extra info

The AST can be seem here, the parser consider both functions one single "node"/"statement", while the create table statements are parsed correctly

[manticore-projects]

You are certainly correct, parsing Function or Procedure is extremely weak and not in our scope.
However, PRs and improvements are welcome.

[LouizFC]

I was searching for similar issues and found that "WITH FUNCTION" syntax was recently implemented, which makes it easier for implementing a PR in the future.

I found a workaround. By not using dolar sign ($) when refering to the function parameters, the parser behaves normally, as can be seen here

create table if not exists test_table (
  id bigint not null
);

create or replace function test_fn_1(
  target text,
  characters text
) returns boolean as $$
  select trim(test_fn_1.characters from test_fn_1.target) <> test_fn_1.target
$$ language sql immutable;

create or replace function test_fn_2(
  target text,
  characters text
) returns boolean as $$
  select position(repeat(first_char, 2) in translate(
    test_fn_2.target, test_fn_2.characters, repeat(first_char, length(test_fn_2.characters))
  )) > 0
  from (values (left(test_fn_2.characters, 1))) params(first_char)
$$ language sql immutable;

create table if not exists test_table_2 (
  id bigint not null
);

With that said, I might make a PR in the future when the workaround becomes cumbersome to maintain.

Thanks for maintaining this project.