Crear procedimientos almacenados en Postgresql

A continuación quiero guiarles paso a paso para desarrollar una aplicación empresarial del mundo real usando tecnología libre o de fácil acceso la cual puede ser usada como base para el desarrollo de una aplicación.

En esta entrada vamos a crear la capa de modelo de datos la cual la vamos a hacer con postgressql para crear nuestra base de datos y los procedimientos almacenados.

Estas son las herramientas o entorno de trabajo sobre la cual he desarrollado este manual:

  1. Postgresql 9.0
  2. PgAdmin 1.4
  3. Sistema Operativo Debian 7 (Puedes usar el SO de tu preferencia)

Para comenzar necesitamos tener nuestro modelo de negocio, vamos a crear una nueva base de datos en el PgAdmin la cual voy a nombrar simple_bd, luego vamos a crea las tablas users y authorities las cuales serán base para nuestra aplicación..

Tabla Users

CREATE TABLE users
(
  id bigserial NOT NULL,
  first_name character varying,
  last_name character varying,
  email character varying NOT NULL,
  phone character varying(20),
  mobile character varying(20),
  identification_number integer,
  identification_type character varying(1),
  gender character varying(1),
  birthday date,
  nickname character varying,
  password character varying,
  country_code character varying(3),
  twitter character varying,
  created_at timestamp without time zone,
  accept_terms boolean,
  is_verify boolean,
  is_active boolean NOT NULL DEFAULT true,
  enabled boolean NOT NULL DEFAULT true,
  last_login timestamp without time zone,
  attempts integer NOT NULL DEFAULT 0,
  avatar character varying,
  facebook_id character varying,
  currency_id bigint NOT NULL DEFAULT 1,
  CONSTRAINT users_pkey PRIMARY KEY (id),
  CONSTRAINT users_email_key UNIQUE (email)
)
WITH (
  OIDS=FALSE
);

No voy a explicar cada columna, solo voy a mencionar que la tabla  nos va a servir para guardar los nuevos usuarios, iniciar sesión, modificar su perfil entre otras operaciones que se pueden hacer con el usuario.

Tabla autorithies

CREATE TABLE authorities
(
  username character varying(50) NOT NULL,
  authority character varying(50) NOT NULL,
  id bigserial NOT NULL,
  user_id bigint,
  CONSTRAINT authorities_pkey PRIMARY KEY (id )
)
WITH (
  OIDS=FALSE
);

La tabla authorities la vamos a usar para guardar el permiso del usuario, esta nos va a ser útil más adelante en la próxima entrada cuando integremos la capa de negocio con spring jdbc.

Crear procedimientos almacenados

Ahora vamos a crear los procedimientos almacenados, en los cuales crearemos el CRUD para Crear, Leer, Actualizar y Eliminar datos de nuestras tablas.

proc_create_users

CREATE OR REPLACE FUNCTION proc_create_users(
	IN p_first_name character varying, 
	IN p_last_name character varying, 
	IN p_email character varying, 
	IN p_phone character varying, 
	IN p_mobile character varying, 
	IN p_identification_number integer, 
	IN p_identification_type character varying, 
	IN p_gender character varying, 
	IN p_birthday date, 
	IN p_nickname character varying, 
	IN p_password character varying, 
	IN p_country_code character varying, 
	IN p_twitter character varying, 
	IN p_accept_terms boolean, 
	IN p_is_verify boolean, 
	IN p_is_active boolean, 
	IN p_enabled boolean, 
	IN p_last_login timestamp without time zone, 
	IN p_attempts integer, 
	IN p_avatar character varying, 
	IN p_facebook_id character varying, 
	IN p_currency_id bigint, 
	IN p_username character varying, 
	IN p_ipaddress character varying, 
	IN p_useragent character varying, 
	OUT v_message character varying, 
	OUT v_end_code character varying, 
	OUT v_users_id bigint)
  RETURNS record AS
$BODY$
    DECLARE
      v_cant int DEFAULT 0;
      v_userid int;
    BEGIN
     v_end_code = '99999';
          
     -- VALIDO QUE NO EXISTA EL EMAIL QUE ES UN CAMPO UNICO Y ENVIO UN MENSAJE DE ERROR PERSONALIZADO
     SELECT COUNT(1) INTO v_cant FROM users WHERE  email = p_email;
     IF V_CANT > 0 THEN
        RAISE NOTICE 'Valor unico en Users ya existe ( % )', p_email ;
	v_message := 'La dirección de correo electrónico "'|| p_email ||'" ya esta siendo utilizada' ;
        v_end_code := '10001';
        RETURN;
     END IF;

     -- FIXME: AQUI PUEDO HACER OTRAS VALIDACIONES NECESARIAS ANTES DE GUARDAR EL USUARIO

     -- GUARDO LOS DATOS DEL USUARIO     
     INSERT INTO users (first_name , last_name , email , phone , mobile , identification_number , identification_type , gender , 
            birthday , nickname , password , country_code , twitter , accept_terms , is_verify , 
            is_active , enabled , last_login , attempts, avatar , facebook_id  , created_at, currency_id) 
          VALUES (p_first_name, p_last_name, lower(p_email), p_phone, p_mobile, p_identification_number, p_identification_type, p_gender,
            p_birthday, p_nickname, p_password, p_country_code, p_twitter, p_accept_terms, p_is_verify, 
            p_is_active, p_enabled, p_last_login, p_attempts, p_avatar, p_facebook_id, CURRENT_TIMESTAMP, p_currency_id);

     v_message := 'Usuario dado de alta con exito, debe verificar su dirección de correo electrónico';

     -- BUSCO EL NUEVO ID DE USUARIO PARA ENVIARLO COMO PARTE DE LA RESPUESTA
     SELECT currval('users_id_seq') INTO v_users_id;

     RAISE NOTICE 'SE GUARDO CON EXITO ID %', v_users_id;

     -- CREAMOS EN LA TABLA AUTHORITIES EL NUEVO USUARIO PARA QUE TENGA EL PERMISO DE USUARIO
     INSERT INTO authorities(username, authority, user_id) VALUES (lower(p_email), 'ROLE_USER', v_users_id);
     
     v_end_code := '00000';
     RETURN;
     
    END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Ahora probamos que nuestro procedimiento almacenado funcione bien usando PgAdmin:

select * FROM proc_create_users('JOSE',	'ESTEVEZ',  '[email protected]', '+584125552211', 
	'+584125552211', 12345678, 'E', 'M', '2015-01-01', 'Yeyo', '*****', 'VE', 
	'@predimania', true, false, true, true, NULL, 0, '', '', 1,'', '', '');

Un ejemplo de la salida:

Captura de pantalla de 2015-11-02 09:59:19

 

Este ha sido un ejemplo sencillo de crear un procedimiento almacenado, ahora veamos uno mas complejo con parametros de Salida.

 

proc_find_users_id

CREATE OR REPLACE FUNCTION proc_find_users_id(IN p_id bigint, IN p_username character varying, IN p_ipaddress character varying, IN p_useragent character varying, OUT v_message character varying, OUT v_end_code character varying, OUT ref refcursor)
  RETURNS record AS
$BODY$
    DECLARE
    BEGIN
      v_end_code = '99999';
      OPEN ref FOR 
	SELECT id, first_name, last_name, email, phone, mobile, identification_number, 
		identification_type, gender, birthday, nickname, password, btc_address, 
		country_code, twitter, created_at, accept_terms, is_verify, is_active, 
		enabled, last_login, attempts, leagues_ids, avatar, facebook_id, 
                currency_id
		FROM users where id = p_id;

      v_end_code = '00000';
      RETURN;

    END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Ahora vamos a probar nuestro procedimiento almacenado, para ello vamos al pgadmin y ejecutamos los siguiente pero vamo a hacerlo paso a paso:

-- Iniciamos una transacción
BEGIN;
-- Ejecutamos nuestro procedimiento almacenado
select * from proc_find_users_id(1, '', '', '');
-- El resultado nos retorna el nombre del cursor actual en mi caso unnamed portal 1 que vamos a usar para ver el resultado
FETCH ALL IN "<unnamed portal 1>";
-- Al finalizar hacemos commit
COMMIT;

 

Enhorabuena! ya sabemos hacer procedimientos almacenados y ejecutarlos desde PgAdmin.

Estén atentos a mis próximas entradas donde voy a dar mas ejemplos!

 

 

 

Comments are closed.