Funciones En Postgresql

GoBack

PostgreSQL Módulo 1 - Funciones

Rodrigo Soliz Rocabado ([email protected]) June 14, 2007

http://www.postgresql.org

PostgreSQL Módulo 1 – Slide 1

Funciones Funciones en PostgreSQL Funciones en PL/pgsql Partes de una función Invocando a una función Variables Estructuras de control IF WHILE FOR

Funciones

Trabajando con tablas Variables compuestas SELECT INTO Bucles en tablas

http://www.postgresql.org

PostgreSQL Módulo 1 – Slide 2

Funciones en PostgreSQL

Funciones Funciones en PostgreSQL Funciones en PL/pgsql Partes de una función Invocando a una función Variables Estructuras de control IF

PostgreSQL viene con muchas funciones incorporadas, podemos verlas todas dentro del psql con el comando \df o examinando la tabla del sistema pg_proc SELECT * FROM pg_proc;

Pero nosotros podemos declarar nuestras propias funciones de acuerdo a las necesidades de nuestra base de datos.

WHILE FOR Trabajando con tablas Variables compuestas SELECT INTO Bucles en tablas

http://www.postgresql.org

PostgreSQL Módulo 1 – Slide 3

Funciones en PL/pgsql

Funciones Funciones en PostgreSQL Funciones en PL/pgsql Partes de una función Invocando a una función

Uno de los lenguajes soportados para programar funciones en PostgreSQL es el PL/pgsql, se desarrolló exclusivamente para él y es muy parecido al PL/SQL de Oracle. Para poder utilizarlo primero debemos instalarlo en la base de datos que vayamos a utilizar:

Variables Estructuras de control

createlang plpgsql -d nombrebase -U nombreusuario

IF WHILE

o si ya estamos conectados a la base de datos:

FOR Trabajando con tablas

CREATE LANGUAGE plpgsql;

Variables compuestas SELECT INTO Bucles en tablas

http://www.postgresql.org

Nota: En la instalación de PostgreSQL para Windows el lenguaje procedural Pl/pgsql esta instalado desde el principio, en distribuciones GNU/Linux no.

PostgreSQL Módulo 1 – Slide 4

Partes de una función

Funciones Funciones en PostgreSQL Funciones en PL/pgsql Partes de una función Invocando a una función Variables Estructuras de control IF WHILE FOR Trabajando con tablas Variables compuestas SELECT INTO Bucles en tablas

http://www.postgresql.org

CREATE FUNCTION nombrefuncion(param, param) RETURNS tiporetorno AS $$ DECLARE variable; variable; BEGIN sentencia; -- esto es un comentario sentencia; /* esto es un bloque de comentario */ sentencia; RETURN retorno; END; $$ Language 'plpgsql';

PostgreSQL Módulo 1 – Slide 5

Ejemplo

Funciones Funciones en PostgreSQL Funciones en PL/pgsql Partes de una función Invocando a una función Variables Estructuras de control IF WHILE FOR Trabajando con tablas Variables compuestas SELECT INTO Bucles en tablas

http://www.postgresql.org

CREATE FUNCTION suma (int4, int4) RETURNS int4 AS $$ DECLARE a int4; b int4; res int4; BEGIN a := $1; b := $2; res := a + b; RETURN res; END; $$ Language 'plpgsql';

PostgreSQL Módulo 1 – Slide 6

Invocando a una función

Funciones

Como es que se invoca a una función?

Funciones en PostgreSQL Funciones en PL/pgsql Partes de una función

Dentro una sentencia SELECT:

Invocando a una función Variables

SELECT suma(3, 4);

Estructuras de control IF WHILE FOR

suma -----7

Trabajando con tablas Variables compuestas SELECT INTO Bucles en tablas

http://www.postgresql.org

PostgreSQL Módulo 1 – Slide 7

Variables

Funciones

Como se asigna un valor a una variable?

Funciones en PostgreSQL Funciones en PL/pgsql

nombrevariable := valor ;

Partes de una función Invocando a una función Variables Estructuras de control

Ejemplo: aux := 1 ;

IF WHILE FOR Trabajando con tablas Variables compuestas SELECT INTO Bucles en tablas

Declarar una variable y asignarle un valor? nombrevariable tipovariable := valor ; Ejemplo: aux boolean := true ;

El uso de variables es idéntico a los lenguajes de programación que comunmente usamos.

http://www.postgresql.org

PostgreSQL Módulo 1 – Slide 8

Ejemplo (Recursión)

Funciones Funciones en PostgreSQL Funciones en PL/pgsql Partes de una función Invocando a una función Variables Estructuras de control IF WHILE FOR Trabajando con tablas Variables compuestas SELECT INTO Bucles en tablas

CREATE FUNCTION fib(int4) RETURNS int4 AS $$ DECLARE a int4; res int4 := 0; BEGIN a := $1; IF ( a = 1 OR a = 2) THEN res := 1; ELSE res := fib(a - 2) + fib(a - 1); END IF; RETURN res; END; $$ Language 'plpgsql';

Asi es, tenemos la posibilidad de utilizar funciones recursivas.

http://www.postgresql.org

PostgreSQL Módulo 1 – Slide 9

Probémoslo

Funciones

Hagamos la prueba:

Funciones en PostgreSQL Funciones en PL/pgsql

SELECT fib(10);

Partes de una función Invocando a una función Variables Estructuras de control

fib -----55

IF WHILE FOR

Recomendación: dada la definición de nuestra función fib, no es recomendable calcular números muy elevados, si es que no queremos que nuestro servidor se colapse ;-)

Trabajando con tablas Variables compuestas SELECT INTO Bucles en tablas

http://www.postgresql.org

PostgreSQL Módulo 1 – Slide 10

Estructuras de control

Funciones Funciones en PostgreSQL Funciones en PL/pgsql

Podemos usar las estructuras de control más comunes disponibles en otros lenguajes de programación:

Partes de una función Invocando a una función Variables Estructuras de control

IF - THEN - ELSE FOR WHILE

IF WHILE FOR Trabajando con tablas

Adicionalmente tenemos el LOOP (ver documentación), pero no hace nada que las anteriores no puedan lograr.

Variables compuestas SELECT INTO Bucles en tablas

http://www.postgresql.org

PostgreSQL Módulo 1 – Slide 11

IF

Funciones Funciones en PostgreSQL Funciones en PL/pgsql Partes de una función Invocando a una función Variables Estructuras de control IF WHILE FOR Trabajando con tablas Variables compuestas SELECT INTO Bucles en tablas

http://www.postgresql.org

CREATE FUNCTION espositivo (int4) RETURNS boolean AS $$ DECLARE a int4; res boolean := false; BEGIN a := $1; IF (a > 0) THEN res := true; ELSE IF (a = 0) THEN res := true; END IF; END IF; RETURN res; END; $$ Language 'plpgsql';

PostgreSQL Módulo 1 – Slide 12

WHILE

Funciones Funciones en PostgreSQL Funciones en PL/pgsql Partes de una función Invocando a una función Variables Estructuras de control IF WHILE FOR Trabajando con tablas Variables compuestas SELECT INTO Bucles en tablas

http://www.postgresql.org

CREATE FUNCTION adicion() RETURNS int4 AS $$ DECLARE cont int4; res int4; BEGIN cont := 1; res := 0; WHILE (cont <= 10) LOOP res := res + cont; cont := cont + 1; END LOOP; RETURN res; END; $$ Language 'plpgsql';

PostgreSQL Módulo 1 – Slide 13

FOR

Funciones Funciones en PostgreSQL Funciones en PL/pgsql Partes de una función Invocando a una función Variables Estructuras de control IF WHILE FOR Trabajando con tablas Variables compuestas SELECT INTO Bucles en tablas

http://www.postgresql.org

CREATE FUNCTION adicion2() RETURNS int4 AS $$ DECLARE cont int4; res int4; BEGIN res := 0; FOR cont IN 1 .. 10 LOOP res := res + cont; END LOOP; RETURN res; END; $$ Language 'plpgsql';

PostgreSQL Módulo 1 – Slide 14

Trabajando con tablas

Funciones Funciones en PostgreSQL Funciones en PL/pgsql Partes de una función Invocando a una función Variables Estructuras de control IF WHILE FOR Trabajando con tablas Variables compuestas SELECT INTO Bucles en tablas

http://www.postgresql.org

Pero lo que nos interesa es trabajar con los datos de una tabla. Entonces veamos un ejemplo más práctico. Supongamos que tenemos una tabla ítem que almacena la información de piezas de hardware vendidas por una tienda de computadoras. CREATE TABLE item ( item_id serial NOT NULL, nombre varchar(150) NOT NULL, tipo varchar(100) NOT NULL, cantidad int4 NOT NULL DEFAULT 0, precio_compra numeric(7,2) NOT NULL, precio_venta numeric(7,2) NOT NULL, CONSTRAINT item_id_pk PRIMARY KEY (item_id) );

PostgreSQL Módulo 1 – Slide 15

Trabajando con tablas

Funciones

Queremos que haya un función comprar_item() que haga lo siguiente:

Funciones en PostgreSQL Funciones en PL/pgsql Partes de una función Invocando a una función Variables

1. Cree una tabla nueva llamada item_por_comprar que almacene los datos de los items que tengan cantidad = 0, o sea los items que hay que renovar para luego poner a la venta.

Estructuras de control IF WHILE

2. Busque en la tabla item todos los items que ya no estén en stock y los inserte en la tabla item_por_comprar

FOR Trabajando con tablas Variables compuestas

3. Nos devuelva la cantidad de items que ya no se encuentran en stock

SELECT INTO Bucles en tablas

http://www.postgresql.org

PostgreSQL Módulo 1 – Slide 16

Primeros pasos

Funciones Funciones en PostgreSQL Funciones en PL/pgsql Partes de una función Invocando a una función Variables Estructuras de control

Primero creamos una tabla para almacenar los items que ya no hay disponibles en stock, la tabla item_por_comprar: CREATE TABLE item_por_comprar ( item_id int4 NOT NULL, nombre varchar(150) );

IF WHILE FOR Trabajando con tablas Variables compuestas

Esta tabla contiene información que solo es importante momentáneamente, no nos interesa almacenarla permanentemente en la base de datos, por lo que la reemplazaremos cada vez que ejecutemos la función.

SELECT INTO Bucles en tablas

http://www.postgresql.org

PostgreSQL Módulo 1 – Slide 17

La función

Funciones

Empezamos a definir la función:

Funciones en PostgreSQL Funciones en PL/pgsql Partes de una función Invocando a una función Variables Estructuras de control IF WHILE FOR Trabajando con tablas Variables compuestas SELECT INTO Bucles en tablas

http://www.postgresql.org

CREATE OR REPLACE FUNCTION comprar_item() RETURNS int4 AS $$ DECLARE cont_item int4; fila_item item%ROWTYPE; BEGIN ... END; $$ Language 'plpgsql';

Declaramos dos variables, cont_item que tendrá el número de items que tengan cantidad = 0 y fila_item que tendrá como campos, los atributos de la tabla item.

PostgreSQL Módulo 1 – Slide 18

Variables compuestas

Funciones Funciones en PostgreSQL Funciones en PL/pgsql Partes de una función Invocando a una función Variables Estructuras de control IF WHILE

Podemos declarar variables compuestas que puedan almacenar los campos de una determinada fila de una determinada tabla. fila_item item%ROWTYPE ;

Indica que la variable fila_item podrá almacenar los campos de cualquier fila de la tabla item. Para acceder a los valores de la variable fila_item usamos una notación ya conocida:

FOR Trabajando con tablas Variables compuestas SELECT INTO

fila_item.nombre fila_item.cantidad fila_item.precio_compra

Bucles en tablas

http://www.postgresql.org

PostgreSQL Módulo 1 – Slide 19

Continuamos con la función

Funciones Funciones en PostgreSQL Funciones en PL/pgsql Partes de una función Invocando a una función Variables Estructuras de control IF WHILE FOR Trabajando con tablas Variables compuestas SELECT INTO

CREATE OR REPLACE FUNCTION comprar_item() RETURNS int4 AS $$ DECLARE cont_item int4; fila_item item%ROWTYPE; BEGIN DROP TABLE item_por_comprar; CREATE TABLE item_por_comprar (item_id int4 NOT NULL, nombre varchar(150)); END; $$ Language 'plpgsql';

Bucles en tablas

Cada vez que vayamos a ejecutar la función borraremos la anterior tabla y crearemos una nueva con datos actualizados. No nos interesa almacenar la información que se genera cada vez que ejecutamos la función, solo la información más reciente.

http://www.postgresql.org

PostgreSQL Módulo 1 – Slide 20

Continuamos con la función

Funciones Funciones en PostgreSQL Funciones en PL/pgsql Partes de una función Invocando a una función Variables Estructuras de control IF WHILE FOR Trabajando con tablas Variables compuestas SELECT INTO Bucles en tablas

CREATE OR REPLACE FUNCTION comprar_item() RETURNS int4 AS $$ DECLARE cont_item int4; fila_item item%ROWTYPE; BEGIN DROP TABLE item_por_comprar; CREATE TABLE item_por_comprar (item_id int4 NOT NULL, nombre varchar(150)); SELECT COUNT(*) INTO cont_item FROM item WHERE cantidad = 0; END; $$ Language 'plpgsql';

Almacenamos en cont_item la cantidad de items que tienen cantidad = 0

http://www.postgresql.org

PostgreSQL Módulo 1 – Slide 21

SELECT INTO

Funciones

Podemos asignar un valor a una variable directamente dentro de una consulta SQL

Funciones en PostgreSQL Funciones en PL/pgsql

SELECT COUNT(*) INTO cont_item FROM item WHERE cantidad = 0;

Partes de una función Invocando a una función Variables Estructuras de control

cont_item es una variable tipo int4 y la consulta también devuelve un tipo compatible, por lo tanto en cont_item tendremos un valor correcto.

IF WHILE FOR Trabajando con tablas Variables compuestas SELECT INTO Bucles en tablas

http://www.postgresql.org

PostgreSQL Módulo 1 – Slide 22

Continuamos con la función

Funciones Funciones en PostgreSQL Funciones en PL/pgsql Partes de una función Invocando a una función Variables Estructuras de control IF WHILE FOR Trabajando con tablas Variables compuestas SELECT INTO Bucles en tablas

CREATE OR REPLACE FUNCTION comprar_item() RETURNS int4 AS $$ DECLARE cont_item int4; fila_item item%ROWTYPE; BEGIN DROP TABLE item_por_comprar; CREATE TABLE item_por_comprar (item_id int4 NOT NULL, nombre varchar(150)); SELECT COUNT(*) INTO cont_item FROM item WHERE cantidad = 0; FOR fila_item IN SELECT * FROM item WHERE cantidad = 0 LOOP INSERT INTO item_por_comprar VALUES (fila_item.item_id, fila_item.nombre); END LOOP; END; $$ Language 'plpgsql';

Definimos un bucle que recorra la tabla temporal que tiene los items con cantidad = 0 y por cada una insertamos una fila en la tabla item_por_comprar

http://www.postgresql.org

PostgreSQL Módulo 1 – Slide 23

Bucles en tablas

Funciones

Vimos que podiamos definir un bucle con la sentencia FOR de esta forma:

Funciones en PostgreSQL Funciones en PL/pgsql

FOR variable IN rango

Partes de una función Invocando a una función Variables Estructuras de control IF WHILE

Si trabajamos con variables compuestas y tablas, podemos colocar en variable una variable compuesta y en rango una tabla cualquiera (o un subconjunto), la variable compuesta recorrerá toda la tabla tomando los valores de cada una de las filas, si tienen los mismos atributos por supuesto.

FOR Trabajando con tablas

FOR fila_item IN SELECT * FROM item WHERE cantidad = 0;

Variables compuestas SELECT INTO Bucles en tablas

http://www.postgresql.org

PostgreSQL Módulo 1 – Slide 24

Continuamos con la función

Funciones Funciones en PostgreSQL Funciones en PL/pgsql Partes de una función Invocando a una función Variables Estructuras de control IF WHILE FOR Trabajando con tablas Variables compuestas SELECT INTO Bucles en tablas

CREATE OR REPLACE FUNCTION comprar_item() RETURNS int4 AS $$ DECLARE cont_item int4; fila_item item%ROWTYPE; BEGIN DROP TABLE item_por_comprar; CREATE TABLE item_por_comprar (item_id int4 NOT NULL, nombre varchar(150)); SELECT COUNT(*) INTO cont_item FROM item WHERE cantidad = 0; FOR fila_item IN SELECT * FROM item WHERE cantidad = 0 LOOP INSERT INTO item_por_comprar VALUES (fila_item.item_id, fila_item.nombre); END LOOP; RETURN cont_item; END; $$ Language 'plpgsql';

Finalmente retornamos cont_item para saber cuantos items han sido insertados.

http://www.postgresql.org

PostgreSQL Módulo 1 – Slide 25

Probando la función

Funciones

Probemos insertando algunas filas en la tabla item:

Funciones en PostgreSQL Funciones en PL/pgsql Partes de una función Invocando a una función Variables Estructuras de control IF WHILE FOR Trabajando con tablas Variables compuestas SELECT INTO

INSERT INTO item (nombre, tipo, cantidad, precio_compra, precio_venta) VALUES ('Switch Dlink 8 puertos', 'Switch', 2, 32.5, 45.6); INSERT INTO item (nombre, tipo, cantidad, precio_compra, precio_venta) VALUES ('Dlink 56K', 'Modem', 0, 10.5, 15.5); INSERT INTO item (nombre, tipo, cantidad, precio_compra, precio_venta) VALUES ('Samsung 17', 'Monitor', 0, 100.0, 120.0); INSERT INTO item (nombre, tipo, cantidad, precio_compra, precio_venta) VALUES ('DDR2 512/533', 'RAM', 15, 40.0, 45.0); INSERT INTO item (nombre, tipo, cantidad, precio_compra, precio_venta) VALUES ('NVIDIA GEFORCE FX 5200', 'Tarjeta de Video', 7, 40.0, 48.5); INSERT INTO item (nombre, tipo, cantidad, precio_compra, precio_venta) VALUES ('Pentium 4 3.2 GHZ 800/2MB', 'Procesador', 7, 200.0, 230.0);

Bucles en tablas

http://www.postgresql.org

PostgreSQL Módulo 1 – Slide 26

Probando la función

Funciones

Ejecutemos la función:

Funciones en PostgreSQL Funciones en PL/pgsql

SELECT comprar_item();

Partes de una función Invocando a una función

Veámos el contenido de la tabla item_por_comprar:

Variables Estructuras de control

SELECT * FROM item_por_comprar;

IF WHILE FOR Trabajando con tablas Variables compuestas SELECT INTO Bucles en tablas

http://www.postgresql.org

PostgreSQL Módulo 1 – Slide 27

Una variante de la funcion anterior

Funciones

Utilicemos la misma tabla item y hagamos una función patrimonio() que nos retorne:

Funciones en PostgreSQL Funciones en PL/pgsql Partes de una función Invocando a una función Variables

 El valor total de todos los items disponibles en la tienda.

o sea :

Estructuras de control IF

cantidad x precio_venta (de cada item)

WHILE FOR Trabajando con tablas Variables compuestas SELECT INTO Bucles en tablas

http://www.postgresql.org

PostgreSQL Módulo 1 – Slide 28

Una variante de la función anterior

Funciones Funciones en PostgreSQL Funciones en PL/pgsql Partes de una función Invocando a una función Variables Estructuras de control IF WHILE FOR Trabajando con tablas Variables compuestas SELECT INTO Bucles en tablas

CREATE OR REPLACE FUNCTION patrimonio() RETURNS numeric(7,2) AS $$ DECLARE total numeric(7,2); fila_item item%ROWTYPE; BEGIN total := 0.0; FOR fila_item IN SELECT * FROM item WHERE cantidad != 0 LOOP total := total + fila_item.cantidad * fila_item.precio_venta ; END LOOP; RETURN total; END; $$ Language 'plpgsql';

Veamos como el tipo de retorno de la funcion ha cambiado, ya no es int4 sino numeric.

http://www.postgresql.org

PostgreSQL Módulo 1 – Slide 29