PostgreSQL. Uso de procedimientos para listados.


En la actualidad, PostgreSQL tiene las herramientas necesarias para que pueda usarse como capa de negocio, algo no recomendable con otros SGBDs. Dedicamos esta entrada de nuestro blog a un hecho que va en esta línea, para explicar paso a paso cómo desarrollar un procedimiento almacenado en PLpgSQL que devuelva varios registros.

En el primer ejemplo vamos a aprovechar el hecho que en sus procedimientos escritos en PLpgSQL, PostgreSQL permite crear variables locales del tipo compuesto igual a una fila de una tabla existente. Es muy útil definir así las variables pues evitaremos tener que modificar la función en caso que se produzcan cambios de tipo en las columnas de la tabla. Empecemos.

Supongamos que tenemos la siguiente tabla:

CREATE TABLE _user (_id bigint NOT NULL, name text NOT NULL, departmentid integer

NOT NULL);

Con los siguientes registros:

insert into _user values(1,'James',345);
insert into _user values(2,'Peter',323);
insert into _user values(3,'John',63);
insert into _user values(4,'Peter',2345);

Para crear una función que nos devuelva todos sus registros, lo haremos del siguiente modo:

CREATE OR REPLACE FUNCTION user_list()
RETURNS setof _user AS 
$BODY$
DECLARE 
    r _user%ROWTYPE; -- Variable local del tipo fila para la tabla _user.

    -- La parte "%ROWTYPE" es opcional para las versiones actuales de PG.

    r_id _user._id%TYPE; -- No la vamos a usar pero incluimos esta línea

    -- a modo de ejemplo para la definición de una variable local

    -- heredando el tipo de datos de una columna concreta de tabla/vista.

BEGIN

for r in select * from _user loop
    return next r;
end loop;

end;

$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

Destacamos lo siguiente en la creación del procedimiento anterior:

  • Definición el tipo de retorno como “setof _user“. Esto es, un conjunto de registros del tipo de tabla _user.
  • Declaración de variable local que contendrá cada registro: r _user%ROWTYPE.
  • Para devolver todo registro devuelto en el SELECT: return next r;

Ya con el procedimiento creado, la siguiente consulta SQL nos devolverá todos los registros de la tabla:

SELECT * FROM user_list()

Resultado:

1;"James";345
2;"Peter";323
3;"John";63
4;"Peter";2345

Entonces, ¿cómo conseguiríamos los usuarios con nombre “Peter”? Sencillamente tratando el procedimiento como si de una tabla se tratara:

SELECT * FROM user_list() WHERE name='Peter';

Resultado:

2;"Peter";323
4;"Peter";2345

Veamos un ejemplo más complejo usando un tipo creado por nosotros en vez de una variable local de tipo heredado de tabla.

Imaginémonos que tenemos un listado de usuarios en una aplicación web en el que permitimos filtrar por nombre. Aprovecharemos para trasladar la lógica de filtrado al procedimiento.

Primero creamos el tipo compuesto de datos que vamos a usar como contenedor de los datos. Para simplificar lo definiremos con la misma estructura que nuestra tabla _user:

CREATE TYPE data_content AS
(
_id bigint,
name text,
departmentid integer
);

Y creamos el procedimiento siguiente:

CREATE OR REPLACE FUNCTION user_weblist(name_ text) RETURNS setof data_content AS 
$BODY$
DECLARE

    r record;
    sql text;
BEGIN

sql := 'SELECT _id,name,departmentid FROM _user WHERE 1=1 '; -- Para

-- simplificar ejecutamos un texto.

-- Lógica de filtrado
IF name_ IS NOT NULL THEN sql := sql || ' AND name='||quote_literal(name_); END IF;

for r in execute sql loop
    return next r;
end loop;
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

Diferencias respecto el primer procedimiento:

  • Definición el tipo de retorno usando el tipo creado: RETURNS setof data_content.
  • Ahora la variable “r” es del tipo record.

Con esta estructura, desde el Back End de nuestra web únicamente deberemos llamar al procedimiento indicando como parámetro el valor introducido por el usuario de nuestro filtro. La siguiente consulta muestra cómo buscar a los Peter:

SELECT * FROM user_weblist('Peter')

Sentencia que nos devuelve los mismos registros que la primera función user_list():

2;"Peter";323
4;"Peter";2345

El ejemplo mostrado es muy sencillo, de un solo filtro, pero se ha de tener presente que el SELECT realizado en el procedimiento puede ejecutar cualquier consulta; usar tablas cruzadas, funciones escalares, etc. Lo único que deberemos tener en cuenta es crear un tipo compuesto que pueda contener los datos devueltos. Para listados complejos, se crearía un procedimiento incluyendo como parámetro todos sus filtros e incluso las variables destinadas a paginación y ordenación del listado.

Ya para finalizar, un aspecto interesante de estos procedimientos es que pueden incluirse en una consulta de este modo:

SELECT u._id,u.name
FROM user_weblist('Peter') u
INNER JOIN _department d ON (u.departmentid = d._id);

Esperamos que esta entrada os haya servido de ayuda.


Leave a Reply

Your email address will not be published. Required fields are marked *