PL/pgSQL INOUT Parameters

Master returning values from PostgreSQL stored procedures via INOUT parameters. Procedures support IN and INOUT modes (no OUT). Critical fixes: original tutorial confused FUNCTION with PROCEDURE syntax, had a broken anonymous block with re-declaration inside BEGIN, and misplaced the LANGUAGE clause after the closing $$.

Procedures can't RETURN expression; — that's how they differ from functions. But you often want a procedure to hand data back to the caller: a count of rows affected, a generated ID, computed totals, status flags.

The mechanism is INOUT parameters. An INOUT parameter serves as both an input slot (so the caller can pass an initial value) and an output slot (so the procedure can pass a value back). Note that procedures support IN and INOUT only — not the function-only OUT mode.

Calling the procedure differs depending on context:

• From psql: CALL sp_name(); — INOUT parameters with defaults can be omitted; the result is displayed in the result set.
• From inside a PL/pgSQL block: Pass variable names as INOUT arguments — they'll be updated in place.

DROP TABLE IF EXISTS film;

CREATE TABLE film (
    film_id          SERIAL PRIMARY KEY,
    title            VARCHAR(255) NOT NULL,
    release_year     INT,
    rental_duration  INT     DEFAULT 3,
    rental_rate      NUMERIC(4, 2) DEFAULT 4.99,
    length           INT,
    rating           VARCHAR(10)
);

INSERT INTO film (title, release_year, rental_duration, rental_rate, length, rating) VALUES
    ('The Matrix',     1999, 5, 3.99, 136, 'R'),
    ('Inception',      2010, 7, 4.99, 148, 'PG-13'),
    ('Interstellar',   2014, 6, 5.99, 169, 'PG-13'),
    ('The Godfather',  1972, 3, 2.99, 175, 'R'),
    ('The Dark Knight',2008, 4, 3.99, 152, 'PG-13'),
    ('Pulp Fiction',   1994, 4, 4.49, 154, 'R'),
    ('Fight Club',     1999, 5, 4.99, 139, 'R');

A procedure that counts the rows in film and returns the count via an INOUT parameter:

DROP PROCEDURE IF EXISTS count_film(INT);

CREATE OR REPLACE PROCEDURE count_film(INOUT total_film INT DEFAULT 0)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT COUNT(*) INTO total_film FROM film;
END;
$$;

CALL count_film();

Two notes about this call:

• We supplied no argument because total_film has a DEFAULT 0. Without a default, you'd need to pass something — typically NULL — as a placeholder.
• psql displays the final value of the INOUT parameter as the result set. Even though procedures don't "return" anything, psql shows the after-state of every INOUT parameter.

Inside another block, you call the procedure with a variable as the argument; the procedure updates that variable:

DO $$
DECLARE
    total INT := 0;
BEGIN
    CALL count_film(total);
    RAISE NOTICE 'Number of films: %', total;
END
$$;

A procedure can hand back several values at once. This one returns total film count, total length in minutes, and average rental rate:

DROP PROCEDURE IF EXISTS film_stat(INT, INT, NUMERIC);

CREATE OR REPLACE PROCEDURE film_stat(
    INOUT total_film       INT     DEFAULT 0,
    INOUT total_length     INT     DEFAULT 0,
    INOUT avg_rental_rate  NUMERIC DEFAULT 0
)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT COUNT(*), SUM(length), ROUND(AVG(rental_rate), 2)
    INTO   total_film, total_length, avg_rental_rate
    FROM   film;
END;
$$;

CALL film_stat();

One SELECT INTO populates all three INOUT parameters from a single aggregate query — simpler and faster than the original tutorial's three separate aggregations.

Genuine "in-out" — the parameter starts with one value and ends with another. Add 10% to a price:

CREATE OR REPLACE PROCEDURE apply_markup(INOUT price NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
    price := price * 1.10;
END;
$$;

DO $$
DECLARE
    p NUMERIC := 100;
BEGIN
    RAISE NOTICE 'Before: %', p;
    CALL apply_markup(p);
    RAISE NOTICE 'After:  %', p;
END
$$;

p went into the procedure as 100 and came out as 110. That's the "INOUT" — the variable's value is updated in place.

1. Use INOUT for return values from procedures — that's the only way procedures hand data back.
2. Give INOUT parameters DEFAULT values when the caller can omit them. Lets CALL proc_name() work without arguments.
3. Compute related outputs in one query when possible. SELECT COUNT(*), SUM(...), AVG(...) INTO a, b, c FROM ... beats three separate aggregations against the same table.
4. Don't use OUT in procedures — it's a function-only mode. Trying it raises a syntax error.
5. Document INOUT semantics in the procedure name or comment. A reader can't tell from the call site whether CALL adjust(x) reads x, modifies it, or both.
6. For procedures with no INOUT params, just don't return anything. Use RAISE NOTICE for diagnostic output, or write to a log table.

• Procedures hand data back via INOUT parameters. They support IN and INOUT, but not OUT.
• Inside the procedure, an INOUT parameter is just a regular variable you can read and assign.
• From psql, CALL displays each INOUT parameter's final value as a result column. From a PL/pgSQL block, the caller's variable is updated in place.
• Give INOUT parameters DEFAULT values to allow omitting them at the call site.
• For multiple related outputs, populate them all from a single aggregate query when possible.