PDF de programación - Set-Returning Functions

Introducción
Retorno
PL/pgSQL
SQL

Set-Returning Functions

Álvaro Herrera

13 de noviembre de 2004

Álvaro Herrera

Set-Returning Functions

Introducción
Retorno
PL/pgSQL
SQL

Qué son
Ejemplo: JOIN con una tabla
Ejemplo: JOIN con una subconsulta

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

Set-Returning Functions

Introducción
Retorno
PL/pgSQL
SQL

Qué son
Ejemplo: JOIN con una tabla
Ejemplo: JOIN con una subconsulta

Ejemplo 1

SELECT * FROM

una_srf(arg1, ···)
JOIN otra_srf( ··· ) USING (columna)
JOIN una_tabla ON (condicion ···)

Álvaro Herrera

Set-Returning Functions

Introducción
Retorno
PL/pgSQL
SQL

Qué son
Ejemplo: JOIN con una tabla
Ejemplo: JOIN con una subconsulta

Ejemplo 2

SELECT * FROM

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

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

WHERE ···

Álvaro Herrera

Set-Returning Functions

Introducción
Retorno
PL/pgSQL
SQL

Tipos de Retorno
Sintaxis para Retorno

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

Set-Returning Functions

Introducción
Retorno
PL/pgSQL
SQL

Tipos de Retorno
Sintaxis para Retorno

Sintaxis para retorno

1. CREATE FUNCTION una_srf (···)

RETURNS SETOF tabla ···;

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

2. CREATE TYPE foo (a int, b int);

CREATE FUNCTION otra_srf (···)
SELECT * FROM otra_srf(···);

RETURNS SETOF foo ···;

Álvaro Herrera

Set-Returning Functions

Introducción
Retorno
PL/pgSQL
SQL

Tipos de Retorno
Sintaxis para Retorno

Sintaxis para retorno (cont.)

3. CREATE FUNCTION tercera_srf(···)

RETURNS SETOF RECORD ···;
SELECT * FROM tercera_srf(···)

AS tercera(definicion de tipo);

Álvaro Herrera

Set-Returning Functions

Introducción
Retorno
PL/pgSQL
SQL

Sintaxis
Ejemplo

PL/pgSQL: sintaxis

RETURN NEXT foo acumula la tupla foo para retornar

RETURN termina la función y devuelve todas las tuplas

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

END LOOP;
RETURN;

Álvaro Herrera

Set-Returning Functions

Introducción
Retorno
PL/pgSQL
SQL

Sintaxis
Ejemplo

Dividir un intervalo en trozos

DECLARE

inicio
valor
final
paso
ret

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

Álvaro Herrera

Set-Returning Functions

Introducción
Retorno
PL/pgSQL
SQL

Sintaxis
Ejemplo

Dividir un intervalo en trozos (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

Set-Returning Functions

Introducción
Retorno
PL/pgSQL
SQL

Sintaxis
Ejemplo

Dividir un intervalo en trozos

SELECT * FROM intervalos

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

fin TIMESTAMP WITH TIME ZONE);

inicio

fin

2003-01-01 00:00:00-03
2003-01-01 03:00:00-03
2003-01-01 06: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 21:00:00-03

2003-01-02 00:00:00-03

Álvaro Herrera

Set-Returning Functions

Introducción
Retorno
PL/pgSQL
SQL

SRF en SQL

CREATE FUNCTION getfoo(int)

RETURNS setof foo AS ’

SELECT * FROM foo
WHERE fooid = $1;’

LANGUAGE SQL;

SELECT * FROM getfoo(1);

Álvaro Herrera

Set-Returning Functions