Pl/pgsql

Pl/pgsql

PL/pgSQL (Procedural Language/PostgreSQL

Structured Query Language) es un lenguaje
imperativo provisto por el gestor de base de
datos PostgreSQL. Permite ejecutar comandos
SQL mediante un lenguaje de sentencias
imperativas y uso de funciones, dando mucho
ms control automtico que las sentencias SQL
bsicas.

Pl/pgsql

Desde PL/pgSQL se pueden realizar clculos

complejos y crear nuevos tipos de datos de
usuario. Como un verdadero lenguaje de
programacin, dispone de estructuras de
control repetitivas y condicionales, adems de
la posibilidad de creacin de funciones que
pueden ser llamadas en sentencias SQL
normales o ejecutadas en eventos de tipo
disparador (trigger).

Pl/pgsql

Una de las principales ventajas de ejecutar

programacin en el servidor de base de datos
es que las consultas y el resultado no tienen
que ser transportadas entre el cliente y el
servidor, ya que los datos residen en el propio
servidor. Adems, el gestor de base de datos
puede planificar optimizaciones en la ejecucin
de la bsqueda y actualizacin de datos.

Pl/pgsql

Las funciones escritas en PL/pgSQL aceptan

argumentos y pueden devolver valores de tipo
bsico o de tipo complejo (por ejemplo,
registros, vectores, conjuntos o incluso tablas),
permitindose tipificacin polimrfica para
funciones abstractas o genricas (referencia a
variables de tipo objeto).

Pl/pgsql

Los objetivos de diseo para PL/pgSQL fueron

crear un lenguaje procedural que

pudiera ser usado para crear funciones y triggers,

Aadir a estructuras de control al lenguaje SQL,

pudiera realizar operaciones complejas,

heredase todos los tipos definidos por el usuario,

funciones y operadores,

pudiera ser definido para ser validado por el

servidor,

fuese sencillo de utilizar.

Pl/pgsql

CREATE FUNCTION populate() RETURNS

INTEGER AS '
DECLARE
-- Declarations
BEGIN
PERFORM my_function();

END;

' LANGUAGE 'plpgsql';

Pl/pgsql Desarrollando en PL/pgSQL

Desarrollar en PL/pgSQL es agradable y rpido,

especialmente si ya ha desarrollado con otros
lenguajes procedurales de bases de datos, tales como
el PL/SQL de Oracle. Dos buenas formas de
desarrollar en PL/pgSQL son:
Usar un editor de texto y recargar el archivo con psql
Usando la herramienta de usuario de PostgreSQL:
PgAccess
Usando la herramienta de usuario de PostgreSQL:
PgAamin3

Pl/pgsql

Una buena forma de desarrollar en PL/pgSQL

es simplemente usar el editor de textos de su
eleccin y, en otra ventana, usar psql (monitor
interactivo de PostgreSQL) para cargar las
funciones. Si lo est haciendo de sta forma,
es una buena idea escribir la funcin usando
CREATE OR REPLACE FUNCTION. De esta
forma puede recargar el archivo para actualizar
la definicin de la funcin. Por ejemplo:

Pl/pgsql

CREATE OR REPLACE FUNCTION

testfunc(INTEGER) RETURNS INTEGER AS '

....

end; '

LANGUAGE 'plpgsql';

Mientras corre psql, puede cargar o recargar la

definicin de dicha funcin con
\i [Link]

Pl/pgsql Estructura de PL/pgSQL

PL/pgSQL es un lenguaje estructurado de

bloques. El texto completo de una definicin de
funcin debe ser un bloque. Un bloque se
define como:

[ <<etiqueta>> ]

[ DECLARE

declaraciones ]
BEGIN
estamentos
END;

Detalles Lxicos

Cada Sentencia y declaracin dentro de un

bloque est terminado por un punto y coma.
Todas las palabras clave e identificadores
pueden ser escritos en caracteres
maysculas/minsculas. Los identificadores
sern implcitamente convertidos a minsculas,
a menos que estn entrecomillados (usando
para ello comillas dobles).

Detalles Lxicos

Hay dos tipos de comentarios en PL/pgSQL. Un

guin doble -inicia un comentario que se
extiende hasta el final de la lnea-. Un /* marca
el inicio de un bloque de comentarios que se
extiende hasta la siguente ocurrencia de */. Los
bloques de comentarios no pueden anidarse,
pero los comentarios con guiones dobles
pueden encerrarse en un bloque de
comentarios, y un doble guin puede ocultar los
delimitadores de bloques de comentarios /* y */.

Declaraciones

Todas las variables, filas y registros usados en

un bloque deben estar declaradas en la
seccin de declaraciones del bloque (la nica
excepcin es que la variable de bucle para una
iteracin de bloque FOR sobre un rango de
valores enteros es automticamente declarada
como variable entera).

Declaraciones

Las variables PL/pgSQL pueden ser de cualquier tipo

de datos SQL, tales como INTEGER, VARCHAR y
CHAR.
Aqu tiene algunos ejemplos de declaracin de
variables:

user_id INTEGER;

cantidad NUMERIC(5);

url VARCHAR;

myrow nombretabla%ROWTYPE;

myfield [Link]%TYPE;

unafila RECORD;

Declaraciones

La sintaxis general de una declaracin de

variables es:
nombre [ CONSTANT ] tipo [ NOT NULL ]
[ { DEFAULT | := } expresin ];

Declaraciones

La clusula DEFAULT, si existe, especifica el

valor inicial asignado a la variable cuando el
bloque es introducido. Sin la clusula DEFAULT
no es facilitada entonces la variable es
inicializada al valor SQL NULL.

Declaraciones

La opcin CONSTANT previene la asignacin

de otros valores a la variable, as que su valor
permanece durante la duracin del bloque. Si
se especifica NOT NULL, una asignacin de un
valor NULL resulta en un error en tiempo de
ejecucin. Todas las variables declaradas como
NOT NULL deben tener un valor por defecto no
nulo especificado.

Creacion de Funciones

Para crear nuestras funciones debemos dentro

de postgres crear una base de datos llamada
por ejemplo FUNCIONES

Uso del RowType

postgres=# create database funciones;

CREATE DATABASE

Seguidamente

Te debes conectar a esta base de datos con el

siguiente comando
postgres=# \c funciones
Ahora est conectado a la base de datos
funciones.

Se Crea una Tabla llamada Prueba

funciones=# create table prueba1(cedula int,

nombre varchar(30));
CREATE TABLE

Insertar Datos a la Tabla

funciones=# insert into prueba1 values(101010, 'noel');

funciones=# insert into prueba1 values(101010, 'marina');

funciones=# insert into prueba1 values(101010, 'paula');

funciones=# insert into prueba1 values(101010, 'maria');

funciones=# insert into prueba1 values(202020, 'alicia');

funciones=# insert into prueba1 values(202020, 'marena');

funciones=# insert into prueba1 values(202020, 'magnolia');

funciones=# insert into prueba1 values(303030, 'tina');

funciones=# insert into prueba1 values(404040, 'gina');

funciones=# insert into prueba1 values(404040, 'fiona');

funciones=# insert into prueba1 values(505050, 'tine play');

funciones=# insert into prueba1 values(505050, 'jennifer');

funciones=# insert into prueba1 values(505050, 'mery');

funciones=# insert into prueba1 values(505050, 'allison');

Verificamos el Contenido

funciones=# select * from prueba1;

cedula | nombre
--------+-----------

101010 | noel

101010 | marina

101010 | paula

101010 | maria

202020 | alicia

202020 | marena

202020 | magnolia

303030 | tina

Verificamos el Contenido

404040 | gina

404040 | fiona

505050 | tine play

505050 | jennifer

505050 | mery

505050 | allison

606060 | antonella

(15 filas)

Luego se crea el lenguaje

funciones=# create procedural language

plpgsql;
CREATE LANGUAGE

Error

Si no se crea el el lenguaje no se prodran crear

las funciones en postgres, y dar el siguiente
error

Error

funciones=# create or replace function

retornando() returns int4 as ' begin return 5+5;
end; ' language 'plpgsql';
ERROR: no existe el lenguaje plpgsql
HINT: Usar CREATE LANGUAGE para instalar
el lenguaje en la base de datos.
funciones=#

Crear la funcion

Finalmente se debe proceder a crear la funcion

en un editor de textos, en el pgAccess, en el
pgAdmin3 o directamente en la consola segn
nuestro gusto?

Ejemplo de Constantes

funciones=# CREATE OR REPLACE

FUNCTION elradio(raddio int4) RETURNS int4
AS $BODY$ DECLARE pi CONSTANT Float :=
3.141516171848; BEGIN RETURN pi*$1*$1;
END; $BODY$ LANGUAGE 'plpgsql'
VOLATILE;
CREATE FUNCTION

Ejemplo de Constantes

funciones=# select elradio(24);

ERROR: la sintaxis de entrada no es valida
para integer: 1809.51331498445
CONTEXT: PL/pgSQL function "elradio" while
casting return value to function's return type

Ejemplo de Constantes

funciones=# drop function elradio(raddio int4);

DROP FUNCTION

Ejemplo de Constantes

funciones=# CREATE OR REPLACE

FUNCTION elradio(raddio int4) RETURNS float
AS $BODY$ DECLARE pi CONSTANT Float :=
3.141516171848; BEGIN RETURN pi*$1*$1;
END; $BODY$ LANGUAGE 'plpgsql'
VOLATILE;
CREATE FUNCTION

Ejemplo de Constantes

funciones=# select elradio(12);

elradio

------------------

452.378328746112
(1 fila)

Para ver una Funcin Definida

por el Usuario

\df elradi*

Ejemplo de Default

funciones=# CREATE OR REPLACE

FUNCTION pordefecto(valorr int4) RETURNS
int4 AS 'DECLARE numero CONSTANT
INTEGER := 5; BEGIN RETURN $1*numero;
END; ' LANGUAGE 'plpgsql';
CREATE FUNCTION

Ejemplo de Default

funciones=# select pordefecto(12);

pordefecto
-----------60
(1 fila)

Declaraciones

El valor por defecto es evaluado cada vez que

el bloque es introducido. As, por ejemplo, la
asignacin de now a una variable de tipo
timestamp provoca que la variable tenga la
fecha y hora de la llamada a la actual funcin, y
no la fecha y hora de su compilacin.

Declaraciones

Ejemplos:

cantidad INTEGER DEFAULT 32;

url varchar := ''[Link]

user_id CONSTANT INTEGER := 10;

Alias para los Parmetros

de la Funcin

nombre ALIAS FOR $n;

Los parmetros pasados a las funciones son
nominados con los identificadores $1, $2, etc.
Opcionalmente, se pueden declara alias para
los nombres de los parmetros, con el objeto
de incrementar la legibilidad del cdigo. Tanto
el alias como el identificador numrico pueden
ser utilizados para referirse al valor del
parmetro.

Algunos ejemplos:

CREATE FUNCTION tasa_ventas(REAL)

RETURNS REAL AS '
DECLARE
subtotal ALIAS FOR $1;
BEGIN
return subtotal * 0.06;

END;

' LANGUAGE 'plpgsql';

Algunos ejemplos:

CREATE FUNCTION
instr(VARCHAR,INTEGER) RETURNS
INTEGER AS '
DECLARE

v_string ALIAS FOR $1;

index ALIAS FOR $2;

BEGIN
-- Algunas computaciones aqu

END;

' LANGUAGE 'plpgsql';

Algunos ejemplos:

CREATE FUNCTION
usa_muchos_campos(tablename) RETURNS
TEXT AS '
DECLARE
in_t ALIAS FOR $1;
BEGIN
RETURN in_t.f1 || in_t.f3 || in_t.f5 || in_t.f7;

END;

' LANGUAGE 'plpgsql';

Ejemplo para la Clase

funciones=# CREATE OR REPLACE

FUNCTION Alias24(numero1 int4, numero2
int4, numero3 int4) RETURNS int4 AS
'DECLARE nu1 Alias for $1; nu2 Alias for $2;
nu3 Alias for $3; SUMARR INT; BEGIN
SUMARR := nu1+nu2+nu3; RETURN
SUMARR; END;' LANGUAGE 'plpgsql';
CREATE FUNCTION

Ejemplo para la Clase

funciones=# select Alias24(5, 5, 5);

alias24
---------

15

(1 fila)

Tipos Fila (Row)

nombre nombretabla%ROWTYPE;
Una variable de un tipo compuesto es
denominada variable de fila (row-type variable).
Una variable de este tipo puede almacenar una
fila completa del resultado de una consulta
SELECT o FOR, mientras que la columna de
dicha consulta coincida con el tipo declarado
para la variable. Los campos individuales del
valor de la fila son accedidos usando la tpica
notacin de puntos, por ejemplo
[Link].

Tipos Fila (Row)

En la actualidad, una variable de tipo fila slo

puede ser declarada usando la notacin
%ROWTYPE; aunque uno podra esperar que
un nombre pblico de nombre de tabla
funcionase como tipo de declaracin, sta no
sera aceptada dentro de funciones PL/pgSQL.

Tipos Fila (Row)

Los parmetros para una funcin pueden ser

tipos compuestos (filas completas de tablas).
En ese caso, el correspondiente identificador
$n ser una variable tipo fila, y los campos
podrn ser accedidos, por ejemplo
$[Link].

Tipos Fila (Row)

Slo los atributos de una tabla definidos por el

usuario son accesibles en una variable tipo fila,
y no OID u otros atributos de sistema (porque
la fila podra venir de una vista). Los campos
del tipo fila heredan el tamao del campo de la
tabla as como la precisin para tipos de datos,
tales como char(n).

Tipos Fila (Row)

CREATE FUNCTION usa_dos_tablas(tablename)

RETURNS TEXT AS '
DECLARE

in_t ALIAS FOR $1;

use_t tabla2nombre%ROWTYPE;

BEGIN

SELECT * INTO use_t FROM tabla2nombre WHERE ... ;

RETURN in_t.f1 || use_t.f3 || in_t.f5 || use_t.f7;

END;

' LANGUAGE 'plpgsql';

Uso del RowType

funciones=# CREATE OR REPLACE

FUNCTION mostrar_estudiantes(int4)
RETURNS "varchar" AS $BODY$declare
registros prueba1%ROWTYPE; BEGIN
SELECT INTO registros * FROM prueba1
WHERE cedula=$1; IF NOT FOUND THEN
RETURN 0; END IF; RETURN registros; end;
$BODY$ LANGUAGE 'plpgsql' VOLATILE;
CREATE FUNCTION

Uso del RowType

funciones=# select
mostrar_estudiantes(606060)
funciones-# ;
mostrar_estudiantes
--------------------(606060,antonella)
(1 fila)

Records

nombre RECORD;
Las variables de tipo registro (record) son
similares a las tipo fila, pero no tienen una
estructura predefinida. Ellas la toman de la
actual estructura de la fila que tienen asignada
durante un comando SELECT o FOR. La
subestructura de una variable tipo registro
puede variar cada vez que se le asigne un
valor.

Records

Una consecuencia de esto es que hasta que a

una variable tipo registro se le asigne valor por
vez primera, sta no tendr subestructura, y
cualquier intento de acceder a un campo en
ella provocar un error en tiempo de ejecucin.
Advertencia un RECORD no es un verdadero
tipo de datos, slo un almacenador.

Atributos

Usando los atributos %TYPE y %ROWTYPE,

puede declarar variables con el mismo tipo de
datos o estructura que otro elemento de la
base de datos ([Link]: un campo de tabla).
variable%TYPE

Atributos

%TYPE proporciona el tipo de datos de una

variable o de una columna de base de datos.
Puede usar esto para declarar variables que
almacenen valores de base de datos. Por
ejemplo, digamos que tiene una columna
llamada user_id en su tabla usuarios. Para
declarar una variable con el mismo tipo de
datos que usuarios.user_id usted escribira:
user_id usuarios.user_id%TYPE;

Atributos

Usando %TYPE no necesita conocer el tipo de

datos de la estructura a la que est
referenciando, y lo ms importante, si el tipo de
datos del elemento referenciado cambia en el
futuro ([Link].: usted cambia su definicin de
tabla para user_id de INTEGER a REAL), no
necesitar cambiar su definicin de funcin.

Atributos

tabla%ROWTYPE
%ROWTYPE proporciona el tipo de datos
compuesto correspondiente a toda la fila de la
tabla especificada. La tabla debe ser una tabla
existente o un nombre de vista de la base de
datos.

Atributos

DECLARE

users_rec usuarios%ROWTYPE;

user_id usuarios.user_id%TYPE;

BEGIN

user_id := users_rec.user_id;

...

Atributos

CREATE FUNCTION does_view_exist(INTEGER) RETURNS bool 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

RETURN false;

END IF;

RETURN true;

END;

' LANGUAGE 'plpgsql';

RENAME

RENAME oldname TO newname;

Usando la declaracin RENAME puede
cambiar el nombre de una variable, registro o
fila. Esto es inicialmente til si NEW o OLD
debieran ser referenciados por otro nombre
dentro de un procedimiento trigger. Vea
tambin ALIAS.

RENAME

Ejemplos:

RENAME id TO user_id;

RENAME this_var TO that_var;

NOTA: ALIAS cubre la mayora de los usos

prcticos de RENAME.

Expresiones

Todas las expresiones utilizadas en los

estamentos PL/pgSQL son procesados usando
el ejecutor SQL regular del servidor. Las
expresiones que parecen contener constantes
pueden de hecho requerir evaluacin en tiempo
de ejecucin ([Link]. ``now'' para el tipo
timestamp), as que es imposible para el
intrprete de PL/pgSQL identificar valores reales
de constantes aparte del valor clave NULL.
Todas las expresiones son evaluadas
internamente al ejecutar la consulta

Expresiones

SELECT expresin
En la expresin, las ocurrencias de
identificadores de variables PL/pgSQL son
reemplazadas por parmetros, y los actuales
valores de las variables son pasados al
ejecutor en el array de parmetros. Esto
permite al plan de consultas para el SELECT
que sea preparado slo una vez, y luego
reutilizado para subsequentes evaluaciones.

Expresiones

La evaluacin realizada por el intrprete

principal de PostgreSQL tiene algunos efectos
de cara a la interpretacin de valores de
constantes. En detalle aqu est la diferencia
entre lo que hacen estas dos funciones:

Expresiones

CREATE FUNCTION logfunc1 (TEXT)

RETURNS TIMESTAMP AS '
DECLARE
logtxt ALIAS FOR $1;
BEGIN
INSERT INTO logtable VALUES (logtxt,
''now'');
RETURN ''now'';

END;

' LANGUAGE 'plpgsql';

Expresiones

En el caso de logfunc1(), el intrprete principal

de PostgreSQL sabe cundo preparar el plan
para el INSERT, y la cadena 'now' debera ser
interpretada como un timestamp debido a que
el campo destino de logtable es de ese tipo.
As, crear una constante a partir de l y su
valor constante ser usado luego en todas las
invocaciones de logfunc1() durante el tiempo
de vida del motor. No es necesario decir que
esto no era lo que deseaba el programador.

Expresiones

CREATE FUNCTION logfunc2 (TEXT) RETURNS TIMESTAMP AS '

DECLARE

logtxt ALIAS FOR $1;

curtime timestamp;

BEGIN

curtime := ''now'';

INSERT INTO logtable VALUES (logtxt, curtime);

RETURN curtime;

END;

' LANGUAGE 'plpgsql';

Expresiones

En el caso de logfunc2(), el intrprete principal

de PostgreSQL no sabe a qu tipo debera
pertenecer 'now' y por tanto devuelve un valor
de tipo text conteniendo la cadena 'now'.
Durante la siguiente asignacin a la variable
local curtime, el intrprete de PL/pgSQL casa
esta cadena con el tipo timestamp llamando a
las funciones text_out() y timestamp_in() para la
conversin. As, el timestamp computado es
actualizado en cada ejecucin, tal como
esperaba el programador.

Expresiones

La naturaleza mutable de las variables tipo registro

presenta un problema en su conexin. Cuando los
campos de una variable registro son usados en
expresiones o estamentos, los tipos de datos de los
campos no debe cambiar entre llamadas de una y la
misma expresin, ya que la expresin ser planeada
usando el tipo de datos que estaba presente cuando
la expresin fue analizada por vez primera. Recuerde
esto cuando escriba procedimientos trigger que
manejen eventos para ms de una tabla (EXECUTE
puede ser usado para resolver este problema cuando
sea necesario).

Comandos Bsicos

En sta seccin y las siguientes subsecciones,

describiremos todos los tipos de estamentos
explcitamente reconocidos por PL/pgSQL. Cualquiera
no reconocido como uno de estos tipos de estamentos
se presume ser una consulta SQL, y es enviada al
motor de la base de datos para ser ejecutado (tras la
sustitucin de cualesquiera variables PL/pgSQL
usadas en el estamento). As, por ejemplo, los
comandos SQL INSERT, UPDATE, y DELETE pueden
ser considerados para ser estamentos de PL/pgSQL.
Pero ellos no estn explcitamente listados aqu

Comandos Bsicos

Asignacin
Una asignacin de un valor a una variable o
campo de fila/registro se escribe:
identificador := expresin;

Ejemplo de Asignacion

funciones=# CREATE OR REPLACE

FUNCTION AsignandoValores(numero1 int4)
RETURNS int4 AS' DECLARE valor int; BEGIN
valor:=$1+10; RETURN valor; END;'
LANGUAGE 'plpgsql';
CREATE FUNCTION

Ejemplo de Asignacion

funciones=# select AsignandoValores(5);

asignandovalores
-----------------15

(1 fila)

Comandos Bsicos

Como se explic anteriormente, la expresin en

el estamento es evaluada como si de un envo
de comando SQL SELECT al motor se tratara.
La expresin debe contener un nico valor.

Comandos Bsicos

Si el tipo de datos resultante de la expresin no

coincide con el tipo de datos de la variable, o la
variable tiene un determinado tamao/precisin
(tal como char(20)), el valor resultante ser
implcitamente convertido por el intrprete
PL/pgSQL usando la resultante funcin de tipos
output-function y el tipo de variable inputfunction. Advierta que esto podra
potencialmente resultar en errores en tiempo de
ejecucin generados por la funcin de entrada,
si el formato de la cadena del valor resultante
no es aceptable para la funcin de entrada.

Comandos Bsicos

Ejemplos:

user_id := 20;

tax := subtotal * 0.06;

SELECT INTO

El resultado de un comando SELECT que

contiene mltiples columnas (pero slo una fila)
puede ser asignado a una variable tipo registro,
tipo fila, o variables escalares o de lista. Esto
se hace as:
SELECT INTO destino expressions FROM ...;

SELECT INTO

donde destino puede ser una variable registro,

fila, o una lista separada por comas de
variables simples y campos registro/fila.
Advierta que esto es algo diferente a la
interpretacin normal que hace PostgreSQL del
SELECT INTO, la cual es que el destino (target)
INTO es una tabla recin creada (si quiere crear
una tabla a partir de un resultado de SELECT
dentro de una funcin PL/pgSQL, use la sintaxis
CREATE TABLE ... AS SELECT).

SELECT INTO

Si una fila o lista de variables es usada como

destino, los valores seleccionados deben
coincidir exactamente con la estructura de los
destinos, u ocurrir un error en tiempo de
ejecucin. Cuando una variable tipo registro es
el destino, esta automticamente se
configurar al tipo fila de las columnas
resultantes de la consulta.

SELECT INTO

Excepto por la clusula INTO, el estamento

SELECT es igual que el normal de la consulta
SQL SELECT y puede usar todo el potencial de
SELECT.
Si la consulta SELECT retorna cero filas, valores
nulos son asignados a los destinos. Si la
consulta SELECT retorna mltiples filas, la
primera es asignada a los destinos y el resto es
descartado (Advierta que la ``primera fila''
depender del uso de ORDER BY.)

SELECT INTO

Actualmente, la clusula INTO puede aparecer

en cualquier lugar en la clusula SELECT, pero
se recomienda ubicarla inmediatamante
despus de la palabra clave SELECT. Futuras
versiones de PL/pgSQL pueden ser menos
restrictivas sobre esto.
Puede usar FOUND immediatamente despus
de un estamento SELECT INTO para determinar
si la asignacin tuvo xito (es decir, al menos
una fila fue retornada por el estamento
SELECT). Por ejemplo:

SELECT INTO

SELECT INTO myrec * FROM EMP WHERE

empname = myname;
IF NOT FOUND THEN
RAISE EXCEPTION ''employee % not found'',
myname;
END IF;

SELECT INTO

Alternativamente, puede usar el condicional IS

NULL (o ISNULL) para testear si un resultado
de RECORD/ROW es nulo. Advierta que no
hay forma de saber si adicionales filas han sido
descartadas.

SELECT INTO

DECLARE

users_rec RECORD;

full_name varchar;

BEGIN

SELECT INTO users_rec * FROM users WHERE user_id=3;

IF users_rec.homepage IS NULL THEN

-- user entered no homepage, return "[Link]

RETURN ''[Link]

END IF;
END;

Ejemplo de Select into

funciones=# CREATE OR REPLACE

FUNCTION Seleccion() RETURNS "varchar"
AS ' declare datos prueba1%rowTYPE; BEGIN
SELECT INTO datos nombre FROM prueba1
WHERE cedula=101010; IF NOT FOUND
THEN RETURN 0; END IF; RETURN datos;
end;' LANGUAGE 'plpgsql';
CREATE FUNCTION

Ejemplo de Select into

funciones=# select seleccion();

ERROR: la sintaxis de entrada no es valida
para integer: noel
CONTEXT: PL/pgSQL function "seleccion" line
1 at select into variables

Ejemplo de Select into

funciones=# CREATE OR REPLACE

FUNCTION Seleccion() RETURNS text AS '
declare datos [Link]%TYPE; BEGIN
SELECT INTO datos nombre FROM prueba1
WHERE cedula=101010; IF NOT FOUND
THEN RETURN false; END IF; RETURN datos;
end;' LANGUAGE 'plpgsql';

Ejemplo de Select into

funciones=# select Seleccion();

seleccion
----------noel
(1 fila)

Executando una expresin o

consulta sin resultado Perform

Algunas veces uno desea evaluar una

expresin o consulta pero descartando el
resultado (normalmente porque uno est
llamando a una funcin que tiene efectos tiles,
pero un resultado intil). Para hacer esto en
PL/pgSQL, use el estamento PERFORM:

Executando una expresin o

consulta sin resultado Perform

PERFORM query;
Esto ejecuta una consulta SELECT y descarta
el resultado. Las variables PL/pgSQL son
sustituidas en la consulta de la forma usual.
Adems, la variable especial FOUND se
establece a true si la consulta produce al
menos una fila, o false si no produce ninguna.

Executando una expresin o

consulta sin resultado Perform

NOTA: Uno podra esperar que SELECT sin

clusuala INTO podra acompaar al resultado,
pero actualmente la nica forma aceptada para
hacerlo es con PERFORM.

Ejemplo de Perform

funciones=# CREATE OR REPLACE

FUNCTION Desempeo() RETURNS int AS '
BEGIN PERFORM * FROM prueba1 WHERE
cedula=101010; IF NOT FOUND THEN RAISE
NOTICE ''Datos no encontrados''; RETURN 0;
ELSE RAISE NOTICE ''Datos Encontrados'';
RETURN 1; END IF; END; ' LANGUAGE
plpgsql;
CREATE FUNCTION

Ejemplo de Perform

funciones=# select Desempeo();

NOTICE: Datos Encontrados

desempeo
----------1
(1 fila)

Ejemplo del Uso de Raise

CREATE FUNCTION mensajes() RETURNS

void AS'
BEGIN
RAISE NOTICE ''Los parametros de entrada
son: Cedula, Nombre'';
END; 'LANGUAGE 'plpgsql';

Ejemplo del Uso de Raise

CREATE FUNCTION Noticion() RETURNS int4

AS '

DECLARE cantidad integer := 30;

BEGIN

RAISE NOTICE ''El Dinero a conbrar Es %'',

cantidad;

return cantidad;

END;

'LANGUAGE 'plpgsql';

Retornando desde una Funcin

RETURN expression;
RETURN con una expresin es usado para
retornar desde una funcin PL/pgSQL que no
retorna nada. La funcin termina y el valor de la
expresin es retornado al peticionario.

Retornando desde una Funcin

Para retornar un valor compuesto (una fila),

usted debe escribir una variable registro o fila
como expresin. Cuando retorne una tipo
escalar, cualquier expresin pude ser usada. El
resultado de la expresin ser
automticamente convertido al tipo de retorno
de la funcin (si ha declarado la funcin para
que retorne void (nulo), entonces la expresin
puede ser omitida, y se ignorar en cualquier
caso).

Retornando desde una Funcin

El valor de retorno de una funcin no puede ser

dejado sin definir. Si el control llega al final del
bloque de mayor nivel de la funcin sin detectar
un estamento RETURN, ocurrir un error en
tiempo de ejecucin.

Return

funciones=# create or replace function

retornando() returns int4 as ' begin return 5+5;
end; ' language 'plpgsql';
CREATE FUNCTION

Return

funciones=# select retornando();

retornando
-----------10
(1 fila)

Condicionales

el comando IF es usado para validar ciertas

condiciones, en este caso PL/pgSQL tiene
cuatro formas de IF

IF ... THEN

IF ... THEN ... ELSE

IF ... THEN ... ELSE IF and

IF ... THEN ... ELSIF ... THEN ... ELSE

IF-THEN

Los comandos IF-THEN son el formato ms

simple del IF. Los comandos entre THEN y
END IF sern ejecutados si la condicin se
cumple. En caso contrario, sern ignorados.
IF expression-boolean THEN

Comandos

END IF;

IF-THEN

IF v_user_id <> 0 THEN

UPDATE users SET email = v_email WHERE
user_id = v_user_id;
END IF;

Ejemplo de IF

funciones=# create or replace function

sisimple(int4) returns text as ' begin if $1 > 10
then RAISE NOTICE ''el numero es mayor de
10''; end if; return true; end; ' language 'plpgsql';
CREATE FUNCTION

Ejemplo de IF

funciones=# select sisimple(5);

sisimple
---------t

(1 fila)

funciones=# select sisimple(14);

NOTICE: el numero es mayor de 10

sisimple
---------t
(1 fila)

IF-THEN-ELSE

IF boolean-expression THEN
estamentos
ELSE
estamentos
END IF;

IF-THEN-ELSE

Los comandos IF-THEN-ELSE aadidos al IFTHEN le permiten especificar un juego

alternativo de comandos que deberan ser
ejecutados si la condicin se evala a FALSE.

IF-THEN-ELSE

IF parentid IS NULL or parentid = '''' THEN

return fullname;
ELSE
return hp_true_filename(parentid) || ''/'' ||
fullname;
END IF;

IF-THEN-ELSE

IF v_count > 0 THEN

INSERT INTO users_count(count)
VALUES(v_count);
RETURN ''t'';
ELSE
RETURN ''f'';
END IF;

Ejemplo de IF-THEN-ELSE

funciones=# create or replace function

sisimpleeElse(int4) returns text as ' begin if $1
> 10 then RAISE NOTICE ''el numero es mayor
de 10''; else RAISE NOTICE ''el numero es
mas pequeo que 10''; end if; return true;
end; ' language 'plpgsql';
CREATE FUNCTION

Ejemplo de IF-THEN-ELSE

funciones=# select sisimpleeElse(14);

NOTICE: el numero es mayor de 10

sisimpleeelse
--------------t

(1 fila)

funciones=# select sisimpleeElse(4);

NOTICE: el numero es mas pequeo que 10

sisimpleeelse
--------------t
(1 fila)

IF-THEN-ELSE IF

Los comandos IF pueden anidarse, tal como en

el siguiente ejemplo:
IF demo_row.sex = ''m'' THEN
pretty_sex := ''man'';
ELSE
IF demo_row.sex = ''f'' THEN
pretty_sex := ''woman'';
END IF;
END IF;

IF-THEN-ELSE IF

Cuando use este formato, estar anidando un

estamento IF dentro de la parte ELSE de un
estamento IF superior. Necesitar un comando
END IF por cada IF anidado, y uno para el IFELSE padre. Esto funciona, pero es tedioso
cuando existen muchas alternativas a ser
chequeadas.

Ejemplo de IF-THEN-ELSE IF

funciones=# create or replace function

sicomplejo(int4) returns text as ' begin if $1 <
10 then RAISE NOTICE ''el numero es mas
pequeo que 10''; else if $1 >=10 then RAISE
NOTICE ''el numero es mayor o igual a 10'';
end if; end if; return true; end; ' language
'plpgsql';
CREATE FUNCTION

Ejemplo de IF-THEN-ELSE IF

funciones=# select sicomplejo(4);

NOTICE: el numero es mas pequeo que 10

sicomplejo
-----------t

(1 fila)

funciones=# select sicomplejo(78);

NOTICE: el numero es mayor o igual a 10

sicomplejo
-----------t
(1 fila)

IF-THEN-ELSIF-ELSE

IF boolean-expression THEN
estamentos
[ ELSIF boolean-expression THEN
estamentos
[ ELSIF boolean-expression THEN
estamentos ...]]
[ ELSE
estamentos ]
END IF;

IF-THEN-ELSIF-ELSE

IF-THEN-ELSIF-ELSE proporciona un mtodo

ms conveniente de chequear muchas
alternativas en un comando. Formalmante es
equivalente a comandos IF-THEN-ELSE-IFTHEN anidados, pero slo se necesita un END
IF.

IF-THEN-ELSIF-ELSE

Aqu tiene un ejemplo:

IF number = 0 THEN

result := ''zero'';
ELSIF number > 0 THEN
result := ''positive'';
ELSIF number < 0 THEN
result := ''negative'';
ELSE

-- hmm, the only other possibility is that number IS NULL

result := ''NULL'';

END IF;

IF-THEN-ELSIF-ELSE

La seccin ELSE final es opcional.

Ejemplo IF-THEN-ELSIF-ELSE

funciones=# create or replace function

sicomplejomascomplejoaun(int4) returns text as
' Declare Resultado varchar; begin IF $1=0
THEN Resultado:=''Numero Igual a Cero''; return
Resultado; ELSIF $1 > 0 THEN
Resultado:=''Numero Positivo''; return
Resultado; ELSIF $1 < 0 THEN
Resultado:=''Numero Negativo''; return
Resultado; ELSE Resultado:=''Numero es nulo'';
return Resultado; END IF; END; ' language
'plpgsql';
CREATE FUNCTION

Ejemplo IF-THEN-ELSIF-ELSE

funciones=# select
sicomplejomascomplejoaun(0);
sicomplejomascomplejoaun
-------------------------Numero Igual a Cero
(1 fila)

Ejemplo IF-THEN-ELSIF-ELSE

funciones=# select
sicomplejomascomplejoaun(14);
sicomplejomascomplejoaun
-------------------------Numero Positivo
(1 fila)

Ejemplo IF-THEN-ELSIF-ELSE

funciones=# select
sicomplejomascomplejoaun(-14);
sicomplejomascomplejoaun
-------------------------Numero Negativo
(1 fila)

Bucles Simples

Con los Comandos LOOP, EXIT, WHILE y

FOR, usted puede programar su funcin
PL/pgSQL para repetir una serie de comandos.

a Saber son:

LOOP

EXIT

WHILE

FOR (integer for-loop)

LOOP

[<<etiquetal>>]

LOOP

estamentos
END LOOP;
LOOP define un bucle incondicional que es
repetido indefinidamente hasta que sea
termiando por un estamento EXIT o RETURN.
La etiqueta opcional puede ser usada por
comandos EXIT en bucles anidados para
especificar qu nivel de anidacin debera ser
terminado.

LOOP

funciones=# CREATE OR REPLACE

FUNCTION CICLO1() RETURNS INT4 AS
$BODY$ DECLARE I integer:=0; BEGIN FOR i
IN 1..10 LOOP RAISE NOTICE 'i is %',i; END
LOOP; RETURN TRUE; END; $BODY$
LANGUAGE 'plpgsql';
CREATE FUNCTION

LOOP

funciones=# SELECT CICLO1();

NOTICE: i is 1

NOTICE: i is 2

NOTICE: i is 3

NOTICE: i is 4

NOTICE: i is 5

NOTICE: i is 6

NOTICE: i is 7

NOTICE: i is 8

NOTICE: i is 9

NOTICE: i is 10

EXIT

EXIT [ etiqueta ] [ WHEN expresin ];

Si no se proporciona etiqueta, el bucle ms al
interior es terminado y el comando ms
cercano a END LOOP es ejecutado a
continuacin. Si se proporciona etiqueta, esta
debe ser la etiqueta del bucle o bloque ms
actual o de nivel externo. Entonces el bucle o
bloque nombrado es terminado, y el control
contina con el comando que sigue al
correspondiente END del bucle/bloque.

EXIT

Si WHEN est presente, slo ocurrir la salida

del bucle si la condicin especificada es cierta,
de lo contrario pasa al comando tras EXIT.

Ejemplos:

LOOP

-- algunas computaciones

IF count > 0 THEN

EXIT; -- exit loop

END IF;
END LOOP;

EXIT

LOOP

-- algunas computaciones

EXIT WHEN count > 0;

END LOOP;

BEGIN

-- algunas computaciones

IF stocks > 100000 THEN

EXIT; -- ilegal. No puede usar EXIT fuera de un

LOOP
END IF;
END;

Ejemplo de Exit

funciones=# CREATE OR REPLACE

FUNCTION SalidaBruta() RETURNS INT AS
$BODY$ DECLARE i integer:=0; BEGIN FOR i
IN 1..10 LOOP RAISE NOTICE 'i is %',i; EXIT
WHEN i>0; END LOOP; RETURN 1; END;
$BODY$ LANGUAGE 'plpgsql';
CREATE FUNCTION

Ejemplo de Exit

funciones=# select SalidaBruta();

NOTICE: i is 1

salidabruta
------------1

(1 fila)

WHILE

[<<etiqueta>>]

WHILE expresin LOOP

estamentos
END LOOP;

WHILE

El comando repite una secuencia de comando

mientras que la condicin se evale a
verdadero. La condicin es chequeada jsuto
antes de cada entrada al cuerpo del bucle.

WHILE

Por ejemplo:
WHILE amount_owed > 0 AND
gift_certificate_balance > 0 LOOP
-- algunas computaciones aqu

END LOOP;

WHILE NOT boolean_expression LOOP

-- algunas computaciones aqu

END LOOP;

Ejemplo de WHILE

funciones=# CREATE FUNCTION

imprimirmientras(int4, int4 ) RETURNS integer
AS ' DECLARE num1 ALIAS FOR $1; num2
ALIAS FOR $2; registros INTEGER = 0; BEGIN
registros:=num1; WHILE registros <= num2
LOOP registros := registros + 1; END LOOP;
RETURN registros; END; ' LANGUAGE
'plpgsql';
CREATE FUNCTION

Ejemplo de WHILE

funciones=# select imprimirmientras(5, 10) ;

imprimirmientras
-----------------11

(1 fila)

Ejemplo de WHILE

Correccion del anterior

FOR (integer for-loop)

[<<etiqueta>>]
FOR nombre IN [ REVERSE ] expresin ..
expresin LOOP
estamentos
END LOOP;

FOR (integer for-loop)

Este formato de FOR crea un bucle que itera

sobre un rango de valores enteros. La variable
nombre es automticamente definida como de
tipo integer y existe slo dentro del bucle. Las
dos expresiones dando el menor y mayor
valores del rango son evaluadas unavez se
entra en el bucle. El intervalo de iteracin es de
1 normalmente, pero es -1 cuando se
especifica REVERSE.

FOR (integer for-loop)

Algunos ejemplos:

FOR i IN 1..10 LOOP

-- algunas expresiones aqu

RAISE NOTICE ''i is %'',i;

END LOOP;

FOR i IN REVERSE 10..1 LOOP

-- algunas expresiones aqu

END LOOP;

FOR (integer for-loop)

Ejemplo
funciones=# CREATE FUNCTION sumadora()
RETURNS integer AS ' DECLARE suma
INTEGER:=0; cuenta INTEGER; BEGIN FOR
cuenta IN 1..10 LOOP suma:=suma+1; END
LOOP; RETURN suma; END; ' LANGUAGE
'plpgsql';
CREATE FUNCTION

FOR (integer for-loop)

funciones=# select sumadora();

sumadora
---------10
(1 fila)

Errores y Mensajes

Errores y Mensajes
Use el estamento RAISE para retornar
mensajes y lanzar errores.
RAISE level 'format' [, variable [...]];

Errores y Mensajes

Los posibles niveles son DEBUG (escribe el mensaje

en el registro del servidor), LOG (escribe el mensaje
en el servidor con una prioridad alta), INFO, NOTICE y
WARNING (escribe el mensaje en el registro del
servidor y lo enva al cliente, con prioridad alta en
ambos envos), y EXCEPTION (lanza un error y aborta
la actual transaccin). Si los mensajes de error de una
determinada prioridad son reportados al cliente,
escritos en el registro del servidor, o ambos es es
controlado por las variables de configuracin
LOG_MIN_MESSAGES y CLIENT_MIN_MESSAGES.
Vea la Gua del Adminitrador de PostgreSQL para ms
informacin.

Errores y Mensajes

Dentro del formato de la cadena, % es

reemplazado por el siguiente argumento
opcional de la representacin externa. Escriba
%% para emitir un literal %. Advierta que los
argumentos opcionales deben ser variables
simples, no expresiones, y el formato debe ser
un simple literal.

Errores y Mensajes

Ejemplos:
RAISE NOTICE ''Calling
cs_create_job(%)'',v_job_id;
En ste ejemplo, el valor de v_job_id
reemplazar el % en la cadena.
RAISE EXCEPTION ''Inexistent ID -->
%'',user_id;
Esto abortar la transaccin con el mensaje de
error dado.

Excepciones

PostgreSQL no tiene un modelo de manejo de

excepciones demasiado elegante. Cuando el
intrprete, planificador/optimizador o ejecutor
decide que el estamento no puede ser
procesado, toda la transaccin es abortada y el
sistema salta al bucle principal para obtener la
siguiente consultad desde la aplicacin cliente.

Excepciones

Es posible entrar en el mecanismo de error para

averiguar qu es lo que est pasando. Pero
actualmente es imposible decir qu es lo que
realmente caus la cancelacin (error de
conversin de entrada/salida, error de coma
flotante, error de sintaxis). Y es posible que el
motor de base de datos est en un estado
inconsistente en ste punto, as que podra
retornar al ejecutor superior o la entrega de ms
comandos podra corromper toda la base de
datos.

Excepciones

As, la nica cosa que hace PL/pgSQL

actualmente cuando se encuentra con una
cancelacin durante la ejecucin de una
funcin o trigger es escribir adicionales
mensajes de nivel NOTICE, indicando en qu
funcin y cundo (nmero de lnea y tipo de
estamento) ocurri. El error siempre para la
ejecucin de la funcin.

Ejemplo de Exception

funciones=# CREATE FUNCTION

nopuedehaber() RETURNS text AS ' DECLARE
registro RECORD; BEGIN SELECT INTO
registro COUNT(*) AS NumerodeRegistros
FROM prueba1; IF [Link]
< 5 THEN RAISE EXCEPTION ''Todo Bien'';
ELSE RAISE EXCEPTION ''No pueden existir
ms de 5 registros en la tabla Prueba1'';
RETURN NULL; END IF; END; ' LANGUAGE
'plpgsql';
CREATE FUNCTION

Ejemplo de Exception

funciones=# select nopuedehaber();

ERROR: Todo Bien

Ejemplos

Aqu tiene unas cuantas funciones para

demostrar lo sencillo que es escribir funciones
PL/pgSQL. Para ejemplos ms complejos el
programador podra echar un vistazo a los tests
de regresin para PL/pgSQL.

Ejemplos

Un detalle importante en la escritura de funciones

PL/pgSQL es el manejo de las comillas simples. El
cdigo fuente de la funcin en CREATE FUNCTION
debe ser un literal de cadena. Las comillas simples
dentro de literales cadena deben ser dobles o
antecedidas por una barra invertida (backslash).
Todava seguimos buscando una alternativa elegante.
Por ahora, doblar las comillas simples como en los
ejemplos es lo que debera usar. Cualquier solucin
para futuras versiones de PostgreSQL ser
compatible.

Ejemplos

Una simple funcin PL/pgSQL para concatenar

texto.
Esta funcin recibe dos parmetros de texto y
retorna el resultado de su unin.
CREATE FUNCTION concat_text (TEXT, TEXT)
RETURNS TEXT AS '
BEGIN
RETURN $1 || $2;

END;

' LANGUAGE 'plpgsql';

Ejemplos

Una simple funcin PL/pgSQL para incrementar

un entero.
Esta funcin recibe un entero y lo incrementa
en uno, retornando el valor incrementado.
CREATE FUNCTION add_one (integer)
RETURNS INTEGER AS '
BEGIN
RETURN $1 + 1;

END;

' LANGUAGE 'plpgsql';

Una funcin PL/pgSQL para

composicin de texto.

En ste ejemplo, tomamos EMP (una tabla) y

un entero com argumentos de nuestra funcin,
la cual retorna un booleano. Si el campo salary
de la tabla EMP es NULL, nosotros retornamos
f. De lo contrario comparamos dicho campo
con el entero pasado a la funcin y retornamos
el resultado booleano de la comparacin (t o f).

Una funcin PL/pgSQL para

composicin de texto.

CREATE FUNCTION c_overpaid (EMP, INTEGER) RETURNS

BOOLEAN AS '
DECLARE

emprec ALIAS FOR $1;

sallim ALIAS FOR $2;

BEGIN
IF [Link] ISNULL THEN
RETURN ''f'';

END IF;

RETURN [Link] > sallim;

END;

' LANGUAGE 'plpgsql';