Conceptos básicos de funciones

9 ejemplos para empezar con las funciones de PostgreSQL: 6 básicos y 3 intermedios.

Prerrequisitos

CREATE en el esquema app.

# Desarrollo local con Docker (PostgreSQL 18)
docker run -d --name pg18 -e POSTGRES_PASSWORD=dev -p 5432:5432 postgres:18
psql postgres://postgres:dev@localhost:5432/postgres

Ejemplos básicos

1. Función SQL

Cuerpo de una única expresión SELECT.

CREATE OR REPLACE FUNCTION app.account_order_count(p_account_id bigint)
RETURNS bigint
LANGUAGE sql
STABLE
AS $$
  SELECT count(*) FROM app.orders WHERE account_id = p_account_id;
$$;

• El planificador puede integrarla cuando se marca como STABLE/IMMUTABLE
• Sin variables procedurales
• Rápida para proyecciones simples

2. Función PL/pgSQL

Variables y IF/LOOP.

CREATE OR REPLACE FUNCTION app.is_vip(p_account_id bigint)
RETURNS boolean
LANGUAGE plpgsql
STABLE
AS $$
DECLARE v_total numeric;
BEGIN
  SELECT coalesce(sum(total),0) INTO v_total FROM app.orders WHERE account_id = p_account_id;
  RETURN v_total > 10000;
END;
$$;

• PL/pgSQL para ramas y bucles
• VOLATILE es el valor predeterminado si no se especifica en SQL
• Mantenga los cuerpos pequeños

3. Llamar en una consulta

Usar en SELECT y WHERE.

SELECT id, app.account_order_count(id) AS orders FROM app.accounts;

• Las llamadas correlacionadas por fila pueden ser lentas
• Considere reescribir con JOIN
• Marque la volatilidad correctamente

4. RETURNS TABLE

Forma de función que devuelve un conjunto.

CREATE OR REPLACE FUNCTION app.big_orders(p_min numeric)
RETURNS TABLE (order_id bigint, total numeric)
LANGUAGE sql
STABLE
AS $$
  SELECT id, total FROM app.orders WHERE total >= p_min;
$$;

• Funciona como una vista parametrizada
• El planificador puede integrar funciones SQL
• Otorgue EXECUTE a los roles

5. Ejemplo IMMUTABLE

Las funciones puras permiten expresiones de índice.

CREATE OR REPLACE FUNCTION app.norm_email(e text)
RETURNS text
LANGUAGE sql
IMMUTABLE
AS $$ SELECT lower(trim(e)); $$;

• IMMUTABLE no debe depender del contenido de la base de datos ni del tiempo
• Una etiqueta incorrecta causa planes obsoletos
• Úselo para operaciones simples de cadenas

6. DROP y Reemplazar

CREATE OR REPLACE actualiza el cuerpo.

CREATE OR REPLACE FUNCTION app.norm_email(e text) RETURNS text
LANGUAGE sql IMMUTABLE AS $$ SELECT lower(trim(e)); $$;

• No se puede cambiar el tipo de retorno con OR REPLACE
• Use DROP FUNCTION si la firma cambia
• Rastrear en migraciones

Ejemplos intermedios

7. Procedimiento vs Función

Los procedimientos permiten el control de transacciones (CALL).

CREATE OR REPLACE PROCEDURE app.archive_old_orders()
LANGUAGE plpgsql
AS $$
BEGIN
  DELETE FROM app.orders WHERE created_at < now() - interval '2 years';
END;
$$;
CALL app.archive_old_orders();

• Los procedimientos usan CALL, no SELECT
• Pueden hacer COMMIT dentro de bloques de procedimientos
• Las funciones no pueden hacer COMMIT

8. SECURITY INVOKER

El valor predeterminado se ejecuta como el llamador.

ALTER FUNCTION app.account_order_count(bigint) SECURITY INVOKER;

• Invoker respeta el RLS del llamador
• DEFINER eleva privilegios
• Audite las funciones definer cuidadosamente

9. Establecer search_path en Definer

Refuerce las funciones privilegiadas.

CREATE OR REPLACE FUNCTION app.admin_stat()
RETURNS int LANGUAGE sql SECURITY DEFINER
SET search_path = pg_catalog, pg_temp
AS $$ SELECT 1; $$;

• Previene el secuestro de objetos
• Consulte el artículo sobre seguridad definer
• Todavía se requiere el mínimo privilegio

Versiones de la pila: Esta página se escribió para PostgreSQL 18.4 (estable 18, mantenimiento 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x y PostGIS 3.5+.