CREATE PROCEDURE in SQL

A procedure is CALLed for its effects — here, cancelling an order.

-- A procedure is CALLed for its effects; it returns nothing.
CREATE PROCEDURE cancel_order(order_no INTEGER)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE orders
    SET status = 'canceled'
    WHERE id = order_no;
END;
$$;

CALL cancel_order(102);

SELECT id, status
FROM orders
WHERE id = 102;

How it works

1. CREATE PROCEDURE cancel_order(...) declares parameters but no return type.
2. The LANGUAGE plpgsql body runs an UPDATE against the order.
3. CALL cancel_order(102) invokes it; the closing query shows the effect.

Keywords and builtins used here

• AS
• BEGIN
• CALL
• CREATE
• END
• FROM
• INTEGER
• LANGUAGE
• PROCEDURE
• SELECT
• SET
• UPDATE
• WHERE

The run, in numbers

Lines

16

Characters to type

278

Tokens

47

Three-star pace

70 tpm

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

Type this snippet

Step 1 of 4 in Procedures & transactions, step 9 of 23 in PostgreSQL Stored Procedures.

← Previous Next →