Supabase Migrations cannot handle `CREATE FUNCTION` with the `BEGIN ATOMIC` syntax

[allezxandre]

Bug report

Describe the bug

It is not possible to create a procedure or function using the BEGIN ATOMIC syntax from PostgreSQL 14 in a Supabase migration.

Probably a side-effect, but running supabase db diff after such a function has been defined returns:

CREATE OR REPLACE FUNCTION public.add(
        a integer,
        b integer)
    RETURNS integer
    LANGUAGE 'sql'
    COST 100
    IMMUTABLE STRICT PARALLEL UNSAFE
AS $BODY$
$BODY$;

supabase db diff --use-migra does not have the issue:

CREATE OR REPLACE FUNCTION public.add(a integer, b integer)
 RETURNS integer
 LANGUAGE sql
 IMMUTABLE STRICT
BEGIN ATOMIC
 SELECT (a + b);
END
;

To Reproduce

Create a new migration (supabase migration new function-test), and add to it the following sample add function:

CREATE FUNCTION add(a integer, b integer) RETURNS integer
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT
BEGIN ATOMIC
    SELECT a + b;
END;

Running the migration fails with the following error:

Error: ERROR: syntax error at end of input (SQLSTATE 42601)
At statement 1:                                                          

CREATE FUNCTION add(a integer, b integer) RETURNS integer
LANGUAGE SQL                                             
IMMUTABLE                                                
RETURNS NULL ON NULL INPUT                               
BEGIN ATOMIC                                             
    SELECT a + b;                                        
Try rerunning the command with --debug to troubleshoot the error.

Expected behavior

The migration should complete.

Screenshots

If applicable, add screenshots to help explain your problem.

System information

• OS: all
• Version of supabase-js: 2.1.0
• Version of Node.js: 18 LTS

[allezxandre]

This might be related to pgadmin, but I see the issue has been solved upstream here: https://github.com/pgadmin-org/pgadmin4/issues/4785

[sweatybridge]

Thank you for reporting this issue. This is likely a problem with statement splitter https://github.com/supabase/cli/blob/main/internal/utils/parser/state.go not dealing with semicolons properly in begin atomic functions.

We will look into it more in due course and address them.

[profiluefter]

Hi, I just encountered this issue with the CREATE RULE statement which also contains semicolons as part of the statement (but enclosed in parenthesis). I can verify using the debug flag that the statement is split incorrectly.

Technically, you can work around it by putting the statement inside a dynamic sql EXECUTE in a plpgsql DO block. This is however hilariously too complicated 😂

[rafonseca]

I guess the issue is exactly here, in SplitAndTrim function.

The workaround using dynamic SQL is a big no for me. Any progress here?

Thanks in advance

[sweatybridge]

Thanks for bumping this issue. I've pushed my changes to a draft PR. Will circle back again early next week.

[martinsellergren]

@sweatybridge , I'm still seeing issues with begin atomic. I think related to case expressions, perhaps due to the 'end' statement of case expressions.

Failing:

create
or replace function public."queryPublishedStories" ("queryOrder" jsonb) returns setof "apiStory" language sql
begin atomic
select
    *
from
    "apiStory" s
order by
    case "queryOrder" ->> 'runtimeType'
        when 'DatePublished' then s."datePublished"
    end;

end;