supabase db diff fails in several cases

[ff6347]

Bug report

Describe the bug

supabase db diff with simple setup Migra fails with functions that have variable declarations.

To Reproduce

Steps to reproduce the behavior, please provide code snippets or a repository:

When I create a new project and add a function (taken from the docs) the diff fails.

I ran the following steps:

$ supabase init
$ supabase start

Connect to the DB using TablePlus and add the following table and functions:

CREATE TABLE public.profiles (
    id uuid NOT NULL,
    created_at timestamp with time zone DEFAULT now(),
    username text
);


CREATE OR REPLACE FUNCTION public.handle_new_user ()
    RETURNS TRIGGER
    LANGUAGE plpgsql
    SECURITY DEFINER
    AS $function$
BEGIN
    INSERT INTO public.profiles (id)
        VALUES (NEW.id);

    RETURN new;
END;
$function$;

-- trigger the function every time a user is created
CREATE TRIGGER on_auth_user_created
    AFTER INSERT ON auth.users
    FOR EACH ROW
    EXECUTE PROCEDURE public.handle_new_user ();

Then run the default diff

$ supabase db diff
> Error: unexpected end of JSON input

Adding --debug adds no further output.

---

If I run the diff with the --use-migra option it works.

$ supabase db diff --use-migra --file setup --schema public,auth
> Creating shadow database...
> Initialising schema...
> Diffing local database...
> Finished supabase db diff on branch main.

But when I add a function with a variable declaration the diff fails as well.

CREATE or replace FUNCTION public.do_something()
    RETURNS void
    LANGUAGE plpgsql
    SECURITY DEFINER
    AS $function$
    declare some_var text;
BEGIN
    some_var := 'hello, World!';
    RAISE NOTICE '%', some_var;
    RETURN;
END;
$function$;

$ supabase db diff --use-migra --file function --schema public
> Creating shadow database...
> Initialising schema...
> Applying migration 20220816141151_setup.sql...
> Error: ERROR: unterminated dollar-quoted string at or near "$function$
> BEGIN
> INSERT INTO public.profiles (id)
> VALUES (NEW.id)" (SQLSTATE 42601)

• Realted issue on the migra repo https://github.com/djrobstep/migra/issues/212
• Realted gist https://gist.github.com/ff6347/57947013844be36fce122ef075460025

Expected behavior

The diff should be created without error

System information

• OS: macOS
• Supabase CLI v1.0.1