Cannot use migration with plpgsql functions.

[Chris Baynes]

Creating a function involves using multiple ';' characters for a single function. If I create a .sql migration file, goose will split on those characters and then send invalid sql to postgres.

Is it necessary to split on semicolons when the migrations are run?

[Liam Staskawicz]

I'm actually not sure what the database/sql behavior is if we pass in a query string with multiple statements in it - is that what you're proposing?

Either way, this would be great to resolve. Are you able to share a migration script that demonstrates the issue?

[Chris Baynes]

Yes, passing a query string with multiple statements would certainly fix the problem. This seems to work for Exec() though I don't see any mention of it in the go docs.

A trivial example that demonstrates the issue is:

CREATE OR REPLACE FUNCTION count_users_and_project() RETURNS bigint
LANGUAGE plpgsql
AS $_$
DECLARE
  u integer;
  p integer;
BEGIN
  SELECT count(*) FROM users INTO u;
  SELECT count(*) FROM projects INTO p;
  RETURN p + u;
END;
$_$;

[Liam Staskawicz]

sql migrations: add new annotation to accommodate SQL statements that may have semicolons within them, such as functions. some basic tests as well. fixes #4

→ <<cset cb801ded9c94>>

[Liam Staskawicz]

OK - I did a little testing, and it seemed like not all DB/driver combinations happily execute multiple statements in a single Exec() call, so that strategy is out.

To avoid getting too crazy trying to parse SQL, I've introduced an option to explicitly add -- +goose StatementBegin and -- +goose StatementEnd around more complex statements. It's a slight inconvenience, but should at least be clear and consistent.

Please let me know if you still have any trouble with it.