View
35.444
Download
5
Category
Preview:
DESCRIPTION
Manejo básico de funciones en PostgreSQL
Citation preview
GoBack
http://www.postgresql.org PostgreSQL Módulo 1 – Slide 1
PostgreSQL Módulo 1 - Funciones
Rodrigo Soliz Rocabado (rodrifer@gmail.com)
June 14, 2007
Funciones
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 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
WHILE
FOR
Trabajando con tablas
Variables compuestas
SELECT INTO
Bucles en tablas
http://www.postgresql.org PostgreSQL Módulo 1 – Slide 3
PostgreSQL viene con muchas funciones incorporadas, podemos verlas todas dentro delpsql con el comando \df o examinando la tabla del sistema pg_procSELECT * FROM pg_proc;
Pero nosotros podemos declarar nuestras propias funciones de acuerdo a lasnecesidades de nuestra base de datos.
Funciones en PL/pgsql
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 PostgreSQL Módulo 1 – Slide 4
Uno de los lenguajes soportados para programar funciones en PostgreSQL es elPL/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 autilizar:createlang plpgsql -d nombrebase -U nombreusuarioo si ya estamos conectados a la base de datos:CREATE LANGUAGE plpgsql;Nota : En la instalación de PostgreSQL para Windows el lenguaje procedural Pl/pgsql estainstalado desde el principio, en distribuciones GNU/Linux no.
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 PostgreSQL Módulo 1 – Slide 5
CREATE FUNCTION nombrefuncion(param, param)RETURNS tiporetornoAS $$DECLAREvariable;variable;BEGINsentencia; -- esto es un comentariosentencia; /* esto es un bloque de comentario */sentencia;RETURN retorno;END;$$Language 'plpgsql';
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 PostgreSQL Módulo 1 – Slide 6
CREATE FUNCTION suma (int4, int4)RETURNS int4AS $$DECLAREa int4;b int4;res int4;BEGINa := $1;b := $2;res := a + b;RETURN res;END;$$Language 'plpgsql';
Invocando a 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 PostgreSQL Módulo 1 – Slide 7
Como es que se invoca a una función?
Dentro una sentencia SELECT:SELECT suma(3, 4);suma------7
Variables
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 PostgreSQL Módulo 1 – Slide 8
Como se asigna un valor a una variable?nombrevariable := valor ;Ejemplo:aux := 1 ;
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.
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
http://www.postgresql.org PostgreSQL Módulo 1 – Slide 9
CREATE FUNCTION fib(int4)RETURNS int4AS $$DECLAREa int4;res int4 := 0;BEGINa := $1;IF ( a = 1 OR a = 2) THENres := 1;ELSEres := fib(a - 2) + fib(a - 1);END IF;RETURN res;END;$$Language 'plpgsql';Asi es, tenemos la posibilidad de utilizar funciones recursivas.
Probémoslo
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 PostgreSQL Módulo 1 – Slide 10
Hagamos la prueba:SELECT fib(10);fib------55
Recomendación : dada la definición de nuestra función fib , no es recomendable calcularnúmeros muy elevados, si es que no queremos que nuestro servidor se colapse ;-)
Estructuras de control
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 PostgreSQL Módulo 1 – Slide 11
Podemos usar las estructuras de control más comunes disponibles en otros lenguajes deprogramación:
IF - THEN - ELSEFOR
WHILE
Adicionalmente tenemos el LOOP (ver documentación), pero no hace nada que lasanteriores no puedan lograr.
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 PostgreSQL Módulo 1 – Slide 12
CREATE FUNCTION espositivo (int4)RETURNS booleanAS $$DECLAREa int4;res boolean := false;BEGINa := $1;IF (a > 0) THENres := true;ELSEIF (a = 0) THENres := true;END IF;END IF;RETURN res;END;$$Language 'plpgsql';
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 PostgreSQL Módulo 1 – Slide 13
CREATE FUNCTION adicion()RETURNS int4AS $$DECLAREcont int4;res int4;BEGINcont := 1;res := 0;WHILE (cont <= 10)LOOPres := res + cont;cont := cont + 1;END LOOP;RETURN res;END;$$Language 'plpgsql';
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 PostgreSQL Módulo 1 – Slide 14
CREATE FUNCTION adicion2()RETURNS int4AS $$DECLAREcont int4;res int4;BEGINres := 0;FOR cont IN 1 .. 10LOOPres := res + cont;END LOOP;RETURN res;END;$$Language 'plpgsql';
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 PostgreSQL Módulo 1 – Slide 15
Pero lo que nos interesa es trabajar con los datos de una tabla. Entonces veamos unejemplo más práctico.Supongamos que tenemos una tabla ítem que almacena la información de piezas dehardware 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));
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 PostgreSQL Módulo 1 – Slide 16
Queremos que haya un función comprar_item() que haga lo siguiente:
1. Cree una tabla nueva llamada item_por_comprar que almacene los datos de lositems que tengan cantidad = 0 , o sea los items que hay que renovar para luegoponer a la venta.
2. Busque en la tabla item todos los items que ya no estén en stock y los inserte enla tabla item_por_comprar
3. Nos devuelva la cantidad de items que ya no se encuentran en stock
Primeros pasos
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 PostgreSQL Módulo 1 – Slide 17
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));
Esta tabla contiene información que solo es importante momentáneamente, no nosinteresa almacenarla permanentemente en la base de datos, por lo que lareemplazaremos cada vez que ejecutemos la función.
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
http://www.postgresql.org PostgreSQL Módulo 1 – Slide 18
Empezamos a definir la función:CREATE OR REPLACE FUNCTION comprar_item()RETURNS int4AS $$DECLAREcont_item int4;fila_item item%ROWTYPE;BEGIN...END;$$Language 'plpgsql';Declaramos dos variables, cont_item que tendrá el número de items que tengancantidad = 0 y fila_item que tendrá como campos, los atributos de la tabla item .
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
FOR
Trabajando con tablas
Variables compuestas
SELECT INTO
Bucles en tablas
http://www.postgresql.org PostgreSQL Módulo 1 – Slide 19
Podemos declarar variables compuestas que puedan almacenar los campos de unadeterminada fila de una determinada tabla.fila_item item%ROWTYPE ;
Indica que la variable fila_item podrá almacenar los campos de cualquier fila de la tablaitem.Para acceder a los valores de la variable fila_item usamos una notación ya conocida:fila_item.nombrefila_item.cantidadfila_item.precio_compra
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
http://www.postgresql.org PostgreSQL Módulo 1 – Slide 20
CREATE OR REPLACE FUNCTION comprar_item()RETURNS int4AS $$DECLAREcont_item int4;fila_item item%ROWTYPE;BEGINDROP TABLE item_por_comprar;CREATE TABLE item_por_comprar (item_id int4 NOT NULL,nombre varchar(150));END;$$Language 'plpgsql';Cada vez que vayamos a ejecutar la función borraremos la anterior tabla y crearemos unanueva con datos actualizados. No nos interesa almacenar la información que se generacada vez que ejecutamos la función, solo la información más reciente.
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
http://www.postgresql.org PostgreSQL Módulo 1 – Slide 21
CREATE OR REPLACE FUNCTION comprar_item()RETURNS int4AS $$DECLAREcont_item int4;fila_item item%ROWTYPE;BEGINDROP 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
SELECT INTO
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 PostgreSQL Módulo 1 – Slide 22
Podemos asignar un valor a una variable directamente dentro de una consulta SQLSELECT COUNT(*) INTO cont_item FROM item WHERE cantidad = 0;cont_item es una variable tipo int4 y la consulta también devuelve un tipo compatible, porlo tanto en cont_item tendremos un valor correcto.
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
http://www.postgresql.org PostgreSQL Módulo 1 – Slide 23
CREATE OR REPLACE FUNCTION comprar_item()RETURNS int4AS $$DECLAREcont_item int4;fila_item item%ROWTYPE;BEGINDROP 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 = 0LOOPINSERT INTO item_por_comprarVALUES (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 ypor cada una insertamos una fila en la tabla item_por_comprar
Bucles en 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 PostgreSQL Módulo 1 – Slide 24
Vimos que podiamos definir un bucle con la sentencia FOR de esta forma:FOR variable IN rango
Si trabajamos con variables compuestas y tablas, podemos colocar en variable unavariable compuesta y en rango una tabla cualquiera (o un subconjunto), la variablecompuesta recorrerá toda la tabla tomando los valores de cada una de las filas, si tienenlos mismos atributos por supuesto.FOR fila_item IN SELECT * FROM item WHERE cantidad = 0;
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
http://www.postgresql.org PostgreSQL Módulo 1 – Slide 25
CREATE OR REPLACE FUNCTION comprar_item()RETURNS int4AS $$DECLAREcont_item int4;fila_item item%ROWTYPE;BEGINDROP 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 = 0LOOPINSERT INTO item_por_comprarVALUES (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.
Probando 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
http://www.postgresql.org PostgreSQL Módulo 1 – Slide 26
Probemos insertando algunas filas en la tabla item: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);
Probando 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
http://www.postgresql.org PostgreSQL Módulo 1 – Slide 27
Ejecutemos la función:SELECT comprar_item();
Veámos el contenido de la tabla item_por_comprar :SELECT * FROM item_por_comprar;
Una variante de la funcion 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
http://www.postgresql.org PostgreSQL Módulo 1 – Slide 28
Utilicemos la misma tabla item y hagamos una función patrimonio() que nos retorne:
� El valor total de todos los items disponibles en la tienda.
o sea :
cantidad x precio_venta (de cada item)
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
http://www.postgresql.org PostgreSQL Módulo 1 – Slide 29
CREATE OR REPLACE FUNCTION patrimonio()RETURNS numeric(7,2)AS $$DECLAREtotal numeric(7,2);fila_item item%ROWTYPE;BEGINtotal := 0.0;FOR fila_item IN SELECT * FROM item WHERE cantidad != 0LOOPtotal := 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 .
Recommended