github.com/supabase/cli@v1.168.1/internal/db/reset/templates/drop.sql (about)

     1  do $$ declare
     2    rec record;
     3  begin
     4    -- extensions
     5    for rec in
     6      select *
     7      from pg_extension p
     8      where p.extname not in ('pg_graphql', 'pg_net', 'pg_stat_statements', 'pgcrypto', 'pgjwt', 'pgsodium', 'plpgsql', 'supabase_vault', 'uuid-ossp')
     9    loop
    10      execute format('drop extension if exists %I cascade', rec.extname);
    11    end loop;
    12  
    13    -- functions
    14    for rec in
    15      select *
    16      from pg_proc p
    17      where p.pronamespace::regnamespace::name = 'public'
    18    loop
    19      -- supports aggregate, function, and procedure
    20      execute format('drop routine if exists %I.%I(%s) cascade', rec.pronamespace::regnamespace::name, rec.proname, pg_catalog.pg_get_function_identity_arguments(rec.oid));
    21    end loop;
    22  
    23    -- tables (cascade to views)
    24    for rec in
    25      select *
    26      from pg_class c
    27      where
    28        c.relnamespace::regnamespace::name = 'public'
    29        and c.relkind not in ('c', 'S', 'v', 'm')
    30      order by c.relkind desc
    31    loop
    32      -- supports all table like relations, except views, complex types, and sequences
    33      execute format('drop table if exists %I.%I cascade', rec.relnamespace::regnamespace::name, rec.relname);
    34    end loop;
    35  
    36    -- truncate tables in auth and migrations schema
    37    for rec in
    38      select *
    39      from pg_class c
    40      where
    41        c.relnamespace::regnamespace::name in ('auth', 'supabase_migrations')
    42        and c.relkind = 'r'
    43    loop
    44      execute format('truncate %I.%I restart identity cascade', rec.relnamespace::regnamespace::name, rec.relname);
    45    end loop;
    46  
    47    -- sequences
    48    for rec in
    49      select *
    50      from pg_class c
    51      where
    52        c.relnamespace::regnamespace::name = 'public'
    53        and c.relkind = 's'
    54    loop
    55      execute format('drop sequence if exists %I.%I cascade', rec.relnamespace::regnamespace::name, rec.relname);
    56    end loop;
    57  
    58    -- types
    59    for rec in
    60      select *
    61      from pg_type t
    62      where
    63        t.typnamespace::regnamespace::name = 'public'
    64        and typtype != 'b'
    65    loop
    66      execute format('drop type if exists %I.%I cascade', rec.typnamespace::regnamespace::name, rec.typname);
    67    end loop;
    68  
    69    -- policies
    70    for rec in
    71      select *
    72      from pg_policies p
    73    loop
    74      execute format('drop policy if exists %I on %I.%I cascade', rec.policyname, rec.schemaname, rec.tablename);
    75    end loop;
    76  end $$;