Procedimientos y funciones en PostgreSQL

Los procedimientos o funciones son esctructuras que aglutinan una serie de instruciones con el fin de evitar tener que repetir código, esto produce una mejora en la legibilidad y eficiencia del código, así como un mejor control de errores.

En el SGBD PostgreSQL un procedimiento no devuelve ningún resultado, encambi una función si devuelve un resultado.

Buenas prácticas:

  • Documentar el código generado, incluyendo detalles como autor del procedimiento, fecha de creación y versión actual, así como un registro de los cambios que dicho procedimiento ha sufrido a lo largo de su vida.
  • Asegurarse de que los tipos de datos están correctamente definidos, evita problemas de complilación o transformación de tipos que baja el rendimiento de la base de datos.
  • Utilizar las llamadas a procedimientos desde las aplicaciones, siempre que sea posible que actuan como caja negra frente a la base de datos y protege a las aplicaciones de posibles cambios de estructuras lógicas de las consultas.
  • Evitar la generación de código dinámico, evita errores a largo plazo si las estructuras de datos cambian, asi como evitar problemas de seguridad (denominados Inyección SQL)
  • Evitar llamadas a funciones de manera innecesaria, ejemplo llamar a la función now():date para pedir la hora cada vez que seestime en vez de almacenarla en una variable temporal en la que solo se la llame una vez.

Ejemplo de sintaxis, funcion que inserta nuevos clientes menores de edad en una tabla poder hacerlos promociones.

---------------------------------------------------------------------------
--
-- Crear la tabla donde se reflejaran los nuevos cliente menores de edad
--
---------------------------------------------------------------------------

CREATE TABLE ventas.tb_promocion_joven
(
  promocion_joven_id SERIAL PRIMARY KEY,
  client_name char(40) NOT NULL,
  age integer,
  address char(120),
  city char(25),
  country char(50) NOT NULL,
  contact_email char(100),
  phone char(15),
  created_date date,
  create_by_user char(50)
);
  
----------------------------------------------------------------------------
--
-- Crear una funcion que separe a los clientes jovenes en tb_promocion joven
--
----------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION ventas.promocion_joven() 
RETURNS trigger AS
$$
/* 
 *  Procedimiento: promocion_joven
 *  Autor: Diego Calvo Barreno
 *  Fecha creación: 2017-05-29
 *  Versión: 1.0
 *  Parámetros:  sin parámetros
 *  Descripción: Procedimiento que cargará en la tabla tb_promocion_joven
 *  los clientes que al darse de alta tienen menos de 18 años
 */
DECLARE
  -- Define la estructura temporal que servirá para realizar las operaciones
  temp_row ventas.tb_promocion_joven%rowtype;
  
BEGIN
  
  IF (NEW.age < 18) THEN

      -- Capturar datos del nuevo cliente creado
      temp_row.promocion_joven_id := NEW.client_id;
      temp_row.client_name := NEW.client_name;
      temp_row.age := NEW.age;
      temp_row.address := NEW.address;
      temp_row.city := NEW.city;
      temp_row.country := NEW.country;
      temp_row.contact_email := NEW.contact_email;
      temp_row.phone := NEW.phone;

      -- Capturar el usuario y el momento de la operación
      temp_row.create_by_user := user;
      temp_row.created_date := now();

      BEGIN
        -- Inserta los valores en la tabla CDC
      INSERT INTO ventas.tb_promocion_joven SELECT temp_row.*;
      END;

      -- Devuelve el la estructura grabada NEW
      RETURN NEW;
   END IF;
   
   RETURN NULL;
   
END; -- Fin del procedimiento  
$$
LANGUAGE plpgsql;

----------------------------------------------------------------------------
--
-- Crear el disparador que activa la funcion
--
----------------------------------------------------------------------------

-- Definición del disparador que llama a la función anteriormente descrita
CREATE TRIGGER tg_promocion_joven 
  -- Este disparador se activa con los eventos de inserción, actualización y borrado
  AFTER INSERT ON ventas.tb_client
  FOR EACH ROW
  EXECUTE PROCEDURE ventas.promocion_joven()
;

----------------------------------------------------------------------------
--
-- Inserciones que prueban la funcion
--
----------------------------------------------------------------------------
INSERT INTO ventas.tb_client ( client_id, client_name, age, address, city, country, contact_email, phone, created_date ) VALUES
 ('10000', 'Susana', '15','C/ Real 1', 'Soria', 'España', NULL, NULL, NULL),
 ('10001', 'Rosana', '16','C/ Real 1', 'Soria', 'España', NULL, NULL, NULL),
 ('10002', 'Javier', '35','C/ Real 2', 'Soria', 'España', NULL, NULL, NULL);

Otros artículos que pueden ser de interés:

Autor: Diego Calvo