RETURN QUERY in SQL

A PL/pgSQL function that streams a whole result set back out.

-- RETURN QUERY streams a result set out of a PL/pgSQL function.
CREATE FUNCTION low_stock(threshold INTEGER)
RETURNS TABLE (sku TEXT, remaining INTEGER)
LANGUAGE plpgsql
STABLE
AS $$
BEGIN
    RETURN QUERY
    SELECT p.sku, p.stock
    FROM products AS p
    WHERE p.stock < threshold
    ORDER BY p.stock;
END;
$$;

SELECT *
FROM low_stock(10);

How it works

1. RETURNS TABLE (sku TEXT, remaining INTEGER) shapes the output.
2. RETURN QUERY followed by the SELECT sends every matching row.
3. Qualifying columns as p.sku avoids colliding with the output names.

Keywords and builtins used here

• AS
• BEGIN
• BY
• CREATE
• END
• FROM
• FUNCTION
• INTEGER
• LANGUAGE
• ORDER
• RETURN
• RETURNS
• SELECT
• STABLE
• TABLE
• TEXT
• WHERE

The run, in numbers

Lines

17

Characters to type

326

Tokens

63

Three-star pace

65 tpm

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

Type this snippet

Step 2 of 4 in Queries in code, step 14 of 23 in PostgreSQL Stored Procedures.

← Previous Next →