report_function.sql en SQL

Una función de tabla alimenta una consulta y un reporte narrado con los mismos números.

-- A sales report: one table function feeds both queries and code.
CREATE FUNCTION customer_sales()
RETURNS TABLE (customer_name TEXT, orders_placed BIGINT, lifetime NUMERIC)
LANGUAGE SQL
STABLE
BEGIN ATOMIC
    SELECT c.name, COUNT(o.id), COALESCE(SUM(o.total), 0)
    FROM customers AS c
    LEFT JOIN orders AS o ON o.customer_id = c.id
    GROUP BY c.name
    ORDER BY COALESCE(SUM(o.total), 0) DESC;
END;

-- As a relation: filter it like any table.
SELECT *
FROM customer_sales()
WHERE orders_placed > 0;

-- As rows in code: narrate the same data with RAISE.
DO $$
DECLARE
    row_data RECORD;
    rank INTEGER := 0;
BEGIN
    FOR row_data IN
        SELECT *
        FROM customer_sales()
    LOOP
        rank := rank + 1;
        RAISE NOTICE '#% % -- % orders, % lifetime', rank,
            row_data.customer_name, row_data.orders_placed,
            row_data.lifetime;
    END LOOP;
END;
$$;

Cómo funciona

1. customer_sales() agrega pedidos por cliente dentro de BEGIN ATOMIC.
2. Usada como relación se filtra como una tabla; LEFT JOIN conserva a los clientes callados.
3. El bloque DO la relee fila por fila, rankeando y narrando con RAISE.

Palabras clave y builtins usados aquí

• AS
• ATOMIC
• BEGIN
• BIGINT
• BY
• COALESCE
• COUNT
• CREATE
• DECLARE
• DESC
• DO
• END
• FOR
• FROM
• FUNCTION
• GROUP
• IN
• INTEGER
• JOIN
• LANGUAGE
• LEFT
• NUMERIC
• ON
• ORDER
• RETURNS
• SELECT
• SQL
• STABLE
• SUM
• TABLE
• TEXT
• WHERE
• c

El intento, en números

Líneas

35

Caracteres a escribir

808

Tokens

155

Ritmo de tres estrellas

65 tpm

Al ritmo de tres estrellas de 65 tokens por minuto, este intento toma unos 143 segundos.

Escribe este fragmento

Paso 3 de 3 en Bis; paso 23 de 23 en Procedimientos almacenados de PostgreSQL.

← Anterior