PDF de programación - Programación de funciones en PL/pgSQL para PostgreSQL

Programación de funciones en PL/pgSQL para

PostgreSQL

Roberto Andrade Fonseca. ABL Consultores, S.A. de C.V.

8 de febrero de 2002

1. Objetivos

Presentar a los asistentes las ventajas de contar con funciones que manejen
las ’reglas del negocio’ dentro de una base de datos, PostgreSQL en nuestro
caso, para independizarlas del código en que se programen los clientes.

Describir el lenguaje y mostrar ejemplos reales de funciones desarrolladas
con PL/pgSQL.

2.

Introducción

Es común que los desarrolladores de aplicaciones subutilicen las prestaciones
de las bases de datos relacionales modernas, en ocasiones aimplemente por de-
conocer las ventajas que le ofrecen o por desconocer su manejo.

Dentro de PostgreSQL, la base de datos de código abierto más poderosa,
se pueden desarrollar funciones en varios lenguajes. El lenguaje PL/pgSQL es
uno de los más utilizados dentro de PostgreSQL, debido a que guarda cierta
similitud con PL/SQL de Oracle y a su facilidad de uso.

En este tutorial se mostrará la sintaxis, el control de flujo y otras carac-

terísticas del lenguaje, además de presentarán algunos ejmeplos reales.

2.1. Ventajas de usar PL/pgSQL

SQL es el lenguaje estándar para realizar consultas a un servidor de base de
datos. Cada sentencia SQL se ejecuta de manera individual por el servidor, lo
cual implica que las aplicaciones cliente deben enviar cada consulta al servidor,
esperar a que la procese, recibir los resultados, procesar los datos y después
enviar la siguiente sentencia.

Al usar PL/pgSQL es posible relizar cálculos, manejo de cadenas y consultas
dentro del servidor de la base de datos, combinando el poder de un lenguaje
procedimental y la facilidad de uso de SQL, minimizando el tiempo de conexión
entre el cliente y el servidor.

1

3 NUESTRA BASE DE DATOS DEL TUTORIAL

2

3. Nuestra base de datos del tutorial

Para ejemplificar el uso de las funciones en PL/pgSQL, vamos a utilizar una

base de datos que tiene el siguiente esquema:

-- Esquema de la base de datos del tutorial
-- de PL/pgSQL

varchar,

varchar,

int REFERENCES puesto,
varchar,
varchar,

varchar(40),

int REFERENCES titulo,
varchar NOT NULL,
varchar,

varchar NOT NULL,
char(1) CHECK (sexo in (’M’, ’F’)),

CREATE TABLE asistente (
id_asistente SERIAL,
id_titulo
ap_paterno
ap_materno
nombre
sexo
id_puesto
compania
direccion
colonia
ciudad
codigo_postal
id_estado
id_pais char(2) NOT NULL REFERENCES pais,
lada
telefono1
telefono2
fax varchar(8),
email
url varchar,
id_categoria
id_giro_empresa int REFERENCES giro_empresa,
id_lugar_compra int REFERENCES lugar_compra,
id_sistema_operativo int REFERENCES sistema_operativo,
PRIMARY KEY (id_asistente)

varchar REFERENCES estado,

varchar,

varchar(8),
varchar(8),

varchar,

int NOT NULL REFERENCES categoria DEFAULT 1,

);

4. Estructura de PL/pgSQL

El lenguaje PL/pgSQL es estructura en bloques. Todas las palabras clave y
los identificadores pueden escribirse mezclando letras mayúsculas y minúsculas.

Un bloque se definde de la siguiente manera:

[<<label>>]
[DECLARE

declaraciones]

5 COMENTARIOS, CONSTANTES Y VARIABLES

3

BEGIN

sentencias

END;

Pueden existir varios bloques o sub-bloques en la sección de sentencias de
un bloque. Los sub-bloques pueden ser usados para ocultar las variables a los
bloques más externos.

Normalmente una de las sentencias es el valor de retorno, usando la palabra

clave RETURN.

Las variables declaradas en la sección que antecede a un bloque se inicializan
a su valor por omisión cada vez que se entra al bloque, no solamente al ser
llamada la función. Por ejemplo:

CREATE FUNCTION estafunc() RETURNS INTEGER AS ’
DECLARE

cantidad INTEGER := 30;

BEGIN

RAISE NOTICE ’’Cantidad contiene aquí %’’,cantidad;
-- Cantidad contiene aquí 30
cantidad := 50;
--
-- Creamos un sub-bloque
--
DECLARE

cantidad INTEGER := 80;

BEGIN

RAISE NOTICE ’’Cantidad contiene aquí %’’,cantidad;
-- Cantidad contiene aquí 80

END;
RAISE NOTICE ’’Cantidad contiene aquí %’’,cantidad;
-- Cantidad contiene aquí 50

RETURN cantidad;

END;
’ LANGUAGE ’plpgsql’;

No se debe confundir el uso de las sentencias de agrupamiento BEGIN/END
de PL/pgSQL con los comandos de la base de datos que sirven para el control
de las transacciones. Las funciones y procedimientos disparadores no pueden
iniciar o realizar transacciones y Postgres no soporta transacciones anidadas.

5. Comentarios, constantes y variables

5.1. Comentarios

Existen dos tipo de comentarios en PL/pgSQL. Un doble guión – da inicio
a un comentario, el cual se extiende hasta el final de la línea. Un /* inicia un

5 COMENTARIOS, CONSTANTES Y VARIABLES

4

bloque que se extiende hasta la primera ocurrencia de */.

5.2. Variables y constantes

Todas las variables, filas y registros usados en un bloque o en sus sub-bloques

deben declararse en la sección de declaraciones del bloque.

La excepción es la variable de un ciclo FOR que itera sobre un rango de

valores enteros.

Las variables en PL/pgSQL pueden ser de cualquier tipo de datos de SQL,
como INTEGER, VARCHAR y CHAR. El valor por omisión de todas la vari-
ables es el valor NULL de SQL.

A continuación se muestran algunos ejemplos de declaración de variables:

user_id INTEGER;
quantity NUMBER(5);
url VARCHAR;

5.2.1. Constantes y variables con valores por omisión

La declaraciones tienen las siguiente sintaxis:

nombre [ CONSTANT ] tipo [ NOT NULL ] [ { DEFAULT | := } valor ];

El valor de una variable declarado como CONSTANT no puede ser modificado.
Si acaso se especifica NOT NULL, la asignación de un valor NULL causa en un error
en tiempo de ejecución. Puesto que el valor por omisión de todas las vriables
es el valor NULL de SQL, todas las variables declaradas como NOT NULL deben
contar con un valor por omisión específico.

EL valor se evalúa cada vez que se llama la función, así que asignar now a
una variable de tipo timestamp causa que la variables almacene el tiempo real
de la llamada a la función, no el de la hora en que se compiló en bytecode.

Ejemplos:

cantidad INTEGER := 32;
url varchar := ’’http://misitio.com’’;
user_id CONSTANT INTEGER := 10;

5.2.2. Variables pasadas a las funciones

Las variables que se pasan a las funciones son denominadas con los identifi-

cadores $1, $2, etc. (el máximo es 16).

Algunos ejemplos:

CREATE FUNCTION iva_venta(REAL) RETURNS REAL AS ’
DECLARE

subtotal ALIAS FOR $1;

BEGIN

6 ATRIBUTOS

5

return subtotal * 1.15;

END;
’ LANGUAGE ’plpgsql’;

CREATE FUNCTION instr(VARCHAR,INTEGER) RETURNS INTEGER AS ’

DECLARE

v_string ALIAS FOR $1;
index ALIAS FOR $2;

BEGIN

-- Algunos cáculos irían aquí.

END;
’ LANGUAGE ’plpgsql’;

6. Atributos

Usando los atributos %TYPE and %ROWTYPE, es posible declarar vari-
ables con el mismo tipo de dato o estructura de otro item de la base de datos
(por ejemplo, un campo de una tabla).

%TYPE Proporciona el tipo de dato de una variable o una columna. Se puede
utilizar para declarar variables que almacenen valores de bases de datos.
Por ejemplo, supongamos que usted tiene una columna llamada user id
en la tabla users. Para declarar una variable con el mismo tipo de dato
que el usado en nuestra tabla de usuarios, lo que haría es:

user_id users.user_id\%TYPE;

Al usar %TYPE puede despreocuparse de los cambios futuros en la defini-
ción de la tabla.

nombre tabla %ROWTYPE Declara una renglón con la estructura de la
tabla especificada. tabla puede ser una tabla o una vista que exista en la
base de datos. Los campos del renglón se accesan con la notación punto.
Los parámetros de una función pueden ser de tipo compuesto (renglones
completos de una tabla). Es este caso, el identificador correspondiente $n
será del tipo rowtype, pero debe usarse un seudónimo o alias usando el
comando ALIAS que se describe más adelante.
Solamente los atributos del usuario de la tabla pueden ser accesibles en el
renglón, ni los OID ni otros atributos del sistema (debido a que el renglón
puede ser de una vista). Los campos de un rowtype heredan los tamaños
de los campos o la precisión de los tipos de dato para char(), etc.

DECLARE

users_rec users%ROWTYPE;

7 EXPRESIONES

6

user_id users%TYPE;

BEGIN

user_id := users_rec.user_id;
...

create function cs_refresh_one_mv(integer) returns integer as ’

DECLARE

key ALIAS FOR $1;
table_data cs_materialized_views\%ROWTYPE;

BEGIN

SELECT INTO table_data * FROM cs_materialized_views

WHERE sort_key=key;

IF NOT FOUND THEN

RAISE EXCEPTION ’’View ’’ || key || ’’ not found’’;
RETURN 0;

END IF;
-- La columna mv_name de cs_materialized_views almacena
-- los nombres de las vistas.
TRUNCATE TABLE table_data.mv_name;
INSERT INTO table_data.mv_name || ’’ ’’ || table_data.mv\query;
return 1;

end;
’ LANGUAGE ’plpgsql’;

7. Expresiones

Todas las expresiones usadas en las sentencias de PL/pgSQL son procesadas
usando el ejecutor del backend. Las expresiones que parecen contener constantes
pueden requerir de una evaluación en tiempo de ejecución (por ejemplo, now para
el tipo de dato timestamp) así que es imposible para el analizador sintáctico
(parser) de PL/pgSQL identificar los valores de las constantes reales diferentes
de NULL. Todas las expresiones son evaluadas internamente ejecutando una sen-
tencia

SELECT expresión

usando el gestor de SPI. En la expresión, las ocurrencias de los identificadores
de las variables se sustituyen por parámetros y los valores reales de las variables
se pasan al ejecutor en el arreglo de parámetros. Todas las expresiones usadas
en una función de PL/pgSQL son preparadas y almacenadas solamente una vez.
La única excepción a esta regla es una sentencia EXECUTE si se requiere analizar
una consulta cada vez que es encontrada.

La revisión del tipo realizada por el analizador sintáctico principal de Post-
gres tiene algunos efectos secundarios a la interpretación de valores constantes.
En detalle, existe una diferencia entre lo que hacen estas dos funciones:

CREATE FUNCTION logfunc1 (text) RETURNS times