INOUT parameters in SQL

A payment procedure that hands a receipt back through its parameter list.

-- An INOUT parameter carries a result back from a CALL.
CREATE PROCEDURE take_payment(
    order_no INTEGER,
    amount NUMERIC,
    INOUT receipt TEXT DEFAULT NULL
)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO payments (order_id, amount, paid_at)
    VALUES (order_no, amount, now());
    receipt := 'paid ' || amount || ' against order ' || order_no;
END;
$$;

CALL take_payment(102, 34.95);

How it works

1. INOUT receipt TEXT DEFAULT NULL is both argument and result.
2. The body records the payment, then assigns the receipt string.
3. CALL prints the INOUT values as a result row when they come back.

Keywords and builtins used here

• AS
• BEGIN
• CALL
• CREATE
• DEFAULT
• END
• INOUT
• INSERT
• INTEGER
• INTO
• LANGUAGE
• NULL
• NUMERIC
• PROCEDURE
• TEXT
• VALUES

The run, in numbers

Lines

16

Characters to type

371

Tokens

73

Three-star pace

65 tpm

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

Type this snippet

Step 2 of 4 in Procedures & transactions, step 10 of 23 in PostgreSQL Stored Procedures.

← Previous Next →