exec_sql.sql - master - dmukhin/SpherePlatform

exec_sql.sql
74 строки3 KB
OIS\sabogdan
first commit
24 мар 2026, 07:50
24 мар 2026, 07:50b0e18c7
-- Canonical copy for fresh installs: db/init/01_sphere_functions.sql
-- Удалить старую версию, если была
drop function if exists public.exec_sql(text);

-- Функция для DDL из приложения
create or replace function public.exec_sql(sql text)
returns jsonb
language plpgsql
security definer
set search_path = public
as $$
declare
  normalized_sql text;
begin
  if sql is null or btrim(sql) = '' then
    raise exception 'SQL is empty';
  end if;

  normalized_sql := lower(regexp_replace(sql, '^[[:space:]]+', '', ''));

  -- Ограничение: только CREATE TABLE / DROP TABLE / ALTER TABLE
  if normalized_sql !~ '^create[[:space:]]+table([[:space:]]+if[[:space:]]+not[[:space:]]+exists)?[[:space:]]+'
     and normalized_sql !~ '^drop[[:space:]]+table([[:space:]]+if[[:space:]]+exists)?[[:space:]]+'
     and normalized_sql !~ '^alter[[:space:]]+table[[:space:]]+' then
    raise exception 'Only CREATE TABLE / DROP TABLE / ALTER TABLE are allowed';
  end if;

  execute sql;
  return jsonb_build_object('ok', true);
end;
$$;

-- Права: для вызова через anon key (ваше приложение сейчас так и работает)
revoke all on function public.exec_sql(text) from public;
grant execute on function public.exec_sql(text) to anon, authenticated, service_role;

-- Список всех таблиц в schema public
drop function if exists public.list_public_tables();

create or replace function public.list_public_tables()
returns table(table_name text)
language sql
security definer
set search_path = public
as $$
  select t.tablename::text as table_name
  from pg_catalog.pg_tables t
  where t.schemaname = 'public'
  order by t.tablename;
$$;

revoke all on function public.list_public_tables() from public;
grant execute on function public.list_public_tables() to anon, authenticated, service_role;

-- Колонки конкретной таблицы (строго по имени таблицы)
drop function if exists public.list_table_columns(text);

create or replace function public.list_table_columns(p_table_name text)
returns table(column_name text, data_type text)
language sql
security definer
set search_path = public
as $$
  select
    c.column_name::text,
    c.data_type::text
  from information_schema.columns c
  where c.table_schema = 'public'
    and c.table_name = p_table_name
  order by c.ordinal_position;
$$;

revoke all on function public.list_table_columns(text) from public;
grant execute on function public.list_table_columns(text) to anon, authenticated, service_role;