Creando Funciones agregadas en PostgreSQL

Cuando usamos GROUP BY en un Query, usualmente usamos las funciones de agrupamiento o funciones agregadas, tales como count(), avg(), sum()

Pero a veces se quedan cortas estas operaciones ya que no necesitamos sumar sino concatenar, o la suma no nos sirve sino necesitamos sacar una suma de cuadrados.

Pues en postgres esto es muy sencillo y les voy a dejar un par de ejemplos usando textos:

Vamos a tener 2 tablas (objeto y etiqueta_objeto) con los siguientes datos:

Vamos a crear una función para poder concatenar 2 textos usando una "," y luego la vamos a usar en una función agregada :

CREATE OR REPLACE FUNCTION concat(text, text)
RETURNS text AS
$BODY$
DECLARE
t text;
BEGIN
IF character_length($1) > 0 THEN
IF character_length($2) > 0 THEN
t = $1 ||’, ‘|| $2;
else
t = $1;
end if;
ELSE
t = $2;
END IF;
RETURN t;
END;
$BODY$
LANGUAGE ‘plpgsql’ VOLATILE;

Y la función agregada es:

CREATE AGGREGATE concatena(
BASETYPE=text,
SFUNC=concat,
STYPE=text
);

Si quisieramos listar todas las etiquetas de un objeto tendríamos que hacer algo como

SELECT nombre,etiqueta as etiquetas FROM objeto JOIN etiqueta_objeto USING (id_objeto) GROUP BY nombre,etiqueta ORDER BY nombre;

Con esto obtendríamos:

Si quisieramos obtener un listado con filas con el nombre y el listado de etiquetas separado por comas ("casa";"blanca,grande,vieja"). tendríamos que concatenar cada etiqueta del lado de nuestra progra, o de alguna otra forma (que ahora no se me ocurre, :P). Pero usando la función agregada en el query tenemos:

SELECT nombre,concatena(etiqueta) AS etiquetas FROM objeto JOIN etiqueta_objeto USING (id_objeto) GROUP BY nombre ;

Esta otra función con hace algo parecido pero en vez de dejarlo en un campo de tipo texto, crea un arreglo con todo las etiquetas (esto por si lo van a utlizar como arreglo, etc).

CREATE AGGREGATE array_accum(
BASETYPE=anyelement,
SFUNC=array_append,
STYPE=anyarray,
INITCOND='{}’
);

SELECT nombre,array_accum(etiqueta) AS etiquetas FROM objeto JOIN etiqueta_objeto USING (id_objeto) GROUP BY nombre ;

Aquí esta la documentación oficial para crear funciones agregadas en PostgreSQL.

Bueno… pues allí les dejo los Script para probar, y espero que les sirva el ejemplo.

Hasta la próxima y haber quien deja el query para que las etiquetas salgan en orden alfabético, se los dejo de tarea :). Saludos!