Important Supabase RPCs

create_function_get_enum_create_type.sql

-- ============================================================
-- Function: get_enum_create_type
-- Schema: public
-- Objective:
-- Return, for each ENUM in the public schema, the complete command
-- CREATE TYPE ... AS ENUM (...);
--
-- Typical use:
-- SELECT * FROM public.get_enum_create_type();
--
-- Can be used in migrations, structure backups,
-- script generation, etc.
--
-- ============================================================

CREATE OR REPLACE FUNCTION public.get_enum_create_type()
RETURNS TABLE (
  schema_name     TEXT,
  enum_name       TEXT,
  create_type_sql TEXT
)
LANGUAGE sql
STABLE
SECURITY DEFINER
AS $$
  SELECT
    n.nspname AS schema_name,
    t.typname AS enum_name,
    'CREATE TYPE '
      || quote_ident(n.nspname) || '.' || quote_ident(t.typname)
      || ' AS ENUM ('
      || string_agg(
           quote_literal(e.enumlabel),
           ', ' ORDER BY e.enumsortorder
         )
      || ');' AS create_type_sql
  FROM pg_type t
  JOIN pg_enum e       ON e.enumtypid   = t.oid
  JOIN pg_namespace n  ON n.oid         = t.typnamespace
  WHERE n.nspname = 'public'        -- filtra apenas schema public
    AND t.typtype = 'e'             -- garante que é ENUM
  GROUP BY n.nspname, t.typname
  ORDER BY n.nspname, t.typname;
$$;

COMMENT ON FUNCTION public.get_enum_create_type()
IS 'Retorna, para cada ENUM do schema public, o comando CREATE TYPE ... AS ENUM (...);';

create_function_get_indexes.sql

-- ============================================================================
-- Função RPC para obter índices e constraints únicas de uma tabela (public)
-- ============================================================================

CREATE OR REPLACE FUNCTION public.get_indexes_and_constraints(p_table_name TEXT)
RETURNS TABLE (
    index_name      TEXT,
    index_type      TEXT,
    columns         TEXT[],
    is_unique       BOOLEAN,
    is_primary      BOOLEAN,
    constraint_name TEXT
)
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
    RETURN QUERY
    WITH
    -- 1) Constraints (PRIMARY KEY e UNIQUE), com nome da constraint
    constraint_info AS (
        SELECT
            con.conname::TEXT                           AS constraint_name,
            CASE con.contype
                WHEN 'p' THEN 'PRIMARY KEY'
                WHEN 'u' THEN 'UNIQUE'
                ELSE 'CONSTRAINT'
            END                                         AS index_type,
            idx.relname::TEXT                           AS index_name,
            ARRAY_AGG(att.attname::TEXT
                      ORDER BY array_position(ix.indkey, att.attnum)) AS columns,
            (con.contype IN ('p','u'))                  AS is_unique,
            (con.contype = 'p')                         AS is_primary
        FROM pg_constraint con
        JOIN pg_class       tab ON tab.oid = con.conrelid
        JOIN pg_namespace   n   ON n.oid = tab.relnamespace
        JOIN pg_index       ix  ON ix.indexrelid = con.conindid
        JOIN pg_class       idx ON idx.oid = ix.indexrelid
        JOIN pg_attribute   att ON att.attrelid = tab.oid
                               AND att.attnum = ANY(ix.indkey)
        WHERE
            n.nspname   = 'public'
            AND tab.relname = p_table_name
            AND con.contype IN ('p','u')   -- p = PRIMARY KEY, u = UNIQUE
        GROUP BY
            con.conname, con.contype, idx.relname, ix.indkey
    ),

    -- 2) Índices "soltos" (não PK, não UNIQUE de constraint)
    plain_index_info AS (
        SELECT
            idx.relname::TEXT                           AS index_name,
            'INDEX'::TEXT                               AS index_type,
            ARRAY_AGG(att.attname::TEXT
                      ORDER BY array_position(ix.indkey, att.attnum)) AS columns,
            ix.indisunique                              AS is_unique,
            ix.indisprimary                             AS is_primary,
            NULL::TEXT                                  AS constraint_name
        FROM pg_index ix
        JOIN pg_class     tab  ON tab.oid = ix.indrelid
        JOIN pg_namespace n    ON n.oid  = tab.relnamespace
        JOIN pg_class     idx  ON idx.oid = ix.indexrelid
        JOIN pg_attribute att  ON att.attrelid = tab.oid
                               AND att.attnum   = ANY(ix.indkey)
        WHERE
            n.nspname   = 'public'
            AND tab.relname = p_table_name
            AND NOT EXISTS (
                SELECT 1
                FROM pg_constraint con
                WHERE con.conindid = ix.indexrelid
                  AND con.conrelid = tab.oid
            ) -- exclui índices usados por constraints (PK/UNIQUE)
        GROUP BY
            idx.relname, ix.indisunique, ix.indisprimary, ix.indkey
    )

    SELECT
        ci.index_name,
        ci.index_type,
        ci.columns,
        ci.is_unique,
        ci.is_primary,
        ci.constraint_name
    FROM constraint_info ci

    UNION ALL

    SELECT
        pi.index_name,
        pi.index_type,
        pi.columns,
        pi.is_unique,
        pi.is_primary,
        pi.constraint_name
    FROM plain_index_info pi

    ORDER BY
        is_primary DESC,
        is_unique DESC,
        index_name;
END;
$$;

COMMENT ON FUNCTION public.get_indexes_and_constraints(TEXT)
    IS 'Retorna índices (não ligados a constraints) + constraints PRIMARY KEY/UNIQUE (sem duplicar UNIQUE).';

create_function_get_public_enums.sql

---- RPC: listar todos os ENUMs do schema public

---- Essa função retorna exatamente:
---- schema
---- enum_name
---- enum_value

CREATE OR REPLACE FUNCTION public.get_public_enums()
RETURNS TABLE (
  schema TEXT,
  enum_name TEXT,
  enum_value TEXT
)
LANGUAGE sql
STABLE
SECURITY DEFINER
AS $$
  SELECT
    n.nspname      AS schema,
    t.typname      AS enum_name,
    e.enumlabel    AS enum_value
  FROM pg_type t
  JOIN pg_enum e       ON t.oid = e.enumtypid
  JOIN pg_namespace n ON n.oid = t.typnamespace
  WHERE n.nspname = 'public'
  ORDER BY
    n.nspname,
    t.typname,
    e.enumsortorder;
$$;


--- SELECT * FROM public.get_public_enums();

create_function_get_table_comments.sql

-- ============================================================================
-- Função RPC para obter comentários de tabelas e colunas
-- ============================================================================
-- Esta função retorna os comentários (COMMENT) de uma tabela e suas colunas
-- para uso no sistema de migração.
-- ============================================================================

CREATE OR REPLACE FUNCTION public.get_table_comments(
    p_schema TEXT,
    p_table TEXT
)
RETURNS TABLE (
    tipo TEXT,           -- 'table' ou 'column'
    nome TEXT,           -- nome da tabela ou coluna
    comentario TEXT      -- texto do comentário
)
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
    v_table_oid oid;
    v_table_comment TEXT;
    v_col_record RECORD;
BEGIN
    -- Localiza a tabela no catálogo
    SELECT c.oid
      INTO v_table_oid
    FROM pg_class c
    JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE n.nspname = p_schema
      AND c.relname = p_table
      AND c.relkind = 'r';  -- 'r' = Tabela normal

    IF NOT FOUND THEN
        -- Retorna vazio se tabela não encontrada
        RETURN;
    END IF;

    -- Comentário da tabela
    SELECT obj_description(v_table_oid, 'pg_class')
      INTO v_table_comment;
    
    IF v_table_comment IS NOT NULL AND v_table_comment != '' THEN
        tipo := 'table';
        nome := p_table;
        comentario := v_table_comment;
        RETURN NEXT;
    END IF;

    -- Comentários das colunas
    FOR v_col_record IN
        SELECT 
            att.attname AS column_name,
            col_description(att.attrelid, att.attnum) AS column_comment
        FROM pg_attribute att
        WHERE att.attrelid = v_table_oid
          AND att.attnum > 0           -- ignora system columns
          AND NOT att.attisdropped     -- ignora colunas apagadas
          AND col_description(att.attrelid, att.attnum) IS NOT NULL
          AND col_description(att.attrelid, att.attnum) != ''
        ORDER BY att.attnum
    LOOP
        tipo := 'column';
        nome := v_col_record.column_name;
        comentario := v_col_record.column_comment;
        RETURN NEXT;
    END LOOP;

    RETURN;
END;
$$;

-- Comentário na função
COMMENT ON FUNCTION public.get_table_comments(TEXT, TEXT) IS 
'Retorna comentários (COMMENT) de uma tabela e suas colunas para migração';

create_function_get_table_ddl.sql

CREATE OR REPLACE FUNCTION public.get_table_ddl(p_schema TEXT, p_table TEXT)
RETURNS SETOF TEXT
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
    v_table_oid oid;
    v_cols      text;
    v_stmt      text;
BEGIN
    -- Localiza a tabela no catálogo
    SELECT c.oid
      INTO v_table_oid
    FROM pg_class c
    JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE n.nspname = p_schema
      AND c.relname = p_table
      AND c.relkind = 'r';  -- 'r' = Tabela normal

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Tabela %.% não encontrada', p_schema, p_table;
    END IF;

    -- Monta a definição das colunas
    SELECT string_agg(
               format(
                   '  %I %s%s%s%s',
                   att.attname,
                   format_type(att.atttypid, att.atttypmod),
                   CASE att.attidentity
                       WHEN 'a' THEN ' GENERATED ALWAYS AS IDENTITY'
                       WHEN 'd' THEN ' GENERATED BY DEFAULT AS IDENTITY'
                       ELSE ''
                   END,
                   CASE
                       WHEN def.adbin IS NOT NULL AND att.attidentity = '' THEN
                           ' DEFAULT ' || pg_get_expr(def.adbin, def.adrelid)
                       ELSE ''
                   END,
                   CASE
                       WHEN att.attnotnull THEN ' NOT NULL'
                       ELSE ''
                   END
               ),
               E',\n'
           ) AS cols
      INTO v_cols
    FROM pg_attribute att
    LEFT JOIN pg_attrdef def
           ON def.adrelid = att.attrelid
          AND def.adnum   = att.attnum
    WHERE att.attrelid = v_table_oid
      AND att.attnum > 0           -- ignora system columns
      AND NOT att.attisdropped;    -- ignora colunas apagadas

    -- PRIMEIRA LINHA: CREATE TABLE ...
    v_stmt := format(
        'CREATE TABLE %I.%I (' || E'\n%s\n' || ');',
        p_schema,
        p_table,
        v_cols
    );
    RETURN NEXT v_stmt;

    -- SEGUNDA PARTE: constraints (PK, UNIQUE, FK, CHECK)
    FOR v_stmt IN
        SELECT format(
                   'ALTER TABLE %I.%I ADD CONSTRAINT %I %s;',
                   p_schema,
                   p_table,
                   con.conname,
                   pg_get_constraintdef(con.oid, true)
               )
        FROM pg_constraint con
        WHERE con.conrelid = v_table_oid
        ORDER BY con.contype DESC, con.conname
    LOOP
        RETURN NEXT v_stmt;
    END LOOP;

    -- TERCEIRA PARTE: índices não ligados a constraints (CREATE INDEX ...)
    FOR v_stmt IN
        SELECT pg_get_indexdef(idx.oid, 0, true) || ';'
        FROM pg_index ix
        JOIN pg_class idx ON idx.oid = ix.indexrelid
        WHERE ix.indrelid = v_table_oid
          AND NOT ix.indisprimary
          AND NOT EXISTS (
              SELECT 1
              FROM pg_constraint con
              WHERE con.conindid = ix.indexrelid
          )
        ORDER BY idx.relname
    LOOP
        RETURN NEXT v_stmt;
    END LOOP;

END;
$$;


--- SELECT * FROM public.get_table_ddl('public', 'execucao');

create_function_list_schemas.sql

-- ============================================================================
-- Função RPC para listar todos os schemas do banco de dados
-- ============================================================================
-- Esta função retorna todos os schemas disponíveis no banco de dados
-- para uso no sistema de migração.
-- ============================================================================

CREATE OR REPLACE FUNCTION list_schemas()
RETURNS TABLE (
    schema_name TEXT,
    schema_owner TEXT,
    description TEXT
)
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        n.nspname::TEXT AS schema_name,
        pg_get_userbyid(n.nspowner)::TEXT AS schema_owner,
        obj_description(n.oid, 'pg_namespace')::TEXT AS description
    FROM pg_namespace n
    WHERE n.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast', 'pg_temp_1', 'pg_toast_temp_1')
      AND n.nspname NOT LIKE 'pg_temp_%'
      AND n.nspname NOT LIKE 'pg_toast_temp_%'
    ORDER BY n.nspname;
END;
$$;

-- Comentário
COMMENT ON FUNCTION list_schemas() IS 'Lista todos os schemas do banco de dados (exceto schemas do sistema)';

create_table_get_enum_usage.sql

-- ============================================================
-- Função: get_enum_usage
-- Schema: public
-- Objetivo:
--   Listar, para cada ENUM do schema public, em quais
--   tabelas/colunas ele é utilizado.
--
-- Uso típico:
--   SELECT * FROM public.get_enum_usage();
--
--   Útil para:
--     - impact analysis (ver onde um ENUM é usado)
--     - refatoração de tipos
--     - geração de migrações
-- ============================================================

CREATE OR REPLACE FUNCTION public.get_enum_usage()
RETURNS TABLE (
  enum_schema   TEXT,
  enum_name     TEXT,
  table_schema  TEXT,
  table_name    TEXT,
  column_name   TEXT
)
LANGUAGE sql
STABLE
SECURITY DEFINER
AS $$
  SELECT
    n.nspname        AS enum_schema,
    t.typname        AS enum_name,
    c.table_schema   AS table_schema,
    c.table_name     AS table_name,
    c.column_name    AS column_name
  FROM pg_type t
  JOIN pg_namespace n ON n.oid         = t.typnamespace
  JOIN information_schema.columns c
    ON c.udt_name   = t.typname       -- tipo da coluna = tipo do enum
   AND c.udt_schema = n.nspname
  WHERE n.nspname = 'public'          -- apenas ENUMs do schema public
    AND t.typtype = 'e'               -- t.typtype = 'e' garante que é ENUM
  ORDER BY
    t.typname,
    c.table_schema,
    c.table_name,
    c.column_name;
$$;

COMMENT ON FUNCTION public.get_enum_usage()
IS 'Lista os ENUMs do schema public e as tabelas/colunas onde cada ENUM é utilizado.';