PDF de programación - Funciones y Triggers Introducción, Ejemplos

Funciones y Triggers
Introducción, Ejemplos

Álvaro Herrera

13 de noviembre de 2004

Álvaro Herrera

Funciones y TriggersIntroducción, Ejemplos

Contenidos

I Funciones: ¿para qué?
I Lenguajes
I Tipos de funciones
I Creación de funciones

I tradicionales
I SRFs

I Funciones para triggers
I Indices Funcionales

Álvaro Herrera

Funciones y TriggersIntroducción, Ejemplos

¿Para qué quiero funciones?

I Mantener lógica lejos de la aplicación

I consistencia entre aplicaciones
I reducción de funcionalidad duplicada

I Acceso predefinido a objetos restringidos
I Escoger herramienta adecuada a cada caso
I Algunas cosas necesitan lenguaje procedural

I SQL es declarativo
I se necesita poder expresivo distinto

Álvaro Herrera

Funciones y TriggersIntroducción, Ejemplos

Los lenguajes

I PostgreSQL soporta múltiples lenguajes procedurales

I saber usar el apropiado en cada caso
I conocer debilidades, fortalezas de cada uno

I Hackers:

I agregar soporte a nuevos lenguajes

I Lenguajes confiables (trusted)

I se puede “desconfiar” del usuario

Álvaro Herrera

Funciones y TriggersIntroducción, Ejemplos

Lenguajes: SQL

I Lenguaje más básico y simple
I No otorga poder adicional
I Permite simplificar consultas

Álvaro Herrera

Funciones y TriggersIntroducción, Ejemplos

Lenguajes: PL/pgSQL

I PL por excelencia
I Procedural

I Sintaxis para loops, condicionales, etc

I Fácil de usar

I portar desde Oracle PL/SQL
I sintaxis simple y apropiada
I No particularmente veloz ···

I cache de planes de ejecución

Álvaro Herrera

Funciones y TriggersIntroducción, Ejemplos

Lenguajes: C

I Muy potente, flexible
I Excelente rendimiento
I Tiene acceso a todo

I ejecutar consultas, escribir archivos, etc
I incluso a botar el proceso servidor

I Obtuso, pesado, complicado, peligroso

I usarlo sólo en caso de necesidad!

I No es confiable (trusted)

Álvaro Herrera

Funciones y TriggersIntroducción, Ejemplos

Lenguajes: scripting

I Tcl
I Perl
I Python
I PHP
I otros

Álvaro Herrera

Funciones y TriggersIntroducción, Ejemplos

Lenguajes: PL/R

I Mención especial

I brillantemente mantenido

I Propósito específico
I manejo estadístico

Álvaro Herrera

Funciones y TriggersIntroducción, Ejemplos

Lenguajes: conclusiones

I Experimentar, usar, familiarizarse
I Puede alivianar el trabajo ···

I si se usa la herramienta adecuada

I Puede mejorar rendimiento

“premature optimization is the root of all evil”

(Donald Knuth)

Álvaro Herrera

Funciones y TriggersIntroducción, Ejemplos

Tipos de funciones

I Según lo que retornan

I Funciones “normales”
I SRF (set-returning function)
I Agregación

I Especiales
I trigger
I language_handler

I Privilegios durante invocación

I security definer
I security invoker

Álvaro Herrera

Funciones y TriggersIntroducción, Ejemplos

Funciones Tradicionales

I Retornan un solo resultado

I escalar o tupla

I Es posible retornar un cursor abierto
I Pueden tener efectos secundarios

I tabla temporal

Álvaro Herrera

Funciones y TriggersIntroducción, Ejemplos

Sintaxis de Creación de Funciones

CREATE [OR REPLACE] FUNCTION
([tipo argumento], ···)
RETURNS [tipo resultado]

{ LANGUAGE lenguaje

| IMMUTABLE | STABLE | VOLATILE
| CALLED ON NULL INPUT

| RETURNS NULL ON NULL INPUT
| STRICT

| [EXTERNAL] SECURITY INVOKER

| [EXTERNAL] SECURITY DEFINER

| AS ’definición’
| AS ’archivo’, ’símbolo’

} ···

Álvaro Herrera

Funciones y TriggersIntroducción, Ejemplos

Creación de Funciones: atributos

I IMMUTABLE

I retorna lo mismo con los mismos argumentos
I no hace búsquedas en la BD!

I STABLE

I como immutable, pero puede cambiar con el estado de la BD

I VOLATILE

I puede cambiar de una llamada a otra
I si tiene efectos secundarios

Álvaro Herrera

Funciones y TriggersIntroducción, Ejemplos

Creación de Funciones: atributos (2)

I STRICT
I RETURNS NULL ON NULL INPUT

I si algún argumento es NULL, no es necesario llamarla

I CALLED ON NULL INPUT

I debe ser invocada

Álvaro Herrera

Funciones y TriggersIntroducción, Ejemplos

Ejemplo 1: Una fila, SQL

CREATE FUNCTION suma(INTEGER, INTEGER)

RETURNS INTEGER
IMMUTABLE STRICT LANGUAGE SQL
AS ’SELECT $1 + $2’;

regression=> select suma(10, 14);

suma

------

24

(1 row)
regression=> select * FROM suma(10, 14);

suma

------

24

(1 row)

Álvaro Herrera

Funciones y TriggersIntroducción, Ejemplos

Ejemplo 1: Una fila, SQL (cont.)

CREATE OR REPLACE FUNCTION suma(INT, INT)

RETURNS INTEGER LANGUAGE SQL
CALLED ON NULL INPUT
AS ’

SELECT CASE

WHEN $1 IS NULL THEN

CASE

WHEN $2 IS NULL THEN NULL
ELSE $2

END

WHEN $2 IS NULL THEN $1
ELSE $1 + $2

END’

IMMUTABLE;

Álvaro Herrera

Funciones y TriggersIntroducción, Ejemplos

Ejemplo 1a, PL/pgSQL

CREATE OR REPLACE FUNCTION suma_null(INT, INT, INT)

RETURNS INTEGER CALLED ON NULL INPUT
LANGUAGE plpgsql AS ’

DECLARE

acum INTEGER;

BEGIN

acum := 0;
IF $1 IS NOT NULL THEN
acum := acum + $1;

END IF;
IF $2 IS NOT NULL THEN
acum := acum + $2;

END IF;
IF $3 IS NOT NULL THEN
acum := acum + $3;

END IF;
RETURN acum;

END’;

Álvaro Herrera

Funciones y TriggersIntroducción, Ejemplos

Ejemplo 1b: Un operador

CREATE OPERATOR @+

(PROCEDURE = suma,

LEFTARG = INTEGER,
RIGHTARG = INTEGER);

SELECT 1 @+ 2 @+ NULL @+ 4;

?column?

----------

7

(1 fila)

Álvaro Herrera

Funciones y TriggersIntroducción, Ejemplos

Ejemplo 2: Una fila, PL/pgSQL

Números a letras

CREATE OR REPLACE FUNCTION test_plpgsql(INT)

RETURNS TEXT LANGUAGE plpgsql STRICT
IMMUTABLE AS ’

DECLARE

num ALIAS FOR $1;
ret TEXT;

BEGIN

IF num = 1 THEN

ret := ’’uno’’;

ELSIF num = 2 THEN

ret := ’’dos’’;

END IF;
RETURN ret;

END ’;

Álvaro Herrera

Funciones y TriggersIntroducción, Ejemplos

Ejemplo 3: Una fila, PL/perl

CREATE OR REPLACE FUNCTION num2pal(INTEGER)

RETURNS TEXT AS ’

$num = shift;
return undef if ($num < 1 || $num > 9);
$num--;
return (qw(uno dos tres cuatro cinco seis

siete ocho nueve))[$num];

’ LANGUAGE plperl;
regression=> select num2pal(5);

num2pal

---------

cinco

(1 fila)

Álvaro Herrera

Funciones y TriggersIntroducción, Ejemplos

Ejemplo 4: Una fila, C

#include "postgres.h"
#include "fmgr.h"
PG_FUNCTION_INFO_V1(digitoVer);
Datum digitoVer(PG_FUNCTION_ARGS) {

int rut = PG_GETARG_INT32(0);
text *ret;
int M=0, S=1;
for (; rut; rut = rut / 10)

S = (S + rut % 10 * (9 - M++ % 6)) % 11;

ret = (text *) palloc(VARHDRSZ + 1);
VARATT_SIZEP(ret) = 5;
sprintf(VARDATA(ret), "%c", S ? S + ’0’ - 1 : ’K’);
PG_RETURN_TEXT_P(ret);

}

Álvaro Herrera

Funciones y TriggersIntroducción, Ejemplos

Ejemplo 4: Una fila, C (cont.)

gcc -Wall -O2 -c -fpic

-I‘pg_config --includedir‘
-I‘pg_config --includedir‘/server
pglib.c

gcc -shared pglib.o -o pglib.so
CREATE OR REPLACE FUNCTION

digito_verificador(INTEGER)
RETURNS TEXT
STRICT IMMUTABLE
AS ’/usr/local/lib/pgsql/pglib.so’,
’digitoVer’ LANGUAGE C;

Álvaro Herrera

Funciones y TriggersIntroducción, Ejemplos

Ejemplo 4: Una fila, C (cont.)

#include "postgres.h"
#include "fmgr.h"
PG_FUNCTION_INFO_V1(digitoVer);
Datum digitoVer(PG_FUNCTION_ARGS) {

int rut = PG_GETARG_INT32(0);
text *ret;
int M=0, S=1;
for (; rut; rut = rut / 10)

S = (S + rut % 10 * (9 - M++ % 6)) % 11;

ret = (text *) palloc(VARHDRSZ + 1);
VARATT_SIZEP(ret) = VARHDRSZ + 1;
sprintf(VARDATA(ret), "%c", S ? S+’0’-1 : ’K’);
PG_RETURN_TEXT_P(ret);

}

Álvaro Herrera

Funciones y TriggersIntroducción, Ejemplos

Ejemplo 4: para que?

CREATE TABLE clientes (

···
rut INTEGER,
dv TEXT CHECK (dv = digito_verificador(rut)),
···

);

Álvaro Herrera

Funciones y TriggersIntroducción, Ejemplos

Set-returning Functions (SRF)

I Funciones que “retornan tablas”
I En cláusula FROM

I hacer JOIN

I con otras tablas
I otras funciones
I subconsultas

I Definir tipo de retorno

Álvaro Herrera

Funciones y TriggersIntroducción, Ejemplos

SRF: Ejemplos de uso

SELECT * FROM

SELECT * FROM

una_srf(arg1, arg2),
otra_srf(arg1, arg2)
WHERE una_srf.uno = otra_srf.uno
una_srf(arg1, ···)
JOIN otra_srf( ··· ) USING (columna)
JOIN una_tabla ON (condicion ···)

Álvaro Herrera

Funciones y TriggersIntroducción, Ejemplos

SRF: Ejemplos de uso (cont.)

SELECT * FROM

una_tabla,
una_srf(···),
(SELECT tres, cuatro

FROM otra_srf(···)
WHERE ···)

WHERE ···

Álvaro Herrera

Funciones y TriggersIntroducción, Ejemplos

SRF: Tipos de retorno

I Lo especial de una SRF
I RETURNS SETOF foo
I foo puede ser

I un tipo
I una tabla
I una definición anónima

Álvaro Herrera

Funciones y TriggersIntroducción, Ejemplos

SRF: Sintaxis para retorno

CREATE FUNCTION una_srf (···)
SELECT * FROM una_srf (foo, bar, ···);

RETURNS SETOF tabla ···;

CREATE TYPE foo (a int, b int);
CREATE FUNCTION otra_srf (···)
RETURNS SETOF foo ···;
SELECT * FROM otra_srf(···);
CREATE FUNCTION tercera_srf(···)
RETURNS SETOF RECORD ···;
SELECT * FROM tercera_srf(···)

AS tercera(definicion de tipo);

Álvaro Herrera

Funciones y TriggersIntroducción, Ejemplos

SRF en plgpsql: sintaxis

I RETURN NEXT foo

I acumula la tupla foo para retornar

I RETURN termina la función

I y devuelve todas las tuplas retornadas

FOR SELECT ··· LOOP
···
RETURN NEXT ···

END LOOP;
RETURN;

Álvaro Herrera

Funciones y TriggersIntroducción, Ejemplos

Ejemplo 5: SRF en plpgsql

I Todo el mes en intervalos de 30 minutos

DECLARE

inicio
valor
final
paso
ret

ALIAS FOR $1;
TIMESTAMP WITH TIME ZONE;
ALIAS FOR $2;
ALIAS FOR $3;
RECORD;

Álvaro Herrera

Funciones y TriggersIntroducción, Ejemplos

Ejemplo 5: SRF en plpgsql (cont.)

BEGIN
valor := inicio;
LOOP

IF valor >= final THEN

RETURN;

END IF;
SELECT INTO ret valor, valor + paso;
RETURN NEXT ret;
valor := (valor + paso);

END LOOP;
END;

Álvaro Herrera

Funciones y TriggersIntroducción, Ejemplos

Ejemplo 5: SRF en plpgsql (cont.)

SELECT * FROM intervalos

(’2003-01-01’, ’2003-01-02’, ’3 hour’)
AS foo(inicio TIMESTAMP WITH TIME ZONE,

fin TIMESTAMP WITH TIME ZONE);

inicio
2003-01-01 00:00:00-03
2003-01-01 03:00:00-03
2003-01-01 06:00:00-03
2003-01-01 09:00:00-03
2003-01-01 12:00:00-03
2003-01-01 15:00:00-03
2003-01-01 18:00:00-03
2003-01-01 21:00:00-03

fin
2003-01-01 03:00:00-03
2003