Descarga la aplicación para disfrutar aún más
Vista previa del material en texto
Universidad Nacional de Salta - Facultad de Ciencias Exactas Bases de datos II y Programación avanzadas en bases de datos Postgresql PLPGSQL – FUNCIONES Y TRIGGERS Martín Díaz - Guillermo Villanueva - 2020 Tabla de contenidos Funciones o procedimientos almacenados ................................................................................... 3 Ejemplo de un bloque anónimo ................................................................................................ 4 Declaración de variables ........................................................................................................... 5 Ejemplos ................................................................................................................................ 5 Sentencias básicas ..................................................................................................................... 5 Asignación ............................................................................................................................. 5 Ejecución sin resultado.......................................................................................................... 5 Ejecución con resultado ........................................................................................................ 5 Ejemplos ............................................................................................................ 5 Obtener diagnósticos ............................................................................................................ 6 Mensajes y generación de errores ........................................................................................ 6 Level .................................................................................................................. 6 Ejemplos ............................................................................................................ 6 Estructuras de control ............................................................................................................... 7 Condicionales IF..................................................................................................................... 7 Ejemplo .............................................................................................................. 7 Condicionales CASE ............................................................................................................... 7 Ejemplo .............................................................................................................. 7 Loop ....................................................................................................................................... 8 Ejemplo .............................................................................................................. 8 While - Loop .......................................................................................................................... 9 Ejemplo .............................................................................................................. 9 For - Loop (n - veces) ............................................................................................................. 9 Ejemplos: ........................................................................................................... 9 Manejando excepciones o errores ...................................................................................... 10 Ejemplo ............................................................................................................ 10 Retornando valores desde una función .............................................................................. 12 Return .............................................................................................................. 12 Ejercicios prácticos 1: .......................................................................................................... 14 Funciones que devuelven una tabla........................................................................................ 15 Ejemplos: ............................................................................................................................. 16 LOOPING - Recorrer registro por registro el resultado de una consulta ................................ 18 Sintaxis ................................................................................................................................ 18 Ejemplos .............................................................................................................................. 18 Ejercicios prácticos 2: .......................................................................................................... 19 Funciones o procedimientos almacenados En PostgreSQL un procedimiento almacenado se puede definir como un programa, procedimiento o función, el cual está almacenado en la base de datos y listo para ser usado. Las funciones en PostgreSQL se pueden escribir en múltiples lenguajes de programación. En una instalación por defecto de PostgreSQL están disponibles los siguientes lenguajes: PL/pgSQL, PL/Perl, PL/Tcl y PL/Python. PL/pgSQL es muy parecido al lenguaje PL/SQL utilizado por Oracle, es fácil de aprender, potente y siempre está disponible. Los objetivos de PL/pgSQL cuando se creó fueron: Poder ser usado para crear funciones y disparadores (triggers) Añadir estructuras de control al lenguaje SQL Poder realizar cálculos complejos Heredar todos los tipos, funciones y operadores definidos por el usuario Poder ser definido como un lenguaje "de confianza" Fácil de usar PL/pgSQL es un lenguaje estructurado en bloques. Como mínimo existirá un bloque principal en la función y dentro de esta podremos tener sub-bloques. Un bloque se define de la siguiente manera (Todo entre los corchetes [] es opcional): [<< etiqueta >>] [ DECLARE declaraciones de variables ] BEGIN Sentencias; ... ... END [etiqueta]; Algunas reglas: Cada bloque tiene dos secciones: declaraciones y body. La sección de declaraciones es opcional, la sección body es requerida. El bloque finaliza con un punto y coma (;) después de la palabra reservada END. El bloque puede tener una etiqueta ubicada al principio y al final. La sección de declaraciones es donde se declaran todas las variables a utilizar en el bloque, cada sentencia debe finalizar con punto y coma (;). La sección body es donde va el código de la función, cada sentencia también se finalizará con un punto y coma (;). Ejemplo de un bloque anónimo do $$ <<first_block>> declare li_film_count integer := 0; begin -- get the number of films select count(*) into li_film_count from products; -- display a message raise notice 'The number of films is %', li_film_count; end first_block $$; Podemos definir una función persistente en PL/pgSQL de la siguiente manera (sintáxis reducida): CREATE [ OR REPLACE ] FUNCTION nombre_funcion([ [ argmodo ] [ argnombre ] argtipo [, ...] ]) RETURNS tipo AS $$ BLOQUE PRINCIPAL $$ LANGUAGE plpgsql | IMMUTABLE | STABLE | VOLATILE A continuación, algunas de las opciones y valores: argmodo: El modo de un argumento puede ser IN, OUT o INOUT. Por defecto IN. argnombre: nombre del argumento o parámetro. argtipo: Los tipos que podemos utilizar son todos los disponibles en PostgreSQL y todos los definidos por el usuario. declaración de variables: Ver tema mas adelante. IMMUTABLE | STABLE | VOLATILE: IMMUTABLE: Indica que la función no puede alterar a la base de datos y que siempre devolverá el mismo resultado, dados los mismos valores como argumentos. Este tipo de funciones no pueden realizar consultas en la base de datos. STABLE: Indica que la función no puede alterar a la base de datos y que siempredevolverá el mismo resultado en una consulta individual de una tabla, dados los mismos valores como argumentos. El resultado podría cambiar entre sentencias SQL. VOLATILE: Indica que la función puede devolver diferentes valores, incluso dentro de una consulta individual de una tabla (valor por defecto). Declaración de variables En este lenguaje todas las variables a utilizar deben ser declaradas, la sintaxis es la siguiente name [ CONSTANT ] type [ NOT NULL ] [ { DEFAULT | := } expression ]; Ejemplos -- Estandar a utilizer en el curso -- Prefijo: primera letra "l" de local, segunda letra inicial del tipo -- de dato, guion bajo, nombre de variable li_userid integer; ln_cant numeric(5); lv_url varchar := 'http:/www.google.com'; lr_myrow orders%ROWTYPE; lt_myfield customer.firstname%TYPE; lr_fila RECORD; Sentencias básicas Asignación Variable := expresión; Ejecución sin resultado PERFORM consulta; Ejecución con resultado SELECT select_expressions INTO target FROM ...; INSERT ... RETURNING expressions INTO target; UPDATE ... RETURNING expressions INTO target; DELETE ... RETURNING expressions INTO target; Ejemplos SELECT cusomerid INTO li_id FROM customers WHERE custname='Juan Pérez'; Obtener el id del cliente Juan Pérez guardándolo en la variable li_id DELETE FROM customers WHERE custname='Juan Pérez' RETURNING id INTO li_id; Eliminar el cliente con nombre Juan Pérez guardando en la variable li_id el id del cliente eliminado Obtener diagnósticos GET DIAGNOSTICS variable = ROW_COUNT; -- Obtener la cantidad de filas -- afectadas en la última consulta Mensajes y generación de errores RAISE [ level ] 'format' [, expression [, ... ]] Level DEBUG, LOG, INFO, NOTICE, WARNING, EXCEPTION Ejemplos do $$ begin raise info 'information message %', now() ; raise log 'log message %', now(); raise debug 'debug message %', now(); raise warning 'warning message %', now(); raise notice 'notice message %', now(); end $$; -- Solo exception aborta la transacción actual. RAISE EXCEPTION 'ID no existente --> %', id_usuario; Estructuras de control Condicionales IF IF boolean-expression THEN statements [ ELSIF boolean-expression THEN statements [ ELSIF boolean-expression THEN statements ...]] [ ELSE statements ] END IF; Ejemplo IF number = 0 THEN result := 'cero'; ELSIF number > 0 THEN result := 'positivo'; ELSIF number < 0 THEN result := 'negativo'; ELSE -- mm, la única que queda es que sea nulo result := 'Nulo'; END IF; Condicionales CASE CASE WHEN boolean-expression THEN statements [ WHEN boolean-expression THEN statements ... ] [ ELSE statements ] END CASE; Ejemplo CASE WHEN x BETWEEN 0 AND 10 THEN msg := 'El valor está entre 0 y 10'; WHEN x BETWEEN 11 AND 20 THEN msg := 'El valor está entre 11 y 20'; END CASE; Otra sintáxis válida (más estructurada) CASE x WHEN 1, 2 THEN msg := 'uno o dos'; ELSE msg := 'otro valor'; END CASE; Loop Es un ciclo de repetición incondicional que ejecuta un bloque de código repetidamente hasta que termina por un exit o un return. loop statements; if condition then exit; end if; end loop; Ejemplo do $$ <<primer_bloque>> declare li_var integer := 0; begin loop li_var := li_var + 1; exit when li_var = 5; raise notice '%', li_var; end loop; raise notice 'Terminó con: %', li_var; end primer_bloque$$; While - Loop Es un ciclo de repetición condicional que ejecuta un bloque de código repetidamente mientras se cumple la condición. while condición loop statements; end loop; Ejemplo do $$ declare li_var integer := 0; begin while li_var < 5 loop li_var := li_var + 1; raise notice '%', li_var; end loop; raise notice 'Terminó con: %', li_var; end $$; For - Loop (n - veces) Permite iterar un bloque de código una cantidad determinada de veces for loop_counter in [ reverse ] desde..hasta [ by step ] loop statements end loop; Ejemplos: do $$ begin for counter in 1..5 loop raise notice 'counter: %', counter; end loop; end; $$ do $$ begin for counter in reverse 5..1 loop raise notice 'counter: %', counter; end loop; end; $$ Manejando excepciones o errores Cuando se produce un error en un bloque, Postgres abortará la ejecución del mismo y también la transacción correspondiente. Para manejar el error es posible utilizar la sentencia exception: declare begin statements; exception when condition [or condition...] then handle_exception; [when condition [or condition...] then handle_exception;] [when others then handle_other_exceptions; ] end; Primero, cuando ocurre un error entre begin y exception, PL/pgSQL detiene la ejecución y pasa el control a la lista de excepciones. En segundo lugar, PL/pgSQL busca la primera condición que coincida con el error que se produjo. En tercer lugar, si hay una coincidencia, las sentencias handle_exception se ejecutarán. PL/pgSQL pasa el control a la sentencia posterior al END. Finalmente, si no se encuentra ninguna coincidencia, el error se propaga y PL/pgSQL aborta el procesamiento. Existe un gran número de condiciones las cuales están documentadas en https://www.postgresql.org/docs/current/errcodes-appendix.html podrían ser por ejemplo: no_data_found, too_many_rows, datetime_field_overflow, invalid_datetime_format, integrity_constraint_violation, etc. Ejemplo El siguiente código provoca un error porque el cliente -1 no existe: do $$ declare lr_rec record; li_prod_id int = -1; begin -- select a film select prod_id, title into strict lr_rec from products where prod_id = li_prod_id; end; $$ language plpgsql; Salida ERROR: query returned no rows CONTEXT: PL/pgSQL function inline_code_block line 6 at SQL statement SQL state: P0002 Ahora un código similar pero con manejo de errores: do $$ declare lr_rec record; li_prod_id int = -1; begin -- select a film select prod_id, title into strict lr_rec from products where prod_id = li_prod_id; -- catch exception exception when no_data_found then raise exception 'film % not found', li_prod_id; end; $$ language plpgsql; Salida ERROR: film -1 not found CONTEXT: PL/pgSQL function inline_code_block line 14 at RAISE SQL state: P0001 Usando SQLSTATE code do $$ declare lr_rec record; li_length int = 30; begin -- select a film select prod_id, title into strict lr_rec from products where length = li_length; -- catch exception exception when sqlstate 'P0002' then raise exception 'film with length % not found', li_length; when sqlstate 'P0003' then raise exception 'The with length % is not unique', li_length; end; $$ language plpgsql; Retornando valores desde una función Return RETURN [expresión]; Termina la función y retorna el valor especificado en la expresión a quien llamó a la función (caller). Si se declara una función con parámetros de salida, se debe escribir return sin expresión. Si se declara una función de tipo void, return solo servirá para salir de la función en forma anticipada, no debe llevar expresión en este caso. Ejemplo La siguiente función devuelve el cuadrado del número pasado como parámetro. create function get_cuadrado(pi_nro int) returns int as $$ begin return pi_nro * pi_nro; end; $$ language plpgsql immutable; La siguiente función actualiza el precio de un único producto,este tipo de funciones de update o delete es muy útil para evitar peligrosos olvidos de la cláusula where. create or replace function upd_precio(pi_prod_id int, pi_porc int) returns void as $$ begin update products set price = price * (1 + (pi_porc::numeric /100)) where prod_id = pi_prod_id ; return; end; $$ language plpgsql; A continuación, se modifica la función anterior para retornar la cantidad de filas modificadas. create or replace function upd_precio2(pi_prod_id int, pi_porc int) returns int as $$ declare li_filas int:=0; begin update public.products set price = price * (1 + (pi_porc::numeric /100)) where prod_id = pi_prod_id ; get diagnostics li_filas = row_count; if li_filas < 1 then raise notice 'No se encontraron filas'; end if; return li_filas; end; $$ language plpgsql; La forma de ejecutar o llamar la función es: select upd_precio2(-1,50); La siguiente función cuenta la cantidad de películas cuyo precio se encuentra en un intervalo. create function get_film_count(pn_desde numeric(12,2), pn_hasta numeric(12,2)) returns int as $$ declare li_film_count integer; begin select count(*) into li_film_count from products where price between pn_desde and pn_hasta; return li_film_count; end; $$ language plpgsql; Esta función tiene dos secciones, el header y el body Header: se define el nombre de la función get_film_count get_film_count() admite dos parámetros enteros get_film_count() retorna un valor entero Body: Utiliza los delimitadores $$ Declara una variable que utilizará para guardar la cantidad contada en el bloque de código del body utiliza select into para contar los valores almacenando el valor contado en la variable definida. Al final del bloque usa return para devolver el valor contado. Ejercicios prácticos 1: Crear una función que permita eliminar espacios en blanco innecesarios (trim) de una columna de una tabla. Los nombres de columna y tabla deben ser pasados como parámetros y la función deberá devolver como resultado la cantidad de filas afectadas. Utilizar EXECUTE Crear una función que calcule la cantidad de productos vendidos de una categoría dada por parámetro. Luego utilizar la función en un listado de categorías. Crear una función para el devolver el total de una orden dada por parámetro, calcularlo con los datos de orderlines y el precio del producto. Luego utilizar la función en un listado de órdenes del mes marzo de 2004. Funciones que devuelven una tabla Las funciones en postgres pueden devolver también una tabla, en este caso será necesario declarar cada una de las columnas que devuelve con la siguiente sintáxis: CREATE [ OR REPLACE ] FUNCTION nombre_funcion([lista de parámetros]) RETURNS table (<definición de columnas>) AS $$ ... BLOQUE PRINCIPAL ... $$ LANGUAGE plpgsql [ IMMUTABLE | STABLE | VOLATILE] La lista de parámetros tiene el mismo formato que la de créate table, especificando solo nombre de columna y tipo. El BLOQUE PRINCIPAL deberá contener en alguna parte del código lo siguiente: RETURN NEXT [expression]; RETURN QUERY consulta-sql; Cuando una función PL/pgSQL se declara para retornar una tabla, el procedimiento a seguir es un poco diferente. En ese caso, los elementos individuales de retorno se especifican mediante una secuencia de comandos RETURN NEXT o RETURN QUERY, y luego un comando RETURN al final sin argumento que se utiliza para indicar que la función ha terminado de ejecutarse. RETURN NEXT se puede utilizar tanto con tipos escalares y tipos compuestos; con un tipo de resultado compuesto, se devolverá una "tabla" con todo de los resultados. RETURN QUERY anexa los resultados de la ejecución de una consulta al conjunto de resultados de la función. RETURN NEXT y RETURN QUERY pueden mezclarse en una función, en cuyo caso sus resultados serán unidos. Si RETURN NEXT no tiene parámetros utilizará los valores de las variables de contexto cuyo nombre coincida con los nombres de columnas de la tabla a retornar. RETURN NEXT y RETURN QUERY no detiene la ejecución de la función, simplemente anexan cero o más filas al conjunto de resultados de la función. Código de la función BEGIN … … RETURN QUERY… … … RETURN QUERY… … --Preparando variables RETURN NEXT; END Tabla de retorno que va generando la función A diferencia de las otras funciones que devuelven un valor, las funciones que devuelven tablas son llamadas normalmente en la cláusula from de la sentencia select. select <expresiones de columnas> from ... función(parámetros) ... otras expresiones de tabla ... ... Ejemplos: Ejemplo 1 La siguiente función lista los productos que contienen una cierta cadena en el título. CREATE OR REPLACE FUNCTION fn_test(pv_producto VARCHAR) RETURNS TABLE(tv_title VARCHAR) AS $$ BEGIN RETURN QUERY SELECT title FROM products WHERE title ILIKE '%'||pv_producto||'%'; END; $$ LANGUAGE PLPGSQL Ejemplo 2 A diferencia del ejemplo anterior, este utiliza un return query combinado con un return next que agrega un nuevo registro con el valor actual de las variables cuyos nombres coinciden con los nombres de columnas de la tabla a retornar. CREATE OR REPLACE FUNCTION fn_productos(pv_producto VARCHAR) RETURNS TABLE(ti_prod_id int, tv_title VARCHAR) AS $$ BEGIN RETURN QUERY SELECT prod_id, title FROM products WHERE title ILIKE '%'||pv_producto||'%'; -- preparación de variables a devolver ti_prod_id:=0; tv_title:='Producto no listado'; RETURN NEXT; END; $$ LANGUAGE PLPGSQL; La forma de llamar a esta función es: select * from fn_productos('A') order by 2 Ejemplo 3 Esta función que devuelve una tabla con productos de dos categorías pasadas por parámetros, combina dos return next. A lo sumo devolverá 2 registros ya que el SELECT al usar INTO solo asume la primera fila obtenida. CREATE OR REPLACE FUNCTION fn_productos(pi_cat1 int, pi_cat2) RETURNS TABLE( ti_prod_id int, tv_title varchar, tn_price numeric(12,2) ) as $$ BEGIN SELECT prod_id, title, price INTO ti_prod_id, tv_title, tn_price FROM products WHERE category = pi_cat1; RETURN NEXT; SELECT prod_id, title, price INTO ti_prod_id, tv_title, tn_price FROM products WHERE category = pi_cat2; RETURN NEXT; RETURN; END; $$ LANGUAGE 'plpgsql'; La forma de llamar a esta función es: select * from fn_ordenes('1980-01-01') Ejemplo 4 Similar a la función del ejemplo anterior, pero en este caso utiliza return query, entonces incluye todos los registros encontrados en ambas sentencias. CREATE OR REPLACE FUNCTION fn_productos(pi_cat1 int, pi_cat2) RETURNS TABLE( ti_prod_id int, tv_title varchar, tn_price numeric(12,2) ) as $$ BEGIN RETURN QUERY SELECT prod_id, title, price FROM products WHERE category = pi_cat1; RETURN QUERY SELECT prod_id, title, price FROM products WHERE category = pi_cat2; RETURN; END; $$ LANGUAGE 'plpgsql'; La forma de llamar a esta función es: select * from fn_ordenes_rq('1980-01-01') LOOPING - Recorrer registro por registro el resultado de una consulta Mediante el ciclo FOR, se puede iterar a través de los resultados de una consulta y manipular los datos obtenidos. No es necesario definir un cursor como en otros motores de bases de datos. Sintaxis FOR target IN query LOOP sentencias END LOOP ; target: es una variable de tipo RECORD (normalmente) que recibirá los datos de cada una de las filas del resultado de la consulta. query: cualquier consulta que devuelva filas, normalmente SELECT. Ejemplos Para un intervalo dado, actualizar el campo redundante orderlines.orderdateutilizando una función con recorrido línea por línea de la tabla orders. Desarrollo: La estrategia será recorrer cada línea de la tabla orders y por cada una, tomar el valor de orderdate y actualizar con ese valor, todas las filas de orderlines correspondiente a la order activa. create or replace function upd_orderdate(pd_desde date,pd_hasta date) returns int as $$ declare lr_orden record; li_ordenes int:=0; begin for lr_orden in select orderid, orderdate from orders where orderdate between pd_desde and pd_hasta loop update orderlines set orderdate = lr_orden.orderdate where orderid = lr_orden.orderid; li_ordenes := li_ordenes + 1; end loop; return li_ordenes; end; $$ LANGUAGE 'plpgsql'; Ejercicios prácticos 2: Crear una función que muestre por cada línea de detalle de orden, el nombre del cliente, la fecha, el título del producto, cantidad, importe unitario y subtotal de cada producto para un intervalo de tiempo dado por parámetros. Programar una función que reciba como parámetro un orderid y devuelva una cadena de caracteres (resúmen) con el id, nombre, precio unitario y cantidad de todos los productos incluidos en la orden en cuestión. Por ejemplo para la orden 4 La función deberá devolver: 3753-AFFAIR RUSH-16.99-3, 5126-AGENT CASUALTIES-18.99-3, 5317- AGENT FIREBALL-16.99-1, 4377-AFRICAN GREASE-18.99-3, 9309-ALADDIN FEUD-26.99-3 Listar órdenes de un intervalo de fechas dado por parámetro agregando una columna adicional que sea la cadena de detalles de la función anterior.
Compartir