Table functions in SQL

RETURNS TABLE turns a function into something you can put in FROM.

-- RETURNS TABLE makes the function usable like a relation.
CREATE FUNCTION customer_orders(cust INTEGER)
RETURNS TABLE (order_no INTEGER, status TEXT, total NUMERIC)
LANGUAGE SQL
STABLE
BEGIN ATOMIC
    SELECT id, status, total
    FROM orders
    WHERE customer_id = cust;
END;

SELECT *
FROM customer_orders(1)
ORDER BY order_no;

How it works

1. RETURNS TABLE (order_no INTEGER, ...) declares the shape of the result set.
2. BEGIN ATOMIC ... END holds a checked SQL body — typos fail at CREATE time.
3. FROM customer_orders(1) then reads it like any relation.

Keywords and builtins used here

• ATOMIC
• BEGIN
• BY
• CREATE
• END
• FROM
• FUNCTION
• INTEGER
• LANGUAGE
• NUMERIC
• ORDER
• RETURNS
• SELECT
• SQL
• STABLE
• TABLE
• TEXT
• WHERE

The run, in numbers

Lines

14

Characters to type

320

Tokens

51

Three-star pace

65 tpm

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

Type this snippet

Step 3 of 4 in Functions, step 3 of 23 in PostgreSQL Stored Procedures.

← Previous Next →