[Bug]: Can't create procedure without breaking SQL

[Mishamba]

Module

PostgreSQL

Testcontainers version

postgres:15.3

Using the latest Testcontainers version?

Yes

Host OS

Windows

Host Arch

x86

Docker version

Server: Docker Desktop 4.26.1 (131620)
 Engine:
  Version:          24.0.7
  API version:      1.43 (minimum version 1.12)
  Go version:       go1.20.10
  Git commit:       311b9ff
  Built:            Thu Oct 26 09:08:02 2023
  OS/Arch:          linux/amd64
  Experimental:     false
 containerd:
  Version:          1.6.25
  GitCommit:        d8f198a4ed8892c764191ef7b3b06d8a2eeb5c7f
 runc:
  Version:          1.1.10
  GitCommit:        v1.1.10-0-g18a0cb0
 docker-init:
  Version:          0.19.0
  GitCommit:        de40ad0

What happened?

I'm trying to create a procedure with following script

CREATE OR REPLACE PROCEDURE get_user(
    OUT user_ref refcursor,
    IN ID numeric)
    LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
    OPEN user_ref FOR
        SELECT username, create_date
        FROM users
        WHERE id = ID
        ORDER BY modified_dttm;
END;
$BODY$;

And i'm getting next error

Caused by: org.postgresql.util.PSQLException: Unterminated dollar quote started at position 99 in SQL CREATE OR REPLACE PROCEDURE get_user( OUT user_ref refcursor, IN ID numeric) LANGUAGE 'plpgsql' AS $BODY$ BEGIN
    OPEN user_ref FOR
        SELECT username, create_date
        FROM users
        WHERE id = ID
        ORDER BY modified_dttm;
END. Expected terminating $$

It can be solved by removing semicolon after END. After this it works, but only if I try to create one procedure in a row.

CREATE OR REPLACE PROCEDURE get_user(
    OUT user_ref refcursor,
    IN ID numeric)
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
    OPEN user_ref FOR
        SELECT username, create_date
        FROM users
        WHERE id = ID
        ORDER BY modified_dttm;
END
$BODY$;

But if there two such procedures in a row they wont work. Problem is related with how ScriptUtils parse initial script into transactions.

Relevant log output

Caused by: org.postgresql.util.PSQLException: Unterminated dollar quote started at position 99 in SQL CREATE OR REPLACE PROCEDURE get_user( OUT user_ref refcursor, IN ID numeric) LANGUAGE 'plpgsql' AS $BODY$ BEGIN
    OPEN user_ref FOR
        SELECT username, create_date
        FROM users
        WHERE id = ID
        ORDER BY modified_dttm;
END. Expected terminating $$

Additional Information

No response

[eddumelendez]

Can you try with Testcontainers 1.19.5, please?

[EvalVis]

@Mishamba
If you try using Testcontainers 1.19.5 and it still does not work could you provide a code snippet on how do you execute the script?