Procedimientos almacenados y PL/pgSQL

programacion

En este artículo vamos a dar una introducción a los llamados procedimientos almacenados (stored procedures) en PostgreSQL. Un procedimiento almacenado se puede definir como un programa, procedimiento ó función, el cual está almacenado en la base de datos y listo para ser usado.

Este artículo es una introducción a este tema, la documentación completa con todos los detalles e información necesaria está disponible en la documentación oficial de PostgreSQL, "Capítulo 37. Procedural Languages"

Existen dos ventajas evidentes al utilizar procedimientos almacenados en nuestro sistema:

  • La ejecución del procedimiento ocurre en el servidor de bases de datos. Esto probablemente aumentará el rendimiento de nuestra aplicación al no tenerse que mandar datos entre el cliente y el servidor, y no tener que procesar resultados intermedios en el cliente para obtener el resultado final.
     
  • Al tener la lógica de la aplicación implementada en la base de datos no tendremos que implentarla en los clientes, con el consiguiente ahorro de lineas de código redundante y complejidad.

    Si tenemos diferentes tipos de clientes implementados en diferentes sistemas ó lenguajes de programación y accediendo a la misma base de datos, no tendremos que programar la misma lógica en todos, al estar esta disponible en la base de datos. Tendremos una API a la lógica de la aplicación lista para usarse desde diferentes clientes

Un procedimiento almacenado en PostgreSQL se puede escribir en multiples lenguajes de programación. En una instalación por defecto de PostgreSQL podremos tener disponibles los siguientes lenguajes: PL/pgSQL, PL/Perl, PL/Tcl y PL/Python.

El único lenguaje que está disponible automáticamente es PL/pgSQL. Para utilizar PL/Perl, PL/Tcl o PL/Python tendremos que haber configurado/compilado PostgreSQL con estos parámetros --with-perl --with-tcl --with-python.

Tambien existen muchos otros lenguajes disponibles como módulos adicionales, entre ellos, PL/Java, PL/PHP, PL/R, PL/Ruby, PL/Sheme y PL/sh, pero estos tienen que descargarse e instalarse por separado [Información sobre descargas].

Suponiendo que tenemos PostgreSQL instalado con los lenguajes que queremos utilizar tendremos que realizar dos operaciones para poder empezar a utilizar un procedimiento almacenado en nuestra base de datos:

  1. Instalar, si no lo tenemos instalado, el lenguaje que vayamos a utilizar para programar nuestro procedimiento (solamente necesitamos hacer esto una sola vez por base de datos)
  2. Programar nuestro procedimiento e instalarlo en la base de datos

PL/pgSQL

En este artículo vamos a utilizar el lenguaje de procedimientos PL/pgSQL por ser el que tendremos seguro disponible. PL/pgSQL es muy parecido al lenguaje PL/SQL utilizado por Oracle y bajo mi punto de vista uno de los mejores lenguajes de procedimientos que podemos usar en PostgreSQL, es fácil de aprender, potente y siempre está disponible.

Los objetivos de PL/pgSQL cuando se creo 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 tendremos un bloque principal en nuestro procedimiento almacenado y dentro de este podremos tener subbloques. Un bloque se define de la siguiente manera (Todo entre los corchetes [] es opcional):

[ << etiqueta >> ]
[ DECLARE
    declaraciones de variables ]
BEGIN
    codigo
END [ etiqueta ];

Podemos definir e instalar un procedimiento en PL/pgSQL de la siguiente manera:

CREATE [ OR REPLACE ] FUNCTION 
nombre_funcion([ [ argmodo ] [ argnombre ] argtipo [, ...] ]) 
RETURNS tipo AS $$

[ DECLARE ]
 [ declaraciones de variables ]

BEGIN
  codigo  

END;
$$ LANGUAGE plpgsql
   | IMMUTABLE | STABLE | VOLATILE
   | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
   | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
   | COST execution_cost
   | ROWS result_rows
   | SET configuration_parameter { TO value | = value | FROM CURRENT }
;

No os asusteis al ver esto, es más fácil de lo que parece y todas las opciones despues de "LANGUAGE plpgsql" tienen unos valores por defecto que simplifican mucho la definición de un procedimiento. La documentación completa está disponible en la sección CREATE FUNCTION de la documentación oficial.

A continuación vamos a ver algunas de las opciones y valores más importantes.

argmodo: El modo de un argumento puede ser IN, OUT, or INOUT. Por defecto se usa IN si no se define.

argtipo: Los tipos que podemos utilizar son todos los disponibles en PostgreSQL y todos los definidos por el usuario

declaraciones de variables: Las declaraciones de variables se pueden realizar de la siguiente manera ($n = orden de declaración del argumento.):

nombre_variable ALIAS FOR $n;
nombre_variable [ CONSTANT ] tipo [ NOT NULL ] [ { DEFAULT | := } expresion ];

código: en este artículo no tenemos espacio para ver como podemos escribir la parte de código de un procedimiento. Teneis que leer la sección de la documentación oficial de PostgreSQL que trata sobre el tema, "Capítulo 38. PL/pgSQL - SQL Procedural Language", especialmente las secciones 38.3. Declarations, 38.5. Basic Statements y 38.6. Control Structures.

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 siempre devolverá el mismo resultado en una consulta individual de una tabla, dados los mismos valores como argumentos. El resultado podria 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)

CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT:

CALLED ON NULL INPUT: Indica que la función se ejecutará aunque algunos de los argumentos sean NULL. El usuario tiene la responsabilidad de comprobar si algún argumento es NULL cuando sea necesario tener esto en cuenta.(valor por defecto)

RETURNS NULL ON NULL INPUT / STRICT: Indican que la función no se ejecutará y devolverá el valor NULL si alguno de los argumentos es NULL.

SECURITY INVOKER | SECURITY DEFINER:

SECURITY INVOKER: Indica que la función se ejecutará con los privilegios del usuario que la ejecuta (valor por defecto)

SECURITY DEFINER: Indica que la función se ejecutará con los privilegios del usuario que la creo.

El resto de opciones son avanzadas y podeis leer sobre ellas en la documentación oficial.

Aplicando la poca teoria que hemos visto, vamos a ver unos cuantos ejemplos que nos aclaren un poco como definir, instalar y usar un procedimiento almacenado en PL/pgSQL (estos ejemplos han sido comprobados en postgreSQL 8.3.7).

Creamos una base de datos para utilizarla con nuestros ejemplos:

postgres@server:~$ psql
Welcome to psql 8.3.7, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

postgres=# CREATE DATABASE test001;
CREATE DATABASE

postgres=# \c test001
You are now connected to database "test001".

test001=# 

Lo primero que tenemos que hacer es instalar el lenguaje plpgsql si no lo tenemos instalado.

CREATE PROCEDURAL LANGUAGE plpgsql;

Si queremos que cualquier usuario con acceso a la base de datos pueda usarlo sin tener que ser el administrador postgres, tendremos que utilizar TRUSTED con el comando anterior.

CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql;

A continuación creamos nuestro primer procedimiento. (Podemos copiar y pegar en el cliente psql, escribirlo a mano ó usar el editor interno en psql (\e)):

CREATE OR REPLACE FUNCTION ejemplo() RETURNS integer AS $$
BEGIN
 RETURN 104;
END;
$$ LANGUAGE plpgsql;

Este procedimiento se puede usar de la siguiente manera:

test001=# SELECT ejemplo();

 ejemplo1 
----------
      104
(1 row)

Ahora definimos la función con un argumento:

CREATE OR REPLACE FUNCTION ejemplo(integer) RETURNS integer AS $$
BEGIN
 RETURN $1;
END;
$$ LANGUAGE plpgsql;

Este procedimiento se podria haber escrito tambien de las siguientes maneras:

CREATE OR REPLACE FUNCTION ejemplo(numero integer) RETURNS integer AS $$
BEGIN
 RETURN numero;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION ejemplo(integer) RETURNS integer AS $$
DECLARE
 numero ALIAS FOR $1;

BEGIN
 RETURN numero;
END;
$$ LANGUAGE plpgsql;

Este procedimiento se puede usar de la siguiente manera:

test001=# SELECT ejemplo(104);
 ejemplo 
---------
     104
(1 row)

Vamos a empezar a complicar un poco las cosas usando dos argumentos y definiendo algunas variables:

CREATE OR REPLACE FUNCTION ejemplo(integer, integer) RETURNS integer AS $$
DECLARE
 numero1 ALIAS FOR $1;
 numero2 ALIAS FOR $2;

 constante CONSTANT integer := 100;
 resultado integer;

BEGIN
 resultado := (numero1 * numero2) + constante;

 RETURN resultado;
END;
$$ LANGUAGE plpgsql;

Este procedimiento se puede usar de la siguiente manera:

test001=# SELECT ejemplo(2,2);
 ejemplo 
---------
     104
(1 row)

A continuacion vamos a usar una sentencia IF ... THEN en nuestra función:

CREATE OR REPLACE FUNCTION ejemplo_txt(integer, integer) RETURNS text AS $$
DECLARE
 numero1 ALIAS FOR $1;
 numero2 ALIAS FOR $2;

 constante CONSTANT integer := 100;
 resultado INTEGER;

 resultado_txt TEXT DEFAULT 'El resultado es 104'; 

BEGIN
 resultado := (numero1 * numero2) + constante;

 IF resultado <> 104 THEN
    resultado_txt :=  'El resultado NO es 104';
 END IF;

 RETURN resultado_txt;
END;
$$ LANGUAGE plpgsql;

Este procedimiento se puede usar de la siguiente manera:

test001=# SELECT ejemplo_txt(2,2);
     ejemplo_txt     
---------------------
 El resultado es 104
(1 row)


test001=# SELECT ejemplo_txt(2,3);
      ejemplo_txt       
------------------------
 El resultado NO es 104
(1 row)

Podriamos seguir modificando y complicando nuestro ejemplo, pero como introducción es suficiente para que os hagais una idea de como funciona.

Solo queda decir que en la definición de un procedimiento no solo se tiene en cuenta el nombre del mismo para diferenciarlo de otros, los argumentos de la función tambien se tienen en cuenta. ejemplo(), ejemplo(integer), ejemplo(integer, integer) y ejemplo(text) son todos procedimientos diferentes, aunque se llamen igual.

En psql existe un comando muy bueno que nos enseña como una función está definida en la base de datos.

test001=# \x
Expanded display is on.

test001=# \df+ ejemplo
List of functions
-[ RECORD 1 ]-------+----------------------
Schema              | public
Name                | ejemplo
Result data type    | integer
Argument data types | 
Volatility          | volatile
Owner               | postgres
Language            | plpgsql
Source code         | 
                    : BEGIN
                    :  RETURN 104;
                    : END;
                    : 
Description         | 
-[ RECORD 2 ]-------+----------------------
Schema              | public
Name                | ejemplo
Result data type    | integer
Argument data types | integer
Volatility          | volatile
Owner               | postgres
Language            | plpgsql
Source code         | 
                    : DECLARE
                    :  numero ALIAS FOR $1;
                    : 
                    : BEGIN
                    :  RETURN numero;
                    : END;
                    : 
Description         | 
-[ RECORD 3 ]-------+-----------------------------------------------
Schema              | public
Name                | ejemplo
Result data type    | integer
Argument data types | integer, integer
Volatility          | volatile
Owner               | postgres
Language            | plpgsql
Source code         | 
                    : DECLARE
                    :  numero1 ALIAS FOR $1;
                    :  numero2 ALIAS FOR $2;
                    : 
                    :  constante CONSTANT integer := 100;
                    :  resultado integer;
                    : 
                    : BEGIN
                    :  resultado := (numero1 * numero2) + constante;
                    : 
                    :  RETURN resultado;
                    : END;
                    : 
Description         | 

test001=# \df+ ejemplo_txt
List of functions

-[ RECORD 1 ]-------+----------------------------------------------------
Schema              | public
Name                | ejemplo_txt
Result data type    | text
Argument data types | integer, integer
Volatility          | volatile
Owner               | postgres
Language            | plpgsql
Source code         | 
                    : DECLARE
                    :  numero1 ALIAS FOR $1;
                    :  numero2 ALIAS FOR $2;
                    : 
                    :  constante CONSTANT integer := 100;
                    :  resultado INTEGER;
                    : 
                    :  resultado_txt TEXT DEFAULT 'El resultado es 104'; 
                    : 
                    : BEGIN
                    :  resultado := (numero1 * numero2) + constante;
                    : 
                    :  IF resultado <> 104 THEN
                    :     resultado_txt :=  'El resultado NO es 104';
                    :  END IF;
                    : 
                    :  RETURN resultado_txt;
                    : END;
                    : 
Description         | 

El resto es solo cuestión de imaginación y de leer la documentación detalladamente. Las posibilidades son infinitas y os aseguro que una vez que empeceis a usar procedimientos almacenados no podreis dejar de usarlos.

AUTOR: Rafael Martinez / PostgreSQL-es.org

Comentarios

Opciones de visualización de comentarios

Seleccione la forma que prefiera para mostrar los comentarios y haga clic en «Guardar las opciones» para activar los cambios.

Preguntas sobre procedimientos

Estoy migrando de SQLServer a Postgre y tengo algunas preguntas.

En plpgsql
¿Cuál es la sintaxis para crear procedimientos que se muestren en pgAdminIII en el nodo de procedures?

Nota, según este artículo, se crean funciones y no procedimientos, pregunto, ¿esto es como lenguaje C que los procedimientos son funciones que no regresan valor?, ¿alguíen me puede ayudar con esta confusión?

edgardigital@yahoo.com.mx

Imagen de rafaelma

Re: Preguntas sobre procedimientos

En PostgreSQL un procedimiento ó función es lo mismo.

Si los defines como se explica en este artículo, deberian de aparecer en pgAdminIII si tienes permisos para ello.

Estos pueden devolver ó no un valor, el programador tiene que definir si su procedimiento/función va a devolver datos, y que tipo de datos.

--
Rafael Martinez
Webmaster

Gracias Rafael Martínez. Me

Gracias Rafael Martínez.

Me ahora me queda claro y empesaré a migrar mis procedimientos de SQL Server a Funciones de Postgre.

Sí tienes razón, aparecen en el nodo Functions, sólo que yo esperaba verlos en el nodo procedures, consecuencia de mi confusión.

Al margen te comento lo siguiente:

Mira alguién me mandó este código, al ejecutarlo me creó un procedimiento en procedures, sólo que al verlo en PgAdmin III el lenguaje es:edbspl y por lo que he leido creo que es mejor seguir por plpgsql.

CREATE OR REPLACE PROCEDURE sp_Alt_AgnCmp (
_AgnCmpClv char(3),
_gnCmpNmb varchar(50),
_FchAlt datetime,
_Mmo text,
_Fto varchar(255),
_Mdf smallint,
_AplClv char(2))
AS
BEGIN
INSERT INTO AgnCmp
(
AgnCmpClv,
AgnCmpNmb,
FchAlt,
Mmo,
Fto,
Mdf,
AplClv
)
VALUES
(
_AgnCmpClv,
_AgnCmpNmb,
_FchAlt,
_Mmo,
_Fto,
_Mdf,
_AplClv
);
END ;

ayuda con informacion en español

quisiera k me ayudes con informacion en espanol

postea algunas paginas web con inf en espanol graxias...

Re: Preguntas sobre procedimientos

Bueno mira, la figura de PROCEDURE no existe en postgre (version libre) como si lo existe en ORACLE o SQL SERVER. En su defecto PostgreSQL tiene FUNCTION, estas funciones como bien se describe alla arriba, pueden o no devolver un valor, eso dependera de lo que vos necesites hacer, bien pueden no devolver nada, simplemente ejecutar una accion, o pueden devolverte hasta una tabla como resultado de algun proceso, eso vos lo definis.

Por otro lado, comentarte que no es como lenguaje C, en realidad Pl/PGsql guarda mucho los standars de SQL, significa que si vos queres trasladar un procedimiento de SQL server a Postgre o de Oracle a postgre... los cambios seran minimos. Sobre todo de oracle se hace mas facil porque el PL tiene mucha pero mucha similitud, en cuanto a transact-sql pues un poquitin la sintaxis pero en verdad son similares.

saludos cordiales desde El Salvador.

Muchas gracias para el colega

Muchas gracias para el colega de El Salvador.

Ya estoy migrando mis Procedimientos de SQL Server a Funciones de Postgre, ahora ya no hay confusión.

Saludos

Edgar

xmx

Tengo una duda que aun no e podido resolver por mas que navego por la web

Mi duda es, para que el pgadminIII tiene el nodo o menu PROCEDURES si es que realmente no se utilizan los procedimientos?

Espero me puedan ayudar

=)

Gracias

Asunto sobre triggers

¿Dentro de un trigger se puede declarar y usar una función ? y si es así ¿como seria su sintaxis?.
Gracias.

Otra sobre Procedimientos Almacenados

Hola, estoy recién empezando con esto del PostgeSQL. He tabajado con otros motores de BD y un procedimiento como:

CREATE PROCEDURE nom_procedure
AS
BEGIN
...muchas instrucciones (con inserts, updates, etc)...

SELECT campo1, campo2, valor1
FROM tabla
WHERE condicion
END

que puedo utilizar luego en un lenguaje cliente para listar un resultado de muchas filas.

¿Como puedo hacerlo con funciones de PL/pgSQL?
He buscado y sólo encuentro como devolver 1 registro. No he encontrado como devolver el resultado de un select.

Gracias..

Imagen de rafaelma

Devolver multiples lineas en PL/pgSQL

Para lo que quieres hacer, las palabras clave por las que tienes que buscar información son: SETOF, ROWTYPE y RECORD, RETURN NEXT y RETURN QUERY

Te paso un pequeño ejemplo con rowtype:

CREATE TABLE test001 (id integer,value text);

INSERT INTO test001 VALUES (1,'a');
INSERT INTO test001 VALUES (1,'b');
INSERT INTO test001 VALUES (1,'c');

SELECT * from test001 ;

 id | value 
----+-------
  1 | a
  1 | b
  1 | c
(3 rows)

CREATE OR REPLACE FUNCTION show_data() RETURNS SETOF test001 AS 
$$
DECLARE
 sql_result test001%rowtype;
BEGIN
 FOR sql_result in EXECUTE 'SELECT * from test001' LOOP
   RETURN NEXT sql_result;
 END LOOP;
END;
$$ LANGUAGE plpgsql;


SELECT * from show_data();

 id | value 
----+-------
  1 | a
  1 | b
  1 | c
(3 rows)

Y otro con record:

CREATE OR REPLACE FUNCTION show_data() RETURNS SETOF record AS 
$$
DECLARE
 sql_result record;
BEGIN
 FOR sql_result in EXECUTE 'SELECT * from test001' LOOP
   RETURN NEXT sql_result;
 END LOOP;
END;
$$ LANGUAGE plpgsql;

SELECT * from show_data() as test001(a integer,b text);
 a | b 
---+---
 1 | a
 1 | b
 1 | c
(3 rows)

--
Rafael Martinez
Webmaster