Escribiendo procedimientos almacenados en postgresql 8.3

January 2, 2009 a las 3:43 pm por Edwin Plauchu · Archivado en Programacion

-- Function: paginacion_pais(integer, integer)

-- DROP FUNCTION paginacion_pais(integer, integer);

CREATE OR REPLACE FUNCTION paginacion_pais(number_of_items integer, page_number integer)
  RETURNS SETOF pais AS
$BODY$

	DECLARE
		r pais%ROWTYPE;
		maximo int:= page_number * number_of_items;
		items int:=1;
		num_items int:=0;
		total_items int:=0;
		diferencia int:=0;
		minimo int:= maximo - number_of_items +1;
	BEGIN

	        EXECUTE 'select count(*) from pais' INTO total_items;
	        IF maximo > total_items THEN
	          diferencia = maximo - total_items;
	        END IF;
                IF diferencia < number_of_items THEN
		FOR r IN SELECT * FROM pais
		LOOP
			-- can do some processing here
			if items = minimo then
			IF minimo <= maximo THEN

			RETURN NEXT r; -- return current row of SELECT
                        minimo=minimo+1;
			END IF;
			end if;
			items=items+1;
		END LOOP;

		END IF;

	RETURN;
	END

$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100
  ROWS 1000;

Forma de uso:

select * from paginacion_pais(100,25 );

2 Comentarios