report_function.sql in SQL

One table function feeds a query and a narrated report from the same numbers.

-- 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;
$$;

How it works

1. customer_sales() aggregates orders per customer inside BEGIN ATOMIC.
2. Used as a relation, it filters like a table; LEFT JOIN keeps quiet customers.
3. The DO block re-reads it row by row, ranking and narrating with RAISE.

Keywords and builtins used here

• 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

The run, in numbers

Lines

35

Characters to type

808

Tokens

155

Three-star pace

65 tpm

At the three-star pace of 65 tokens a minute, this run takes about 143 seconds.

Type this snippet

Step 3 of 3 in Encore, step 23 of 23 in PostgreSQL Stored Procedures.

← Previous