pq: unterminated dollar-quoted string at or near "$BODY$

[REASY]

Seems like x-multi-statement=true is causing migrate to fail with error: migration failed: unterminated dollar-quoted string at or near "$BODY$

Steps to Reproduce

1. My migrations look like

CREATE OR REPLACE FUNCTION add_column(target REGCLASS)
  RETURNS VOID AS $BODY$
DECLARE
table_name TEXT := QUOTE_IDENT(target :: TEXT);
BEGIN
EXECUTE 'ALTER TABLE ' || table_name ||
        ' ADD COLUMN IF NOT EXISTS col_id INT4 NOT NULL';
END;
$BODY$
LANGUAGE plpgsql;

2. I ran migrate with the following options, please note x-multi-statement=true:

docker run -v /tmp/migrations:/migrations --network host -i 9df23b0b252d -path /migrations/ -database 'postgres://postgres:@localhost:5433/test?sslmode=disable&x-multi-statement=true' up

9df23b0b252d is an image id built at commit 3dfb0ff

3. See error error: migration failed: unterminated dollar-quoted string at or near "$BODY$
   

Migrate Version
4.14.1-43-g3dfb0ff, running it as docker container built at commit 3dfb0ff

Loaded Source Drivers
godoc-vfs, gcs, file, s3, github, github-ee, gitlab, go-bindata, bitbucket

Loaded Database Drivers
postgres, clickhouse, cockroachdb, firebird, mongodb, mongodb+srv, sqlserver, postgresql, stub, crdb-postgres, mysql, neo4j, pgx, redshift, spanner, cassandra, cockroach, firebirdsql

In case if I run the migration without x-multi-statement=true it works fine:

Also I can create that function via psql without any issue:

Attachments:
migrations.zip

[dhui]

I don't know if there's a way to have multi-statment work with your migration. multistmt is naive and simply splits by ; so it's incorrectly separating the body of your function. Also, your migration only consists of a single statement so you don't need to use multi-statement

[ppapishe]

I am facing the same issue.
I am trying to run an anon block and getting the same error in multi-statement mode

DO $$
BEGIN
    EXECUTE 'set search_path TO ' || current_database() || ', public';
END;
$$;

Let me know if any one of you guys figured out a workaround

[ppapishe]

@dhui @Fontinalis , Please let me know if you plan to work on this ? else, Please give me a little direction on how to do this and I will try to push a commit. Thanks

[GrandFelix]

Why there is no option that we could use custom delimiter ? multiStmtDelimiter = []byte(";") could be anything right? Like ##__## and we could parse sql files properly so we don't split sql statement for every ; which is not ok in case of Postgres functions etc.

[th0th]

Are there any updates on this one? Or did you folks manage to come up with a workaround?

[NAlexPear]

This is a brutal limitation. My expectation from a migration framework would be at least an option to pass the SQL of a migration directly to the driver without any kind of modification. As it stands, we have to pick between including stored procedures in migrations or only transactional DDL, which is not a choice one should have to make!