Menu

CREATE FUNCTION y procedimientos almacenados en PostgreSQL

CREATE FUNCTION define una función que llamas dentro de las consultas, escrita en SQL o PL/pgSQL. CREATE PROCEDURE define una rutina que ejecutas con CALL y que puede hacer commit a medida que avanza.

Esta página incluye editores ejecutables: edita, ejecuta y ve el resultado al instante.

CREATE FUNCTION name(arg type) RETURNS type LANGUAGE sql AS $$ ... $$; define una función que las consultas pueden llamar como una integrada. El cuerpo va entre marcas $$, en SQL normal o en PL/pgSQL, el lenguaje procedimental de PostgreSQL. CREATE PROCEDURE (PostgreSQL 11+) define en cambio una rutina que ejecutas con CALL.

with_tax(50) usa la tasa por defecto y devuelve 60; with_tax(50, 0.1) devuelve 55. Los argumentos también se pueden pasar por nombre: with_tax(50, rate => 0.1).

Escribir lógica en PL/pgSQL

Usa LANGUAGE plpgsql cuando la función necesite variables, IF, bucles o manejo de errores. Las variables van en una sección DECLARE, y el código entre BEGIN y END:

Un paquete de 4 kg cuesta la base, 5; un paquete exprés de 20 kg cuesta (5 + 10 * 0.5) * 2 = 20. Dentro de PL/pgSQL, RAISE NOTICE 'value: %', cost; imprime un mensaje y RAISE EXCEPTION aborta con un error.

Cuando la lógica se puede expresar en SQL normal, prefiere LANGUAGE sql: el planificador puede integrar una función SQL sencilla en la consulta que la llama.

Devolver un conjunto de filas

RETURNS TABLE (...) crea una función que devuelve filas. Llámala en el FROM como una tabla:

orders_of('ana') devuelve los pedidos 1 y 3. RETURNS SETOF orders devuelve filas completas de la tabla orders en lugar de las columnas listadas.

Desde PostgreSQL 14, una función SQL puede tener un cuerpo SQL estándar en lugar de una cadena. PostgreSQL lo guarda ya analizado y registra las tablas y columnas que usa, así que no se pueden eliminar mientras exista la función:

CREATE FUNCTION order_count(who text) RETURNS bigint
LANGUAGE sql STABLE
BEGIN ATOMIC
  SELECT count(*) FROM orders WHERE customer = who;
END;

Crear un procedimiento almacenado y ejecutarlo con CALL

Un procedimiento no tiene tipo de retorno; lo ejecutas con CALL. Puede cambiar datos y, cuando el CALL no está dentro de un bloque de transacción explícito, puede hacer COMMIT a mitad de camino. Así se procesa un trabajo grande por lotes.

La cuenta 1 termina con 70 y la cuenta 2 con 80. Una transferencia de 200 rompería la restricción CHECK de la cuenta 1, y el error desharía las dos actualizaciones.

FunciónProcedimiento
Se ejecuta conSELECT f(...), en cualquier parte de una consultasolo CALL p(...)
Devuelveun valor, una fila o un conjunto de filasnada (o parámetros OUT/INOUT)
COMMIT / ROLLBACK dentronosí, en PL/pgSQL, cuando no se llama dentro de un bloque de transacción
DesdesiemprePostgreSQL 11

Indicar la volatilidad: IMMUTABLE, STABLE, VOLATILE

La etiqueta de volatilidad le dice al planificador qué puede suponer sobre una función. El valor por defecto es VOLATILE.

  • IMMUTABLE: el resultado depende solo de los argumentos y nunca cambia (with_tax). Es obligatorio para funciones usadas en la expresión de un índice.
  • STABLE: el resultado puede cambiar entre sentencias pero no dentro de una, por ejemplo una función que lee tablas (orders_of).
  • VOLATILE: todo lo demás, como funciones que usan random() o cambian datos. PostgreSQL la vuelve a llamar en cada fila.

Una etiqueta equivocada da resultados equivocados: una función IMMUTABLE que lee una tabla puede devolver valores viejos desde un índice o un plan en caché.

Reemplazar, sobrecargar y eliminar una función

CREATE OR REPLACE FUNCTION cambia el cuerpo de una función existente pero no puede cambiar su tipo de retorno; para eso, elimínala primero. PostgreSQL identifica una función por su nombre y los tipos de sus argumentos, así que f(int) y f(text) son dos funciones. Para eliminar una de dos funciones con el mismo nombre, indica los tipos de los argumentos:

CREATE OR REPLACE FUNCTION with_tax(price numeric, rate numeric DEFAULT 0.2) ...;
DROP FUNCTION with_tax(numeric, numeric);
DROP PROCEDURE transfer;

El capítulo de PL/pgSQL documenta bucles, cursores, excepciones y SQL dinámico con EXECUTE. Para ejecutar una función automáticamente cuando cambian filas, asóciala a un trigger.

Preguntas frecuentes

¿Qué diferencia hay entre una función y un procedimiento en PostgreSQL?

Una función devuelve un valor y se llama dentro de una consulta (SELECT f(x)). Un procedimiento, añadido en PostgreSQL 11, se ejecuta por sí solo con CALL y puede hacer COMMIT o ROLLBACK dentro de su cuerpo; una función siempre corre dentro de la transacción de quien la llama.

¿Qué significa $$ en una función de PostgreSQL?

$$ es el entrecomillado con dólar: marca el inicio y el final de un literal de cadena, así el cuerpo de la función puede contener comillas simples sin escaparlas. También puedes añadir una etiqueta, como en $body$ ... $body$.

¿Cómo veo el código de una función en PostgreSQL?

En psql, \df lista las funciones y \sf name imprime la definición de una. En SQL, SELECT pg_get_functiondef('name'::regproc); devuelve la sentencia CREATE OR REPLACE FUNCTION completa.

¿Una función de PostgreSQL puede devolver varias filas?

Sí. Declárala con RETURNS TABLE (col type, ...) o RETURNS SETOF type y llámala en el FROM como una tabla: SELECT * FROM f(1);.

Ilustración de los lenguajes de programación de Coddy

Aprende a programar con Coddy

Comenzar