restock_procedure.sql in SQL

A nightly restock procedure: find the low shelves, top up, commit as you go.

-- A nightly restock: walk the low shelves, top up, commit as you go.
CREATE TABLE restock_log (
    sku TEXT NOT NULL,
    added INTEGER NOT NULL,
    logged_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE PROCEDURE restock(threshold INTEGER, top_up INTEGER)
LANGUAGE plpgsql
AS $$
DECLARE
    item RECORD;
    touched INTEGER := 0;
BEGIN
    FOR item IN
        SELECT sku, stock
        FROM products
        WHERE stock < threshold
        ORDER BY stock
    LOOP
        UPDATE products
        SET stock = stock + top_up
        WHERE sku = item.sku;
        INSERT INTO restock_log (sku, added)
        VALUES (item.sku, top_up);
        touched := touched + 1;
        COMMIT;
    END LOOP;
    RAISE NOTICE 'restocked % products', touched;
END;
$$;

CALL restock(threshold => 5, top_up => 10);

SELECT r.sku, r.added, p.stock
FROM restock_log AS r
JOIN products AS p ON p.sku = r.sku
ORDER BY r.sku;

How it works

1. The FOR ... IN SELECT walk finds products under the threshold.
2. Each pass updates stock, logs the delivery, and COMMITs its batch.
3. CALL restock(threshold => 5, top_up => 10) runs it with named arguments.

Keywords and builtins used here

• AS
• BEGIN
• BY
• CALL
• COMMIT
• CREATE
• DECLARE
• DEFAULT
• END
• FOR
• FROM
• IN
• INSERT
• INTEGER
• INTO
• JOIN
• LANGUAGE
• NOT
• NULL
• ON
• ORDER
• PROCEDURE
• SELECT
• SET
• TABLE
• TEXT
• UPDATE
• VALUES
• WHERE

The run, in numbers

Lines

38

Characters to type

785

Tokens

171

Three-star pace

65 tpm

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

Type this snippet

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

← Previous Next →