Registrarse

Funciones y triggers

Cree funciones y triggers de base de datos para automatizar la lógica.

Nota: Este es contenido de ejemplo/marcador de posición con fines de demostración.

Las funciones y los triggers de base de datos permiten implementar lógica y automatización en el servidor.

Funciones de base de datos

Crear una función

CREATE OR REPLACE FUNCTION get_user_projects(user_id UUID)
RETURNS TABLE (
  id UUID,
  name TEXT,
  created_at TIMESTAMPTZ
)
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  RETURN QUERY
  SELECT p.id, p.name, p.created_at
  FROM projects p
  INNER JOIN accounts_memberships am ON am.account_id = p.account_id
  WHERE am.user_id = get_user_projects.user_id;
END;
$$;

Llamada desde TypeScript

const { data, error } = await client.rpc('get_user_projects', {
  user_id: userId,
});

Patrones de funciones habituales

Obtener las cuentas del usuario

CREATE OR REPLACE FUNCTION get_user_accounts(user_id UUID)
RETURNS TABLE (account_id UUID)
LANGUAGE sql
SECURITY DEFINER
AS $$
  SELECT account_id
  FROM accounts_memberships
  WHERE user_id = $1;
$$;

Comprobar permisos

CREATE OR REPLACE FUNCTION has_permission(
  user_id UUID,
  account_id UUID,
  required_role TEXT
)
RETURNS BOOLEAN
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  user_role TEXT;
BEGIN
  SELECT role INTO user_role
  FROM accounts_memberships
  WHERE user_id = has_permission.user_id
    AND account_id = has_permission.account_id;

  RETURN user_role = required_role OR user_role = 'owner';
END;
$$;

Función de búsqueda

CREATE OR REPLACE FUNCTION search_projects(
  search_term TEXT,
  account_id UUID
)
RETURNS TABLE (
  id UUID,
  name TEXT,
  description TEXT,
  relevance REAL
)
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  RETURN QUERY
  SELECT
    p.id,
    p.name,
    p.description,
    ts_rank(
      to_tsvector('english', p.name || ' ' || COALESCE(p.description, '')),
      plainto_tsquery('english', search_term)
    ) AS relevance
  FROM projects p
  WHERE p.account_id = search_projects.account_id
    AND (
      to_tsvector('english', p.name || ' ' || COALESCE(p.description, ''))
      @@ plainto_tsquery('english', search_term)
    )
  ORDER BY relevance DESC;
END;
$$;

Triggers

Actualización automática de la marca de tiempo

-- Crear la función del trigger
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$;

-- Asociar a la tabla
CREATE TRIGGER update_projects_updated_at
  BEFORE UPDATE ON projects
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

Trigger de registro de auditoría

-- Crear la tabla de registro de auditoría
CREATE TABLE audit_log (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  table_name TEXT NOT NULL,
  record_id UUID NOT NULL,
  action TEXT NOT NULL,
  old_data JSONB,
  new_data JSONB,
  user_id UUID,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Crear la función del trigger
CREATE OR REPLACE FUNCTION log_changes()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    INSERT INTO audit_log (table_name, record_id, action, new_data, user_id)
    VALUES (TG_TABLE_NAME, NEW.id, 'INSERT', to_jsonb(NEW), auth.uid());
    RETURN NEW;
  ELSIF TG_OP = 'UPDATE' THEN
    INSERT INTO audit_log (table_name, record_id, action, old_data, new_data, user_id)
    VALUES (TG_TABLE_NAME, NEW.id, 'UPDATE', to_jsonb(OLD), to_jsonb(NEW), auth.uid());
    RETURN NEW;
  ELSIF TG_OP = 'DELETE' THEN
    INSERT INTO audit_log (table_name, record_id, action, old_data, user_id)
    VALUES (TG_TABLE_NAME, OLD.id, 'DELETE', to_jsonb(OLD), auth.uid());
    RETURN OLD;
  END IF;
END;
$$;

-- Asociar a la tabla
CREATE TRIGGER audit_projects
  AFTER INSERT OR UPDATE OR DELETE ON projects
  FOR EACH ROW
  EXECUTE FUNCTION log_changes();

Borrado lógico en cascada

CREATE OR REPLACE FUNCTION soft_delete_cascade()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  -- Borrado lógico de las tareas relacionadas
  UPDATE tasks
  SET deleted_at = NOW()
  WHERE project_id = OLD.id
    AND deleted_at IS NULL;

  RETURN OLD;
END;
$$;

CREATE TRIGGER soft_delete_project_tasks
  BEFORE DELETE ON projects
  FOR EACH ROW
  EXECUTE FUNCTION soft_delete_cascade();

Triggers de validación

Aplicar reglas de negocio

CREATE OR REPLACE FUNCTION validate_project_budget()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  IF NEW.budget < 0 THEN
    RAISE EXCEPTION 'Budget cannot be negative';
  END IF;

  IF NEW.budget > 1000000 THEN
    RAISE EXCEPTION 'Budget cannot exceed 1,000,000';
  END IF;

  RETURN NEW;
END;
$$;

CREATE TRIGGER check_project_budget
  BEFORE INSERT OR UPDATE ON projects
  FOR EACH ROW
  EXECUTE FUNCTION validate_project_budget();

Evitar registros huérfanos

CREATE OR REPLACE FUNCTION prevent_owner_removal()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
  owner_count INTEGER;
BEGIN
  IF OLD.role = 'owner' THEN
    SELECT COUNT(*) INTO owner_count
    FROM accounts_memberships
    WHERE account_id = OLD.account_id
      AND role = 'owner'
      AND id != OLD.id;

    IF owner_count = 0 THEN
      RAISE EXCEPTION 'Cannot remove the last owner of an account';
    END IF;
  END IF;

  RETURN OLD;
END;
$$;

CREATE TRIGGER check_owner_before_delete
  BEFORE DELETE ON accounts_memberships
  FOR EACH ROW
  EXECUTE FUNCTION prevent_owner_removal();

Columnas calculadas

Columna virtual con una función

CREATE OR REPLACE FUNCTION project_task_count(project_id UUID)
RETURNS INTEGER
LANGUAGE sql
STABLE
AS $$
  SELECT COUNT(*)::INTEGER
  FROM tasks
  WHERE project_id = $1
    AND deleted_at IS NULL;
$$;

-- Uso en consultas
SELECT
  id,
  name,
  project_task_count(id) as task_count
FROM projects;

Notificaciones de eventos

Notificar los cambios

CREATE OR REPLACE FUNCTION notify_project_change()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  PERFORM pg_notify(
    'project_changes',
    json_build_object(
      'operation', TG_OP,
      'record', NEW
    )::text
  );
  RETURN NEW;
END;
$$;

CREATE TRIGGER project_change_notification
  AFTER INSERT OR UPDATE ON projects
  FOR EACH ROW
  EXECUTE FUNCTION notify_project_change();

Escuchar desde TypeScript

const channel = client
  .channel('project_changes')
  .on(
    'postgres_changes',
    {
      event: '*',
      schema: 'public',
      table: 'projects',
    },
    (payload) => {
      console.log('Project changed:', payload);
    }
  )
  .subscribe();

Funciones de seguridad

Función auxiliar para Row Level Security

CREATE OR REPLACE FUNCTION is_account_member(account_id UUID)
RETURNS BOOLEAN
LANGUAGE sql
SECURITY DEFINER
STABLE
AS $$
  SELECT EXISTS (
    SELECT 1
    FROM accounts_memberships
    WHERE account_id = $1
      AND user_id = auth.uid()
  );
$$;

-- Uso en una política RLS
CREATE POLICY "Users can access their account's projects"
  ON projects FOR ALL
  USING (is_account_member(account_id));

Funciones programadas

Uso de la extensión pg_cron

-- Habilitar la extensión pg_cron
CREATE EXTENSION IF NOT EXISTS pg_cron;

-- Programar la tarea de limpieza
SELECT cron.schedule(
  'cleanup-old-sessions',
  '0 2 * * *', -- Todos los días a las 2:00
  $$
  DELETE FROM sessions
  WHERE expires_at < NOW();
  $$
);

Buenas prácticas

  1. Use SECURITY DEFINER con precaución - Puede eludir RLS
  2. Añada gestión de errores - Utilice bloques EXCEPTION
  3. Mantenga las funciones simples - Una responsabilidad por función
  4. Documente las funciones - Añada comentarios
  5. Pruebe a fondo - Escriba pruebas unitarias para las funciones de base de datos
  6. Use STABLE/IMMUTABLE - Optimización del rendimiento
  7. Evite efectos secundarios - Haga que las funciones sean predecibles
  8. Devuelva tipos adecuados - Use RETURNS TABLE para mayor claridad

Pruebas de funciones

-- Probar la función
DO $$
DECLARE
  result INTEGER;
BEGIN
  SELECT project_task_count('some-uuid') INTO result;

  ASSERT result >= 0, 'Task count should not be negative';

  RAISE NOTICE 'Test passed: task count = %', result;
END;
$$;

Depuración

Habilitar el registro de funciones

CREATE OR REPLACE FUNCTION debug_function()
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
  RAISE NOTICE 'Debug: Processing started';
  RAISE NOTICE 'Debug: Current user is %', auth.uid();
  -- La lógica de su función
  RAISE NOTICE 'Debug: Processing completed';
END;
$$;

Comprobar la ejecución de funciones

-- Ver las estadísticas de ejecución de funciones
SELECT
  schemaname,
  funcname,
  calls,
  total_time,
  self_time
FROM pg_stat_user_functions
ORDER BY total_time DESC;