Funciones

Introducción a funciones SQL

En las funciones Postgresql podemos realizar operaciones de consultas y operaciones de actualizaciones.

Consultas: SELECT

Actualizaciones: INSERT,UPDATE,DELETE

Como buena función puede contener argumentos o parámetros, también podemos definir estos argumentos o parámetros con tipos de una tabla, es decir:

num_empleado hr.employees.employeenumber%type.

num_empleado sería del mismo tipo que el campo employeenumber de la tabla employees del esquema hr.

Para introducir los datos en una variable introducimos el comando INTO.

SELECT * INTO num_empleado FROM employees;

Cuando queremos devolver datos compuestos en una función previamente debemos crear un tipo con todos los campos que queremos devolver. Luego, en la función utilizaremos el tipo como una variable.

Ejemplo de una función en la que devolveremos el departamento, el jefe con su nombre y apellidos, introduciendo el número de identificación como parámetro de la función.

Funciones SQL con datos compuestos

Como vamos a devolver más de un campo vamos a necesitar un tipo.

CREATE TYPE AS capDep_t(
  nomDept varchar(30),
  nomCap varchar(20),
  cognomCap varchar(25));

 

Pasamos a crear la función mostraDept(id).

CREATE OR REPLACE FUNCTION mostraDept(id public.departaments.id_departament%type)

RETURNS capDep_T AS &nomC&

DECLARE
capsDep capDep_T;

BEGIN
IF $1 NOT IN (SELECT d.id_departament FROM public.departaments d)
THEN RAISE EXCEPTION ‘El departamento % no existe’,$1;
ELSE
SELECT d.id_departament,e.nom,e.cognoms
INTO capsDep
FROM public.departaments d, public.empleats e
WHERE d.id_cap = e.id_empleat
AND d.id_departament = $1;
END IF;
RETURN capsDep;

EXCEPTION

WHEN raise_exception THEN RAISE EXCEPTION ‘%:%’,SQLSTATESQLERRM;
END; $nomC$ LANGUAGE plpgsql;

 

Para probar la consulta SELECT * from(’90’);

Funciones SQL que hacen más de un retorno con datos compuestos

Ahora vamos a crear una función que mostrará los empleado que pertenecen a un departamento concreto.

CREATE TYPE currants_T AS (
  nom varchar(30),
  apellidos varchar(35),
  salario numeric);

 

Creamos la función utilizando el comando SETOF para especificar que devolveremos más de un valor del tipo currants_T.

CREATE OR REPLAC FUNCTION mostraTreballadorsDept(id public.departaments.id_departament%type)

RETURNS SETOF currants_T AS $mostraT$

DECLARE
empleats currants_T;

BEGIN
 
IF $1 NOT IN (SELEC d.id_departament FROM public.departaments d)THEN
RAISE EXCEPTION ‘El departamento % no existe’,$1;
ELSE
FOR empleats IN
SELECT e.nom, e.apellidos, e.salario
FROM public.empleats
WHERE e.id_departament = $1 LOOP
RETURN NEXT empleats;
END LOOP;
END IF;

EXCEPTION
WHEN raise_exception THEN RAISE EXCEPTION ‘%:%’,SQLSTATE,SQLERRM;
END; $mostraT$ LANGUAGE plpgsql;