MODULO II Programación de funciones en PL/pgSQL para PostgreSQL

Eduardo Calderón - Taller de Base de Datos Orientados a Objetos

PROCEDIMIENTOS ALMACENADOS

Estructura de un Procedimiento Almacenado

El lenguaje PL/pgSQL es estructura en bloques. Todas las palabras clave y los identificadores pueden escribirse mezclando letras mayúsculas y minúsculas. Un bloque se define de la siguiente manera:

Declaraciones, constantes, variables y alias.

Las declaraciones de las variables son en cada Bloque, esto nos permite, aislar valores, la sintaxis, para la declaración es de la siguiente forma:

CREATE OR REPLACE FUNCTION Nombre(integer) RETURNS integer AS $$ DECLARE …. BEGIN ….. END; $$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION DECLARACIONES(integer,integer) RETURNS Text AS $$ DECLARE suma integer:=0; txtmenor varchar(12):='MENOR QUE 25'; txtmayor varchar(12):='MAYOR QUE 25'; txtigual varchar(12):='IGUAL QUE 25'; numero1 ALIAS FOR $1; constante CONSTANT integer := 100; BEGIN suma:=numero1+$2; IF suma < 25 THEN RETURN txtmenor; ELSE IF suma > 25 THEN RETURN txtmayor; ELSE RETURN txtigual; END IF; END IF; END; $$ LANGUAGE plpgsql;

MODULO II Programación de funciones en PL/pgSQL para PostgreSQL

Eduardo Calderón - Taller de Base de Datos Orientados a Objetos

Estructura de Control .

El siguiente ejemplo, se hace el uso del CASE, una sentencia de control, que nos permite evaluar varias condiciones.

Estructura de iteración FOR, WHILE.

El siguiente procedimiento, acepta por parámetros un número del tipo entero y retorna el mismo tipo, lo único que hace es un FOR, de 1 hasta el valor del parámetro que le enviamos. El siguiente procedimiento, acepta por parámetros un número del tipo entero y retorna el mismo tipo, lo único que hace es un WHILE hasta que el valor del parámetro sea igual al que le enviamos.

CREATE OR REPLACE FUNCTION PARA(integer) RETURNS integer AS $$ DECLARE SUMA integer:=0; CONTADOR integer:=0; BEGIN FOR CONTADOR IN 1..$1 LOOP SUMA:=SUMA+$1; END LOOP; RETURN SUMA; END; $$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION MIENTRAS(integer) RETURNS integer AS $$ DECLARE SUMA integer:=0; CONTADOR integer:=0; BEGIN WHILE CONTADOR <> $1 LOOP SUMA:=SUMA+$1; CONTADOR:=CONTADOR+1; END LOOP; RETURN SUMA; END; $$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION foo(int) RETURNS void AS $$ BEGIN CASE $1 WHEN 1,2,3 THEN RAISE NOTICE '1,2'; RAISE NOTICE '3'; WHEN 4 THEN RAISE NOTICE '4'; ELSE RAISE NOTICE 'other than 1,2,3,4'; END CASE; RETURN; END; $$ LANGUAGE plpgsql;

MODULO II Programación de funciones en PL/pgSQL para PostgreSQL

Eduardo Calderón - Taller de Base de Datos Orientados a Objetos

Abortando la Ejecución y Mensajes.

Excepxiones. El siguiente procedimiento, podemos ver como se maneja la excepción, en este caso, conrolamos la sentencia INSERT, cuando se viola la restriccion de UNIQUE.

CREATE OR REPLACE FUNCTION MENSAJES2(integer,integer) RETURNS Text AS $$ DECLARE suma integer:=0; txtmenor varchar(12):='MENOR QUE 25'; txtmayor varchar(12):='MAYOR QUE 25'; numero1 ALIAS FOR $1; constante CONSTANT integer := 100; BEGIN suma:=numero1+$2; IF suma < 25 THEN RAISE NOTICE 'SUMA %' ,txtmenor; ELSE RAISE EXCEPTION 'SUMA %' ,txtmenor; END IF; END; $$ LANGUAGE plpgsql;

CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS $$ BEGIN LOOP UPDATE db SET b = data WHERE a = key; IF found THEN RETURN; END IF; BEGIN INSERT INTO db(a,b) VALUES (key, data); RETURN; EXCEPTION WHEN unique_violation THEN -- do nothing END; END LOOP; END; $$ LANGUAGE plpgsql;

MODULO II Programación de funciones en PL/pgSQL para PostgreSQL

Eduardo Calderón - Taller de Base de Datos Orientados a Objetos

Retorno Booleano

Arreglos

Devolviendo una tabla

• Se crea la tabla foo. • Se insertan los Datos

CREATE FUNCTION booleano(integer) RETURNS bool AS $$ DECLARE num ALIAS FOR $1; BEGIN IF num ISNULL THEN RETURN 'f'; ELSE IF NUM < 100 THEN RETURN 'f'; ELSE RETURN 't'; END IF; END IF; END; $$ LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION TEXTO(INTEGER) RETURNS INTEGER ARRAY AS $$ DECLARE vect INTEGER ARRAY[5]:='{1,5,3,4,5}'; vector integer array; i INTEGER:=1; BEGIN FOR i IN 1..$1 LOOP vector[i]:=vect[I]; END LOOP; RETURN vector; END; $$ LANGUAGE plpgsql;

MODULO II Programación de funciones en PL/pgSQL para PostgreSQL

Eduardo Calderón - Taller de Base de Datos Orientados a Objetos

• Se muestran los datos a través del PROC ALM

CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT); INSERT INTO foo VALUES (1, 2, 'three'); INSERT INTO foo VALUES (4, 5, 'six'); CREATE OR REPLACE FUNCTION MOSTRAR() RETURNS SETOF foo AS $$ DECLARE r foo%rowtype; BEGIN FOR r IN SELECT * FROM foo WHERE fooid > 0 LOOP RETURN NEXT r; END LOOP; RETURN; END $$ LANGUAGE 'plpgsql' ;

MODULO II Programación de funciones en PL/pgSQL para PostgreSQL

Eduardo Calderón - Taller de Base de Datos Orientados a Objetos

Retorno de campos de varias tablas.

CREATE TABLE vuelos (vu_id INT, vu_vuelo varchar(5), vu_aerolinea varchar(20), vu_fecha date, vu_hora time); CREATE TABLE pasajero (pas_id INT, pas_nombre varchar(20), pas_rut varchar(15), pas_domicilio varchar(40)); CREATE TABLE viaje (via_id INT, via_vuelo varchar(5), via_rut varchar(15)); --------------------------------------------------------- --INSERT INTO pasajero VALUES (1, 'EDUARDO', '10.125.150','LAS CONDES'); --INSERT INTO vuelos VALUES (1, 'LA380', 'LAN','2010-04-26','16:00'); --INSERT INTO viaje VALUES (1, 'LA380', '10.125.150'); -------------------------------------------------------- --Select * from pasajero; --Select * from VUELOS; --Select * from Viaje; --CREACION DE UNA VISTA TIPO------- CREATE TYPE vuelopasajero AS (vu_vuelo varchar(5), pas_rut varchar(15), vu_fecha date, vu_hora time) --CREACION DE PROCEDIMIENTO RETORNANDO VISTA TIPO------------ CREATE OR REPLACE FUNCTION vueloshoy() RETURNS SETOF vuelopasajero AS $$ DECLARE R vuelopasajero%rowtype; BEGIN FOR R IN SELECT vu_vuelo, pas_rut, vu_fecha, vu_hora FROM vuelos, pasajero,viaje WHERE via_rut = pas_rut AND via_vuelo = vu_vuelo AND vu_fecha BETWEEN(current_date) AND (current_date + 2) LOOP RETURN NEXT R; END LOOP; RETURN; END $$ LANGUAGE 'plpgsql';