Funciones dentro del SELECT

Este tema es material de consulta, no de lectura lineal. Es el único del curso que agrupa lo que otras guías reparten en cuatro capítulos y veinte lecciones. Léelo una vez para saber qué existe, y vuelve a él cuando necesites algo concreto. Nadie memoriza esta lista; se memoriza dónde está.

1. Qué es

Una función escalar recibe uno o varios valores y devuelve un valor por fila. No colapsa filas —eso lo hacen las agregaciones, funciones-de-agregacion— ni mira otras filas —eso lo hacen las ventanas, que-es-una-funcion-de-ventana.

Se usan sobre todo en el SELECT, pero valen en cualquier sitio donde quepa una expresión: WHERE, ORDER BY, GROUP BY, CHECK.

Cuatro familias:

2. Texto

La diferencia que importa — || contra concat():

SELECT concat('a', NULL, 'b') AS concat_fn, 'a' || NULL || 'b' AS operador;

 concat_fn | operador
-----------+----------
 ab        |

|| se contagia del NULL y anula la cadena entera. concat() lo ignora. Cuando construyas una dirección o un nombre con partes que pueden faltar, usa concat_ws().

substring tiene tres detalles que confunden: - Las posiciones empiezan en 1, no en 0. - substring(t FROM p) sin FOR va hasta el final. - substr(t, p, n) es la forma corta y hace lo mismo.

3. Fecha

extract acepta year, month, day, hour, quarter, week, doy (día del año) y dow (día de la semana, 0 = domingo).

date_trunc es la función más útil de las cuatro familias. Es la que convierte pedidos sueltos en una serie mensual:

SELECT to_char(order_date, 'YYYY-MM') AS mes,
       count(*)                       AS pedidos,
       round(sum(freight), 2)         AS flete
FROM orders
GROUP BY 1
ORDER BY 1;

   mes   | pedidos |  flete
---------+---------+---------
 1996-07 |      22 | 1288.18
 1996-08 |      25 | 1397.17
 1996-09 |      23 | 1123.48
 1996-10 |      26 | 1520.59
 1996-11 |      25 | 2151.86

Aquí he usado to_char porque produce un texto ordenable (1996-07 < 1996-08). date_trunc es mejor cuando el resultado va a seguir siendo una fecha: mantiene el tipo y permite restar periodos después.

Gotcha: date_trunc() sobre un date devuelve un timestamptz, no un date. Si esperabas una fecha limpia, castea: date_trunc('month', order_date)::date.

El último día del mes, que no tiene función propia:

SELECT date_trunc('month', '1997-05-17'::date) + INTERVAL '1 month - 1 day';

 1997-05-31 00:00:00-05

Formatos de to_char que se usan de verdad:

4. Numéricas

round con dos argumentos solo existe para numeric. round(x::double precision, 2) da error de función inexistente. Una razón más para que el dinero sea numeric (tipos-de-datos-en-postgresql).

greatest/least no son agregaciones. Comparan valores de la misma fila, no de filas distintas. El equivalente entre filas es max()/min().

5. Nulos y condicionales

Están explicadas en null-y-la-logica-de-tres-valores. Se repiten aquí porque es donde las vas a buscar.

El uso que hay que recordar de nullif:

total / nullif(cantidad, 0)     -- NULL en vez de "division by zero"

6. Ejemplos sobre Northwind

Formatear un nombre y calcular antigüedad:

SELECT left(first_name, 1) || '. ' || last_name           AS empleado,
       upper(country)                                     AS pais,
       hire_date,
       extract(year FROM age(current_date, hire_date))::int AS anios
FROM employees
ORDER BY hire_date
LIMIT 4;

   empleado   | pais | hire_date  | anios
--------------+------+------------+-------
 J. Leverling | USA  | 1992-04-01 |    34
 N. Davolio   | USA  | 1992-05-01 |    34
 A. Fuller    | USA  | 1992-08-14 |    34
 M. Peacock   | USA  | 1993-05-03 |    33

34 años de antigüedad porque los datos son de 1992 y hoy es 2026. Es el recordatorio de que Northwind vive en 1996-1998: cualquier cálculo contra current_date da cifras absurdas. Para análisis conviene anclar la fecha "de hoy" al máximo de la base:

SELECT max(order_date) FROM orders;    -- 1998-05-06

Extraer un número que está enterrado en un texto:

SELECT product_name, quantity_per_unit,
       split_part(quantity_per_unit, ' ', 1) AS cantidad
FROM products LIMIT 5;

         product_name         |  quantity_per_unit  | cantidad
------------------------------+---------------------+----------
 Chai                         | 10 boxes x 30 bags  | 10
 Chang                        | 24 - 12 oz bottles  | 24
 Aniseed Syrup                | 12 - 550 ml bottles | 12

Funciona para estas cinco filas. quantity_per_unit es texto libre con al menos tres formatos distintos, y split_part solo acierta cuando el número va primero y separado por un espacio. Esta consulta es un ejemplo de función, no una solución: extraer datos de un campo de texto libre siempre es frágil, y la respuesta profesional es pedir que ese dato sea una columna numérica.

Limpiar un teléfono:

SELECT company_name, phone,
       replace(replace(phone, '(', ''), ')', '') AS limpio
FROM customers WHERE phone LIKE '(%' LIMIT 3;

            company_name            |     phone      |    limpio
------------------------------------+----------------+--------------
 Ana Trujillo Emparedados y helados | (5) 555-4729   | 5 555-4729
 Antonio Moreno Taquería            | (5) 555-3932   | 5 555-3932
 Around the Horn                    | (171) 555-7788 | 171 555-7788

Dos replace anidados para dos caracteres. Con más de dos, translate lo hace de una vez: translate(phone, '()- ', '') los quita todos.

7. Errores comunes

1. || con una parte nula. Anula la cadena. Usa concat_ws().
2. Creer que substring empieza en 0. Empieza en 1.
3. date_trunc devolviendo timestamptz cuando esperabas date. Castea.
4. round(x, 2) sobre un double precision. No existe esa función; hay que castear a numeric.
5. Aplicar una función a una columna dentro del WHERE. WHERE extract(year FROM order_date) = 1997 impide usar un índice sobre order_date. La versión que sí lo usa es WHERE order_date >= '1997-01-01' AND order_date < '1998-01-01'. Ver indices.
6. to_char con 'Month' esperando español. Da el idioma del servidor. Con TM delante respeta la configuración regional; sin ella es inglés.
7. Extraer datos de texto libre y darlo por bueno. Ver quantity_per_unit.

8. Diferencias con SQL Server

Esta es la sección que hay que llevarse a una entrevista. Es la tabla de traducción completa.

8.1 Texto

8.2 Fecha

8.3 Números y conversión

Los dos que más duelen al migrar son DATEDIFF y CONVERT con estilos numéricos. DATEDIFF(month, a, b) cuenta fronteras de mes cruzadas, no meses completos: no hay una traducción de una línea, hay que decidir qué querías contar. Y los estilos numéricos de CONVERT (103, 112, 120…) son códigos que solo existen en SQL Server; se traducen a un formato de to_char, uno por uno.

9. Ejercicios

1. Devuelve el nombre de cada producto en mayúsculas, su longitud, y sus 10 primeros caracteres.
2. Los pedidos agrupados por trimestre (1996-Q3, 1996-Q4…) con su número de pedidos.
3. La dirección completa de cada cliente en una sola columna: calle, ciudad, región y país separados por coma, sin que desaparezca la fila cuando falta la región.
4. Los empleados con su edad en años en el momento de su contratación.
5. El precio de cada producto formateado como texto con separador de miles y dos decimales, y el descuento medio de order_details como porcentaje con un decimal.

10. Pregunta de negocio

Marketing quiere un archivo con "una línea por cliente, lista para pegar en la herramienta de envíos": nombre de contacto, empresa, y dirección postal completa en un solo campo. Insisten en que no puede faltar ninguno de los 91 clientes.

La última frase es la trampa. Y ya la has visto antes en este tema.