Procedimientos almacenados en PostgreSql

PostgreSql

En PostgreSql a diferencia de otros motores de bases de datos, no existe una distinción explicita entre una función y un procedimiento almacenado. En PostgreSql solo existen funciones, claro que estas pueden ser usadas a modo de una función o de un procedimiento almacenado. Además de esta diferencia con otros motores de bases de datos, es importante mencionar que PostgreSql nos ofrece más de un lenguaje para crear nuestros procedimientos almacenados, pudiendo elegir entre los siguientes lenguajes:

En lo personal siempre me he inclinado por Pl/PgSQL pues es un lenguaje procedural basado en SQL y que sigue el estándar ANSI SQL. Si deseas conocer más de Pl/PgSQL aquí puedes encontrar la documentación oficial.

En este artículo veremos cómo se crean funciones en PostgreSql que nos devuelven un recordset y para ello usaremos la misma lógica del procedimiento almacenado creado en Mysql para el artículo “Procedimientos almacenados de Mysql desde Excel Con VBA y ADO“. Y de igual forma al procedimiento en mysql, crearemos una función en PostgreSql que nos devuelva un recordset conteniendo una lista de países, esto claro, dependiendo del valor pasado a través de un parámetro de entrada.

 

Para crear la función, ejecutaremos pgAdmin III

 

Y una vez autenticados en nuestro servidor y seleccionada la base de datos en que trabajaremos ejecutaremos el editor de consultas SQL que se encuentra en la barra de herramientas:

 

Y se nos mostrará el editor de consultas, en el que podremos ejecutar tanto código PLSQL como SQL.

 

Una vez abierto nuestro editor el primer paso será crear la tabla lista_paises y para ello deberemos ejecutar el siguiente bloque SQL, modificando antes el userpostgre por el usuario que usas en tu servidor.

CREATE TABLE lista_paises
(
  id integer DEFAULT 0,
  opcion character varying(100) DEFAULT ''::character varying
)
WITH (
  OIDS=FALSE
);
ALTER TABLE lista_paises
  OWNER TO userpostgre;

Ya que tenemos la tabla comenzaremos a crear nuestra función. Para ello primero definimos el encabezado, y aquí es importante mencionar que en este caso, donde vamos a devolver un recordset, deberemos declarar tantos parámetros de salida como columnas tenga nuestra consulta:

CREATE OR REPLACE FUNCTION SP_DamePaises
   ( IN PV_OPCION VARCHAR(10) default 'DAMETODOS',
     OUT columna1 int, 
     OUT columna2 varchar(100)
   ) RETURNS setof record AS

Dado que la función devolverá un recordset conteniendo las columnas id y opción, hemos agregado ambos parámetros de salida a la función y hemos agregado el parámetro de entrada Pv_Opcion que usaremos a manera de menú de selección para decirle a la función que bloque deberá ejecutar. Al final del encabezado establecemos el tipo de dato que devolverá la función que en este caso será un recordset o setof  record.

Declarado el encabezado vamos a continuar con el cuerpo de la función que contendrá las operaciones a realizar:

$BODY$
begin
 IF PV_OPCION = 'DAMETODOS' THEN
   return query select id, opcion from lista_paises;
 END IF;  
 IF PV_OPCION = 'DAMECINCO' THEN
   return query select id, opcion from lista_paises limit 5;
 END IF;  
return;
end;
$BODY$
LANGUAGE 'plpgsql';

Como podemos ver, el código es muy parecido al usado en Mysql, con las variantes de que aquí debemos establecer con return query el recordset a devolver, además de tener que indicar el lenguaje a usar y el inicio y fin del cuerpo de la función.

Para  ejecutar el procedimiento almacenado ejecutaremos la sentencia que nos devolverá el listado:

Select * From SP_DamePaises('DAMETODOS');

Una vez que lo hayamos ejecutado obtendremos el siguiente resultado:

 

Y para terminar aquí está  el bloque completo de la función:

CREATE OR REPLACE FUNCTION SP_DamePaises
   ( IN PV_OPCION VARCHAR(10) default 'DAMETODOS',
     OUT columna1 int, 
     OUT columna2 varchar(100)
   ) RETURNS setof record AS
$BODY$
begin
 IF PV_OPCION = 'DAMETODOS' THEN
   return query select id, opcion from lista_paises;
 END IF;  
 IF PV_OPCION = 'DAMECINCO' THEN
   return query select id, opcion from lista_paises limit 5;
 END IF;  
return;
end;
$BODY$
LANGUAGE 'plpgsql';

 

Y el video que muestra cómo crear el procedimiento en postgresql:

 

[embedplusvideo height="439" width="732" standard="http://www.youtube.com/v/sCSluxOW-Wc?fs=1&hd=1"  /]

 

Todo comentario o sugerencia será muy apreciado.

Posted in Bases de datos, PostgreSql. Tagged with , , , , , , .

11 Responses

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>