Una de las características destacadas de PostgreSQL es su amplio soporte para funciones y tipos de datos definidos por el usuario. Esto permite a los desarrolladores crear funciones personalizadas de conversión, de operación y de agregación. Los agregados ofrecen una forma poderosa de realizar cálculos y transformaciones complejas en los datos, yendo más allá de las funciones de agregado SQL estándar como SUM, AVG y COUNT. Tanto Navicat for PostgreSQL and Navicat Premium facilitan la escritura de funciones y agregados personalizados que se integran perfectamente con la base de datos, gracias a su interfaz gráfica de usuario (GUI) especializada. ¡Todo lo que necesitamos hacer es proporcionar algunos detalles y Navicat nos crea la declaración pgSQL! En el blog de hoy, crearemos un agregado para trabajar con DVD Rental database que concatena los títulos de las películas por categoría.
Acerca de los agregados
Las agregaciones son una característica fundamental de SQL que le permite realizar cálculos o transformaciones en un conjunto de filas y devolver un único resultado. Las funciones de agregado más comunes son SUM, AVG, COUNT, MIN y MAX, las cuales le permiten resumir rápidamente los datos calculando totales, promedios, recuentos, valores mínimos y valores máximos, respectivamente.
Sin embargo, las funciones de agregado integradas que proporciona SQL no siempre satisfacen las necesidades específicas de una aplicación. Aquí es donde resulta útil la capacidad de crear agregados personalizados. Los agregados personalizados le permiten definir su propia lógica para resumir y transformar datos, yendo más allá del conjunto estándar de agregados de SQL. El proceso generalmente implica definir una función de transición de estado, la cual llama a cada fila para actualizar un acumulador, así como una función final opcional que se llama para producir el resultado agregado final.
Generación de las funciones de transición y final
Nuestra función de transición, array_append_state(), será llamada para cada fila para actualizar el estado agregado.
Para acceder al editor de funciones de Navicat, haga clic en el botón Función en la barra de botones principal y luego haga clic en "Nueva función" en la barra de herramientas Objetos:
Navicat comenzará con la definición de la función principal. A partir de allí, proporcionaremos el nombre de la función, los parámetros de entrada y el cuerpo:
CREATE FUNCTION "public"."array_append_state" (current_state text[], new_value text) RETURNS text[] AS $BODY$ BEGIN RETURN array_append(current_state, new_value); END $BODY$ LANGUAGE 'plpgsql' VOLATILE;
Al terminar, podemos hacer clic en Guardar para crear la función.
Ahora volveremos a la pestaña Objetos y haremos clic en "Nueva función" para crear la función final.
La función array_to_comma_string() tomará una matriz de títulos de películas e insertará una coma entre cada elemento:
CREATE FUNCTION "public"."array_to_comma_string" (state text[]) RETURNS text AS $BODY$ BEGIN RETURN array_append(state, ', '); END $BODY$ LANGUAGE 'plpgsql' VOLATILE;
Crear función de agregación comma_concat()
Ahora podemos conectar nuestras dos funciones al editor de agregación de Navicat. Podemos acceder al editor haciendo clic en el botón Otros en la barra de botones principal y luego seleccionando "Agregar" en el menú contextual:
En el formulario, estableceremos el tipo de entrada en "texto", ingresaremos un Tipo de Estado de "texto[]" y proporcionaremos nuestro estado y las Funciones finales. Además, nos aseguraremos de que la condición inicial sea una matriz vacía ("{}"):
Podemos ver el SQL generado haciendo clic en la pestaña Vista previa:
CREATE AGGREGATE "public"."Untitled" (In "pg_catalog"."text")
(
SFUNC = "public"."array_append_state",
STYPE = "pg_catalog"."text[]",
FINALFUNC = "public"."array_to_comma_string",
INITCOND = "{}",
PARALLEL = UNSAFE
);
ALTER AGGREGATE "public"."Untitled"("pg_catalog"."text") OWNER TO "postgres";
Observe que el nombre del agregado es "Sin título". Navicat nos solicitará el nombre cuando presionemos el botón Guardar y ejecutemos el comando con el nombre que le proporcionemos.
Usar nuestras funciones de agregado personalizadas
Ahora podemos invocar nuestra función de agregado como cualquier otra función. Aquí hay una consulta que obtiene una lista de películas por categoría:
SELECT
c.name AS category,
comma_concat(f.title) AS movies
FROM category c
JOIN film_category fc ON c.category_id = fc.category_id
JOIN film f ON fc.film_id = f.film_id
GROUP BY c.name
ORDER BY c.name;
Conclusión
En el blog de hoy, hemos creado un agregado PostgreSQL personalizado en Navicat Premium para trabajar con la base de datos de alquiler de DVD que concatena los títulos de las películas por categoría.
¿Le interesa probar Navicat Premium 17? Puede descargar para una prueba GRATUITA de 14 días con todas las funcionalidades aquí 14-day fully functional FREE trial. Está disponible para los sistemas operativos Windows, macOS y Linux.
