Antes de pasar a la práctica, vamos a responder a la pregunta: ¿qué es SQL dinámico? Imagina que necesitas crear una tabla con un nombre único que se pasa como parámetro. O ejecutar una consulta a una tabla cuyo nombre se determina en tiempo de ejecución del programa. Aquí el SQL estático no es suficiente — y es justo donde entra la ejecución dinámica.
PL/pgSQL te da el comando EXECUTE, que ejecuta una consulta SQL pasada como string. Esto te permite construir y lanzar código SQL "al vuelo", creando consultas que cambian según los parámetros.
Razones por las que el SQL dinámico puede ser útil:
- Flexibilidad: Posibilidad de construir consultas dinámicamente según los datos de entrada. Por ejemplo, operar sobre tablas o columnas cuyos nombres no se conocen de antemano.
- Automatización: Crear tablas o índices con nombres únicos.
- Universalidad: Poder trabajar con diferentes estructuras de datos sin tener que reescribir el procedimiento.
Ejemplo de la vida real: imagina que estás desarrollando un sistema de analítica, y para cada cliente nuevo necesitas crear una tabla separada para guardar sus datos. Todo esto se puede automatizar usando EXECUTE.
Sintaxis de EXECUTE
Usar SQL dinámico con EXECUTE se ve así:
EXECUTE 'cadena-SQL';
Ejemplo de consulta simple:
DO $$
BEGIN
EXECUTE 'CREATE TABLE test_table (id SERIAL PRIMARY KEY, name TEXT)';
END $$;
Este bloque de código va a crear la tabla test_table. Fácil, pero vamos a ver casos más complejos.
Ejemplos de uso de EXECUTE
1. Crear una tabla con nombre dinámico
Supón que tienes que crear tablas cuyos nombres dependen de la fecha actual. Así es como se hace:
DO $$
DECLARE
table_name TEXT;
BEGIN
-- Generamos el nombre de la tabla
table_name := 'report_' || to_char(CURRENT_DATE, 'YYYYMMDD');
-- Creamos la tabla con nombre dinámico
EXECUTE 'CREATE TABLE ' || table_name || ' (id SERIAL PRIMARY KEY, data TEXT)';
-- Mostramos un mensaje para comprobar
RAISE NOTICE 'Tabla % creada con éxito', table_name;
END $$;Aquí el nombre dinámico se genera a partir de la fecha actual, y la cadena SQL final se pasa a EXECUTE.
2. Ejecutar una consulta con parámetros dinámicos
Supón que necesitas extraer datos de una tabla cuyo nombre se pasa como parámetro. Vamos a crear una función para esto:
CREATE OR REPLACE FUNCTION get_data_from_table(table_name TEXT)
RETURNS TABLE(id INTEGER, name TEXT) AS $$
BEGIN
RETURN QUERY EXECUTE
'SELECT id, name FROM ' || table_name || ' WHERE id < 10';
END $$ LANGUAGE plpgsql;
Llamada a la función:
SELECT * FROM get_data_from_table('employees');
Este enfoque va genial para construir utilidades universales, como sistemas de reportes dinámicos.
Problemas y limitaciones del SQL dinámico
La ejecución dinámica de código SQL te da mucha libertad, pero como en la vida, la libertad viene con responsabilidad. Aquí es donde pueden aparecer problemas:
Inyecciones SQL: si pasas parámetros de tipo string a la consulta sin procesarlos, puedes darle a un atacante la oportunidad de ejecutar cualquier código SQL.
Ejemplo de código vulnerable:
EXECUTE 'SELECT * FROM users WHERE name = ''' || user_input || '''';Si
user_inputcontiene la cadena'; DROP TABLE users; --, la consulta destruirá la tablausers.Dificultad de depuración: el código dinámico es más difícil de analizar y depurar, porque la consulta se construye y ejecuta en tiempo de ejecución.
- Pérdida de rendimiento: las consultas dinámicas saltan los mecanismos de caché de planes de ejecución en PostgreSQL, lo que puede bajar el rendimiento.
Cómo protegerse de las inyecciones SQL
Para evitar ataques de inyección SQL, usa la parametrización en las consultas dinámicas en vez de concatenar strings a lo loco. En PL/pgSQL esto se hace con la función quote_literal() para parámetros de tipo string y quote_ident() para identificadores (como nombres de tablas o columnas).
Ejemplo de código seguro:
DO $$
DECLARE
table_name TEXT;
user_input TEXT := 'John';
BEGIN
table_name := 'employees';
EXECUTE 'SELECT * FROM ' || quote_ident(table_name) ||
' WHERE name = ' || quote_literal(user_input);
END $$;Implementación: actualización dinámica de tablas
Aquí tienes un ejemplo de procedimiento que actualiza valores en una tabla cuyo nombre se pasa como parámetro:
CREATE OR REPLACE FUNCTION update_table_data(table_name TEXT, id_value INT, new_data TEXT)
RETURNS VOID AS $$
BEGIN
EXECUTE 'UPDATE ' || quote_ident(table_name) ||
' SET data = ' || quote_literal(new_data) ||
' WHERE id = ' || id_value;
END $$ LANGUAGE plpgsql;
Llamada a la función:
SELECT update_table_data('test_table', 1, 'Valor Actualizado');
Ejemplo: crear un reporte para un cliente
Supón que llevas el control de pedidos por cliente y quieres automatizar el proceso de crear una tabla de reportes para cada cliente.
CREATE OR REPLACE FUNCTION create_client_report(client_id INT)
RETURNS VOID AS $$
DECLARE
table_name TEXT;
BEGIN
-- Formamos el nombre de la tabla de reporte
table_name := 'client_report_' || client_id;
-- Creamos la tabla para el reporte
EXECUTE 'CREATE TABLE ' || quote_ident(table_name) || ' (order_id INT, amount NUMERIC)';
-- Rellenamos la tabla con datos
EXECUTE 'INSERT INTO ' || quote_ident(table_name) ||
' SELECT order_id, amount FROM orders WHERE client_id = ' || client_id;
RAISE NOTICE 'Reporte para cliente % creado: tabla %', client_id, table_name;
END $$ LANGUAGE plpgsql;El SQL dinámico con EXECUTE es una herramienta potente que abre posibilidades brutales para la automatización y flexibilidad en PL/pgSQL. Úsalo con cabeza, recordando los riesgos de las inyecciones SQL. Si quieres que tus consultas sean seguras y fiables, usa siempre las funciones quote_ident() y quote_literal().
En la próxima lección vamos a profundizar en la creación de procedimientos complejos, incluyendo validación de datos, actualización de registros y logging de operaciones. ¡Prepárate porque trabajar con consultas dinámicas será la base para implementar este tipo de tareas!
GO TO FULL VERSION