Supabase Admin Panel

Supabase Admin
Подключение

SQL Настройка

Выполните его во всех проектах:

-- SQL-скрипты для Supabase Admin Panel
-- Содержит функции панели и пользовательские функции.

-- ======== ВАШИ ПОЛЬЗОВАТЕЛЬСКИЕ ФУНКЦИИ ========

-- 1. Ваша функция execute_sql
DROP FUNCTION IF EXISTS public.execute_sql(text);
CREATE OR REPLACE FUNCTION public.execute_sql(query text)
RETURNS json AS $$
DECLARE
    result_json json;
BEGIN
    EXECUTE 'SELECT json_agg(t) FROM (' || query || ') t' INTO result_json;
    RETURN result_json;
END;
$$ LANGUAGE plpgsql;
GRANT EXECUTE ON FUNCTION public.execute_sql(text) TO service_role;

-- 2. Ваша функция get_unique_stores
DROP FUNCTION IF EXISTS public.get_unique_stores();
CREATE OR REPLACE FUNCTION public.get_unique_stores()
RETURNS TABLE(store text) AS $$
BEGIN
  RETURN QUERY 
    SELECT DISTINCT p.store 
    FROM public.prices p
    WHERE p.store IS NOT NULL AND p.store <> ''
    ORDER BY p.store;
END;
$$ LANGUAGE plpgsql;
GRANT EXECUTE ON FUNCTION public.get_unique_stores() TO service_role;


-- ======== СЛУЖЕБНЫЕ ФУНКЦИИ ПАНЕЛИ ========

-- 3. Тестирование соединения
DROP FUNCTION IF EXISTS public.test_connection_schema();
CREATE OR REPLACE FUNCTION test_connection_schema() RETURNS TABLE (schema_name information_schema.sql_identifier) AS $$ BEGIN RETURN QUERY SELECT s.schema_name FROM information_schema.schemata s LIMIT 1; END; $$ LANGUAGE plpgsql SECURITY DEFINER;
GRANT EXECUTE ON FUNCTION public.test_connection_schema() TO service_role;

-- 4. Получение списка пользовательских таблиц
DROP FUNCTION IF EXISTS public.get_public_user_tables();
CREATE OR REPLACE FUNCTION get_public_user_tables() RETURNS TABLE (table_name information_schema.sql_identifier) AS $$ BEGIN RETURN QUERY SELECT t.table_name FROM information_schema.tables t WHERE t.table_schema = 'public' AND t.table_type = 'BASE TABLE' AND t.table_name NOT LIKE 'pg_%' AND t.table_name NOT LIKE 'sql_%' AND t.table_name NOT IN ('supabase_migrations', 'pg_stat_statements', 'pg_stat_statements_info', 'spatial_ref_sys') ORDER BY t.table_name; END; $$ LANGUAGE plpgsql SECURITY DEFINER;
GRANT EXECUTE ON FUNCTION public.get_public_user_tables() TO service_role;

-- 5. Поиск и подсчет данных в таблице
DROP FUNCTION IF EXISTS public.search_table_data(TEXT, TEXT, INT, INT);
CREATE OR REPLACE FUNCTION search_table_data(p_table_name TEXT, p_search_text TEXT, p_limit INT, p_offset INT) RETURNS SETOF JSON AS $$ DECLARE searchable_cols TEXT[]; where_clause TEXT := '1=1'; search_word TEXT; query TEXT; order_by_col TEXT; BEGIN SELECT array_agg(column_name::TEXT) INTO searchable_cols FROM information_schema.columns WHERE table_schema = 'public' AND table_name = p_table_name AND (data_type IN ('character varying', 'text', 'character') OR udt_name IN ('json', 'jsonb')); IF p_search_text IS NOT NULL AND p_search_text != '' AND array_length(searchable_cols, 1) > 0 THEN where_clause := ''; FOREACH search_word IN ARRAY string_to_array(p_search_text, ' ') LOOP IF search_word != '' THEN IF where_clause != '' THEN where_clause := where_clause || ' AND '; END IF; where_clause := where_clause || '(' || array_to_string(ARRAY(SELECT format('%I::text ILIKE %L', col_name, '%' || search_word || '%') FROM unnest(searchable_cols) AS col_name), ' OR ') || ')'; END IF; END LOOP; END IF; SELECT kcu.column_name INTO order_by_col FROM information_schema.table_constraints AS tc JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name AND tc.table_schema = kcu.table_schema WHERE tc.constraint_type = 'PRIMARY KEY' AND tc.table_schema = 'public' AND tc.table_name = p_table_name LIMIT 1; IF order_by_col IS NULL THEN SELECT column_name INTO order_by_col FROM information_schema.columns WHERE table_schema = 'public' AND table_name = p_table_name ORDER BY ordinal_position LIMIT 1; END IF; query := format('SELECT to_json(t.*) FROM public.%I AS t WHERE %s ORDER BY t.%I LIMIT %s OFFSET %s', p_table_name, where_clause, order_by_col, p_limit, p_offset); RETURN QUERY EXECUTE query; END; $$ LANGUAGE plpgsql SECURITY DEFINER;
GRANT EXECUTE ON FUNCTION public.search_table_data(TEXT, TEXT, INT, INT) TO service_role;

DROP FUNCTION IF EXISTS public.search_table_count(TEXT, TEXT);
CREATE OR REPLACE FUNCTION search_table_count(p_table_name TEXT, p_search_text TEXT) RETURNS INT AS $$ DECLARE searchable_cols TEXT[]; where_clause TEXT := '1=1'; search_word TEXT; query TEXT; row_count INT; BEGIN SELECT array_agg(column_name::TEXT) INTO searchable_cols FROM information_schema.columns WHERE table_schema = 'public' AND table_name = p_table_name AND (data_type IN ('character varying', 'text', 'character') OR udt_name IN ('json', 'jsonb')); IF p_search_text IS NOT NULL AND p_search_text != '' AND array_length(searchable_cols, 1) > 0 THEN where_clause := ''; FOREACH search_word IN ARRAY string_to_array(p_search_text, ' ') LOOP IF search_word != '' THEN IF where_clause != '' THEN where_clause := where_clause || ' AND '; END IF; where_clause := where_clause || '(' || array_to_string(ARRAY(SELECT format('%I::text ILIKE %L', col_name, '%' || search_word || '%') FROM unnest(searchable_cols) AS col_name), ' OR ') || ')'; END IF; END LOOP; END IF; query := format('SELECT count(*) FROM public.%I WHERE %s', p_table_name, where_clause); EXECUTE query INTO row_count; RETURN row_count; END; $$ LANGUAGE plpgsql SECURITY DEFINER;
GRANT EXECUTE ON FUNCTION public.search_table_count(TEXT, TEXT) TO service_role;

-- ======== ФУНКЦИИ ДЛЯ МИГРАЦИИ ========
DROP FUNCTION IF EXISTS public.execute_arbitrary_sql(TEXT);
CREATE OR REPLACE FUNCTION execute_arbitrary_sql(sql_text TEXT) RETURNS TEXT AS $$ BEGIN EXECUTE sql_text; RETURN 'OK'; END; $$ LANGUAGE plpgsql SECURITY DEFINER;
GRANT EXECUTE ON FUNCTION public.execute_arbitrary_sql(TEXT) TO service_role;

DROP FUNCTION IF EXISTS public.get_full_schema_sql();
CREATE OR REPLACE FUNCTION get_full_schema_sql()
RETURNS text AS $$
DECLARE
    schema_sql text := '';
    rec record;
BEGIN
    schema_sql := schema_sql || '-- 1. Tables' || E'\n';
    FOR rec IN SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' AND table_type = 'BASE TABLE' AND table_name NOT LIKE 'pg_%' AND table_name NOT LIKE 'sql_%' AND table_name NOT IN ('supabase_migrations') LOOP
        schema_sql := schema_sql || (SELECT 'CREATE TABLE IF NOT EXISTS ' || quote_ident(rec.table_name) || E' (\n' || string_agg('    ' || quote_ident(column_name) || ' ' || udt_name || CASE WHEN character_maximum_length IS NOT NULL THEN '(' || character_maximum_length || ')' ELSE '' END || CASE WHEN is_nullable = 'NO' THEN ' NOT NULL' ELSE '' END || CASE WHEN column_default IS NOT NULL THEN ' DEFAULT ' || column_default ELSE '' END, E',\n') || E'\n);\n\n' FROM information_schema.columns WHERE table_name = rec.table_name AND table_schema = 'public');
    END LOOP;

    schema_sql := schema_sql || '-- 2. Views' || E'\n';
    FOR rec IN SELECT table_name, view_definition FROM information_schema.views WHERE table_schema = 'public' LOOP
        schema_sql := schema_sql || 'CREATE OR REPLACE VIEW ' || quote_ident(rec.table_name) || ' AS ' || E'\n' || rec.view_definition || E';\n\n';
    END LOOP;

    schema_sql := schema_sql || '-- 3. Functions and Procedures' || E'\n';
    FOR rec IN
        SELECT
            pg_get_functiondef(p.oid) AS function_definition,
            'public.' || p.proname || '(' || pg_get_function_identity_arguments(p.oid) || ')' AS function_signature
        FROM pg_proc p
        JOIN pg_namespace n ON p.pronamespace = n.oid
        LEFT JOIN pg_depend d ON d.objid = p.oid AND d.deptype = 'e'
        WHERE n.nspname = 'public' AND p.prokind IN ('f', 'p') AND d.objid IS NULL
          AND p.proname NOT IN (
              'test_connection_schema', 'get_public_user_tables', 'search_table_data',
              'search_table_count', 'execute_arbitrary_sql', 'get_full_schema_sql'
          )
    LOOP
        schema_sql := schema_sql || 'DROP FUNCTION IF EXISTS ' || rec.function_signature || E';\n';
        schema_sql := schema_sql || rec.function_definition || E';\n\n';
    END LOOP;

    schema_sql := schema_sql || '-- 4. Indexes' || E'\n';
    FOR rec IN SELECT indexdef FROM pg_indexes WHERE schemaname = 'public' LOOP
        IF rec.indexdef NOT LIKE '%_pkey%' THEN
           schema_sql := schema_sql || REPLACE(rec.indexdef, ' INDEX', ' INDEX IF NOT EXISTS') || E';\n\n';
        END IF;
    END LOOP;

    RETURN schema_sql;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
GRANT EXECUTE ON FUNCTION public.get_full_schema_sql() TO service_role;
Миграция Базы Данных

1. Источник (Откуда)

2. Назначение (Куда)

3. Объекты для миграции

4. Лог выполнения

Files in Bucket

Table Data

Панель:

Таблицы (схема: public)

Buckets (Хранилище)
