Cannot define a function in `seed.sql`

[iveshenry18]

Bug report

Describe the bug

I've created a helper function to generate some random data in seed.sql. I can successfully run the script against my local Supabase instance, but supabase db reset fails with the following message:

{"Type":"ErrorResponse","Severity":"ERROR","SeverityUnlocalized":"ERROR","Code":"42883","Message":"function private.generate_random_test_users(integer) does not exist","Detail":"","Hint":"No function matches the given name and argument types. You might need to add explicit type casts.","Position":15,"InternalPosition":0,"InternalQuery":"","Where":"","SchemaName":"","TableName":"","ColumnName":"","DataTypeName":"","ConstraintName":"","File":"parse_func.c","Line":629,"Routine":"ParseFuncOrColumn","UnknownFields":null}

I suspect the function was not successfully created.

To Reproduce

1. Create a function in seed.sql

   CREATE OR REPLACE FUNCTION generate_random_test_users(n integer)
   ...

2. Add an invocation of the function later in seed.sql

   SELECT * FROM generate_random_test_users(1000);

3. Run supabase db reset

Expected behavior

The function executes, creating random test data.

System information

• OS: macOS
• Version of Supabase CLI: 1.26.5

[sweatybridge]

Just to clarify, creating function is seed.sql is not currently supported. The supported statements are simple inserts and deletes. We can look into support it as part of hooks https://github.com/supabase/cli/issues/501

[iveshenry18]

Gotcha, good to know! Thanks for pointing out #501. Looks like that proposal would cover my use case, so I'd say consider this issue a +1 on that request. Thanks again!

[mlynch]

This would be wonderful to ease creating seed data. I wonder what other limitations there are? I've been able to use a number of other postgres commands beyond basic insert/delete/select

[iveshenry18]

@mlynch, not sure it will help for your use case, but as a workaround, I ended up creating a directory (seed_sql) of numbered SQL files that define and execute seed procedures, then I run the following command:

cat ./seed_sql/*.sql | psql postgresql://postgres:postgres@localhost:54322/postgres -1 -f -

[rileytaylor]

For what it's worth, I've been able to define functions in the seed.sql file with success, though I'm attaching them to a schema that I'm also creating in seed.sql. I only use it for testing when developing locally, so I'm not terribly concerned about the random schema hanging around.

-- Set up the schema and lock it down so it doesn't get used unintentionally in actual migrations
create schema if not exists setup;
grant usage on schema setup to anon, authenticated;
alter default privileges in schema setup revoke execute on functions from public;
alter default privileges in schema setup grant execute on functions to anon, authenticated;

-- A function to create an instance of one of our tables
create or replace function setup.create_org(name text)
    returns uuid
    set search_path = public, pg_temp
as $$
declare
    _org_id uuid;
begin
    insert into public.organizations
        (name)
    values
        (name)
    returning id into _org_id;

    return _org_id;
end;
$$ language plpgsql;

-- Then i run a bunch of these in a big do block

do $$
declare
    _org_id uuid;
    _ron_id uuid;
    _leslie_id uuid;
    _andy_id uuid;
    _april_id uuid;
begin
    -- Org
    _org_id := (select setup.create_org('Pawnee City'));

    -- User Profiles
    _ron_id := (select setup.create_profile(_org_id, '1', 'ron@pawnee.gov', 'Ron', 'Swanson', 'owner'));
    _leslie_id := (select setup.create_profile(_org_id, '2', 'leslie@pawnee.gov', 'Leslie', 'Knope', 'manager'));
    _andy_id := (select setup.create_profile(_org_id, '3', 'andy@pawnee.gov', 'Andy', 'Dwyer', 'user'));
    _april_id := (select setup.create_profile(_org_id, '4', 'april@pawnee.gov', 'April', 'Ludgate', 'user'));

    -- Create user logins
    perform setup.create_user_for_profile(_ron_id);
    perform setup.create_user_for_profile(_leslie_id);
    perform setup.create_user_for_profile(_andy_id);
    perform setup.create_user_for_profile(_april_id);

    -- ... and so on
end
$$ language plpgsql;

Since it's unsupported (news to me since this worked pretty much out of the box!) who knows if this will continue to function, but it works fine for now.

[sweatybridge]

I looked into this a bit more and can confirm the following works:

create function random_checksum(n integer)
returns setof text as $$
  select md5(random()::text) from generate_series(1, n);
$$ language sql immutable;

do $$
begin
    insert into employees(name)
    select random_checksum(100);
end$$;

To use any functions defined in seed.sql, the calling statement must be wrapped in postgres DO block.