Creando funciones simples: CREATE FUNCTION

En PostgreSQL las funciones son una herramienta potente que te permite automatizar tareas, crear lógica de negocio y darle más inteligencia al servidor. Piensa en las funciones como mini-programas que se ejecutan dentro de la base de datos. Son útiles para:

• Reutilizar código. Si repites las mismas consultas varias veces, mételas en una función y llámalas cuando las necesites.
• Automatizar tareas. Por ejemplo, si necesitas calcular el sueldo de los empleados según sus horas trabajadas. Una función lo hace de lujo.
• Encapsular lógica. Permite dejar los cálculos complicados en el servidor, así los clientes no se rompen la cabeza con consultas SQL.

Sintaxis general de CREATE FUNCTION

Así se ve la estructura general para crear una función:

CREATE FUNCTION function_name(parameters) RETURNS return_type AS $$
BEGIN
    -- Cuerpo de la función (lógica)
    RETURN resultado;
END;
$$ LANGUAGE plpgsql;

Vamos a ver las partes principales:

CREATE FUNCTION function_name(parameters):

En esta línea ponemos el nombre de la función function_name y los parámetros (si hacen falta).

Los parámetros pueden tener nombre y tipo de dato: my_param INTEGER, another_param TEXT.

RETURNS return_type:

Indicamos qué va a devolver nuestra función: un solo valor (INTEGER, TEXT, etc.) o un conjunto de datos (TABLE, RECORD).

BEGIN ... END:

Entre estas palabras clave está el "cuerpo" de la función, donde pasa toda la magia.

RETURN resultado:

Devuelve el resultado de la función. Ojo: el tipo del resultado tiene que ser igual al que pusiste en RETURNS.

LANGUAGE plpgsql:

Indicamos que usamos el lenguaje PL/pgSQL. PostgreSQL soporta otros lenguajes, pero ahora nos interesa este.

Ejemplo simple: sumar dos números

Vamos a crear una función que devuelve la suma de dos números enteros.

CREATE FUNCTION add_numbers(a INT, b INT) RETURNS INT AS $$
BEGIN
    RETURN a + b;
END;
$$ LANGUAGE plpgsql;

Ahora la llamamos:

SELECT add_numbers(5, 7); -- Resultado: 12

¿Qué pasa aquí?

• La función recibe dos parámetros a y b de tipo INT.
• Dentro de la función simplemente los sumamos (a + b) y devolvemos el resultado.
• ¡Así de fácil, como una calculadora!

Ejemplo usando variables

Imagina que tenemos una base de datos de una universidad y queremos saber cuántos estudiantes están registrados.

Creamos la función:

CREATE FUNCTION count_students() RETURNS INT AS $$
DECLARE
    total INT; -- Declaramos una variable para guardar el resultado
BEGIN
    SELECT COUNT(*) INTO total FROM students; -- Contamos las filas en la tabla
    RETURN total; -- Devolvemos el resultado
END;
$$ LANGUAGE plpgsql;

Llamada a la función:

SELECT count_students(); -- Supongamos, resultado: 120

Aquí vemos:

• Uso de la variable total para guardar el resultado de la consulta SQL.
• El comando SELECT ... INTO mete el resultado de la consulta en la variable.

Este enfoque es muy útil si necesitas procesar los datos antes de devolverlos.

Devolver varios valores: RETURNS TABLE

En el ejemplo anterior solo devolvíamos un valor. Pero ¿y si nuestra función tiene que devolver un conjunto de datos, como una lista de estudiantes? Aquí es donde RETURNS TABLE es útil.

Ejemplo:

CREATE FUNCTION get_students() RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
    RETURN QUERY SELECT id, name FROM students;
END;
$$ LANGUAGE plpgsql;

Llamada a la función:

SELECT * FROM get_students();

Posible resultado:

La utilidad de RETURN QUERY para ejecutar consultas dentro de la función

RETURN QUERY nos permite devolver el resultado de una consulta SQL directamente desde la función. Así nos ahorramos pasos intermedios y las funciones quedan más simples.

Vamos a crear una función que solo devuelve los estudiantes que están activos:

CREATE FUNCTION get_active_students() RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
    RETURN QUERY SELECT id, name FROM students WHERE active = TRUE;
END;
$$ LANGUAGE plpgsql;

Antes de llamar a la función get_active_students(), hay que crear la tabla students y llenarla con datos de prueba. Así se hace:

-- Creamos la tabla de estudiantes
CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    active BOOLEAN DEFAULT TRUE
);

-- Añadimos algunos registros
INSERT INTO students (name, active) VALUES
('Alice', FALSE),
('Bob', TRUE),
('Charlie', TRUE),
('Dana', FALSE);

Tabla:

Ahora la llamada:

SELECT * FROM get_active_students();

Resultado:

Comprobando la validez de los datos antes de ejecutar

Las funciones pueden tener comprobaciones IF para asegurarse de que los datos son correctos. Por ejemplo, podemos crear una función para pasar a un estudiante al siguiente curso solo si ha aprobado todos los exámenes.

Ejemplo:

CREATE FUNCTION promote_student(student_id INT) RETURNS TEXT AS $$
DECLARE
    passed_exams INT;
BEGIN
    -- Contamos el número de exámenes aprobados por el estudiante
    SELECT COUNT(*) INTO passed_exams
    FROM exams
    WHERE student_id = promote_student.student_id AND status = 'aprobado';

    -- Comprobamos la condición
    IF passed_exams < 5 THEN
        RETURN 'El estudiante no ha aprobado suficientes exámenes';
    END IF;

    -- Actualizamos el curso del estudiante
    UPDATE students
    SET course = course + 1
    WHERE id = promote_student.student_id;

    RETURN '¡Estudiante promovido!';
END;
$$ LANGUAGE plpgsql;

Errores típicos al crear funciones

Falta el tipo de resultado. PostgreSQL siempre exige que indiques qué va a devolver la función. Por ejemplo:

CREATE FUNCTION fail() AS $$ -- Error: falta RETURNS
BEGIN
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

Solución:

CREATE FUNCTION succeed() RETURNS INT AS $$
BEGIN
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

Tipo de resultado incorrecto. Si pones RETURNS INT, tienes que devolver un número. Intentar devolver una cadena en ese caso es mala idea.

Error en las consultas SQL dentro de la función. Siempre prueba tus consultas antes de usarlas en funciones. Mejor testéalas "a mano" con psql o pgAdmin.