Supabase cli --migra plpgsql function is broken

[huntedman]

I have a function like this

set check_function_bodies = off;

CREATE OR REPLACE FUNCTION public.authorize(requested_permission app_permission, user_id uuid)
 RETURNS boolean
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $$
  DECLARE
    has_permission BOOLEAN;
  BEGIN
    SELECT authorize.requested_permission in (
      SELECT role_permission.app_permission
    FROM role_permission
    WHERE (role_permission.app_role = ( 
        SELECT user_role.app_role
        FROM user_role
        WHERE (user_role.user_id = authorize.user_id)
      )
    ))
    INTO has_permission;
    RETURN has_permission;
  END;
$$;

when I do

supabase db commit --migra create_authorize_function

Everything works as expected. Cool.

Now when I try to build off from this migration, The next changes I introduce and create a migration for will throw an error in the console

supabase db commit --migra create_public_trainings_view
Creating shadow database...
Initialising schema...
Applying migration 20220803184837_create_user_profile_table.sql...
Applying migration 20220803190451_alter_table_users_birthday_data_type.sql...
Applying migration 20220803192043_users_allow_select_owner_only.sql...
Applying migration 20220807130253_create_security.sql...
Applying migration 20220807143525_create_authorize_function.sql...
Error: ERROR: syntax error at or near "RETURN" (SQLSTATE 42601)

And I have no clue why. It looks like a bug with the --migra check? Any ideas on how to debug this?

[ff6347]

I'm running into issues with supabase db diff --use-migra as well. I was able to create the diff with migra, but when I try to apply them I also get an error like "unterminated dollar-quoted string at or near \"$function$\nDECLARE\nis_admin boolean\"

Maybe this issue should be reported in subabase/cli

[sweatybridge]

Yes, this is likely a bug with supabase cli on how we split up migration script to batched statements. I will look into it.

[djrobstep]

Closing this as doesn't seem to be a migra issue.