Postgres dollar quotes and function definition conflict

[rrrship]

Hey

When trying to see how hard or easy it would be to add support for PostgreSQL's DO block (docs) I stumbled upon this issue with <S_QUOTED_IDENTIFIER> consuming the function's declaration parts. The issue is that in PostgreSQL the function body (or the body of DO block, which is basically an anonymous function) has to be defined as a string constant. Since this is cumbersome (with the need to escape some chars) dollar quotes ($$) are used when defining functions. But since <S_QUOTED_IDENTIFIER> consumes everything between $$ $$ the parser obviously throws an exception.

I actually found this thread under JavaCC project started by @wumpz : link to thread. The issue is kind of similar and I see that allowSquareBracketQuotation feature tag was introduced, but the difference here is that this is actually not a conflict between different SQL flavors - it's valid PostgreSQL. Maybe someone has any ideas how to do some context based switching cleanly. I looked into lexical states, but I'm not sure if this would solve this issue. What I would like to achieve is something like the Block() class - everything between DO $$ ['function body here'] $$ would be parsed as a statement.

Best

[manticore-projects]

Greetings.

While I am a big fan of PostgreSQL, I do not like this particular syntax at all :(
That said, please help me out:

a) is it strictly $$DECLARE ... END$$; (without any white space between $$ and DECLARE or END?
b) or are other syntax allowed after the DO?

It should be possible to check for $$DECLARE or $$END, before we consume $$ ... $$ as String.

[rrrship]

1. No, it's not strict about that: DECLARE is actually optional after the DO. This is a common example of what I see in code:

DO $$
    BEGIN
        IF NOT EXISTS(SELECT 1 FROM country WHERE code = 'EE') THEN
            INSERT INTO country(code) VALUES ('EE');
        END IF;
    END
$$;

2. Language definition is allowed as well after the DO and before the quotes:

DO LANGUAGE plpgsql $$
    DECLARE example_var varchar := 'something-something';
    BEGIN
        IF NOT EXISTS(SELECT 1 FROM country WHERE code = 'EE') THEN
            INSERT INTO country(code) VALUES ('EE');
        END IF;
    END $$;

but language can be set after the closing dollar quotes as well.

[manticore-projects]

Thanks for the illustration, very helpful.
I think, an extra Parser method parseBlock would solve our problem. Alternatively, we could implement a Pre-Processor (using a REGEX), which extracts the Text in between the DO $$ ... $$ and then we send only that Text to the parser.

[rrrship]

Do you mean something like this:

1. If DO token found, then create DoBlock() (or something like that, could be merged with an existing Block())
2. Store the body in a string (using <S_QUOTED_IDENTIFIER>)
3. Add method to the DoBlock() class parseBlock, which invokes the parser again with the stored string?

Also are there any examples of using pre-processing in this project?

[manticore-projects]

Yes, exactly like that.
I would try to draft a template tomorrow.

[manticore-projects]

Another idea was, to implement a special method parseBlock(String blockContent) which does not parse a Statement but only blocks.
However, do I understand it correctly, that the Block itself could contain $$...$$ Strings? brrr......

[rrrship]

Theoretically yes.

[manticore-projects]

Theoretically yes.

Wow.
Well, it is what it is. I feel like we need a 2 step parsing approach here:

1. Parse a PostgreSQL DO $$....$$ block (where the Property statementText will represent the Text inside the $$...$$)
2. then parse the content of statementText in a second step, which will return the Block Statements

[rrrship]

Actually it seems it might not be supported after all. I'll do some additional tests to be sure.

[rrrship]

You can't have additional $$ signs inside the do block. Sorry for the confusion.

[manticore-projects]

You can't have additional $$ signs inside the do block. Sorry for the confusion.

Ok, this simplifies things massively. I will put something together tomorrow.

[manticore-projects]

Good Morning.

As promised, I drafted something: https://github.com/manticore-projects/JSqlParser/tree/PostgresDoBlock
It seems to work in general, but keeps the Code block as Text only when it can't be parsed.

[manticore-projects]

Although I feel it is of little use, since we can't parse all Syntax within the BEGIN/END block. E.g. FOR Loops.

[rrrship]

I think it's a good start because I see this a lot in DO blocks:

IF NOT EXISTS(SELECT 1 FROM country WHERE code = 'EE') 
    INSERT INTO country(code) VALUES ('EE');
END IF;

I see that there's not a support for that kind of syntax as well, but maybe I can try to implement this.
Also, regarding FOR loops, has there been discussions about supporting them? I have no idea how difficult it would be.

[manticore-projects]

A similar T-SQL/MSQL SERVER statement is there already.
We will need to list all similar PL/SQL constructs, prioritize and then implement one by one.

It even might be a good idea to implement a second/independent PL/SQL parser for those in order to avoid overlapping with the SQL Parser.

I never used PL/SQL and do not like it -- but I will certainly help you to get it work.

[rrrship]

By an independent parser, do you mean something with a separate grammar?

[manticore-projects]

Also. The Language tag seems to allow for different languages aside pl sql.So I would implement one grammar as per language. Sent from my Galaxy -------- Original message --------From: rrrship ***@***.***> Date: 10/06/2022 15:05 (GMT+01:00) To: JSQLParser/JSqlParser ***@***.***> Cc: manticore-projects ***@***.***>, Comment ***@***.***> Subject: Re: [JSQLParser/JSqlParser] Postgres dollar quotes and function definition conflict (Discussion #1561) By an independent parser, do you mean something with a separate grammar? —Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you commented.Message ID: ***@***.***>

[manticore-projects]

This seems to be a good illustration: https://github.com/jOOQ/sakila/blob/main/postgres-sakila-db/postgres-sakila-schema.sql

[rrrship]

Thanks for the support. Not sure right now when I would get to it. Seems like a lot of work.

[wumpz]

@rrrship @manticore-projects JSqlParsers function body parsing seems to be exactly what you are looking for. Look into CreateFunctionalStatementTest. The function body between those $$ is parsed as a quoted identifier.

[manticore-projects]

Yes. Exactly. A separate grammar for pl sql and a separate parser class.Helps to keep the grammar simple and avoid too many look aheads.Sent from my Galaxy -------- Original message --------From: rrrship ***@***.***> Date: 10/06/2022 15:05 (GMT+01:00) To: JSQLParser/JSqlParser ***@***.***> Cc: manticore-projects ***@***.***>, Comment ***@***.***> Subject: Re: [JSQLParser/JSqlParser] Postgres dollar quotes and function definition conflict (Discussion #1561) By an independent parser, do you mean something with a separate grammar? —Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you commented.Message ID: ***@***.***>