PL/pgSQL Parameter Modes

Master function parameter modes — IN (default), OUT (return values), and INOUT (both directions). Critical fixes: original WHERE clause referenced undefined film_title variable, and INOUT example used PERFORM which discards the modified value. Use SELECT INTO not PERFORM to capture INOUT outputs in a PL/pgSQL block.

PL/pgSQL functions support three parameter modes: IN, OUT, and INOUT. They control whether the function reads a parameter, writes to it, or both:

Most parameters are IN — that's the default if you don't specify a mode. OUT and INOUT exist for the special case where you want a function to return multiple values without bundling them into a composite type.

An IN parameter is data flowing into the function. Changes inside the function don't escape — the caller's value is unaffected. Most parameters fit this pattern.

CREATE OR REPLACE FUNCTION add_numbers(a INT, b INT)  -- IN is implicit
RETURNS INT
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN a + b;
END;
$$;

SELECT add_numbers(5, 3);

An OUT parameter is data flowing out of the function — a way to "return" multiple named values without using a composite type. The function assigns to the OUT parameter; the caller receives it as part of the function's result.

When a function has OUT parameters, you don't write a RETURNS clause for the same values — the OUT parameters' types implicitly define the return shape.

Look up a film's length by title. Setup:

DROP TABLE IF EXISTS film;

CREATE TABLE film (
    film_id SERIAL PRIMARY KEY,
    title   VARCHAR(255) NOT NULL,
    length  INT
);

INSERT INTO film (title, length) VALUES
    ('Film A',  90),
    ('Film B', 120),
    ('Film C', 150),
    ('Film D',  80),
    ('Film E',  30),
    ('Film F',  60);

CREATE OR REPLACE FUNCTION get_film_length(p_title VARCHAR)
RETURNS INT
LANGUAGE plpgsql
AS $$
DECLARE
    film_length INT;
BEGIN
    SELECT f.length INTO film_length
    FROM   film f
    WHERE  f.title = p_title;

    RETURN film_length;
END;
$$;

SELECT get_film_length('Film A');

OUT parameters earn their keep when you want to return multiple named values:

CREATE OR REPLACE FUNCTION get_film_details(
    p_film_id INT,
    OUT title  VARCHAR,
    OUT length INT
)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT f.title, f.length
    INTO   title, length
    FROM   film f
    WHERE  f.film_id = p_film_id;
END;
$$;

SELECT * FROM get_film_details(1);

Three things happening here:

• No RETURNS clause — the OUT parameters define the shape of the result.
• No RETURN statement in the body — assignments to the OUT params are the return value.
• Called as SELECT * FROM — treats the function call as a row source so the columns spread.

An INOUT parameter starts with a value supplied by the caller and ends with a value supplied by the function. Effectively a parameter that's both read and written.

A function that adjusts a length by a given amount — passes len in, modifies it, returns it:

CREATE OR REPLACE FUNCTION adjust_length(
    INOUT len    INT,
          adjustment INT
)
LANGUAGE plpgsql
AS $$
BEGIN
    len := len + adjustment;
END;
$$;

-- From SQL: returns the modified value
SELECT adjust_length(100, 20);

-- From a PL/pgSQL block: the variable is updated in place
DO $$
DECLARE
    new_length INT := 100;
BEGIN
    SELECT adjust_length(new_length, 20) INTO new_length;
    RAISE NOTICE 'New length: %', new_length;
END
$$;

1. Default to IN parameters. Most data flows in, not out. Reach for OUT/INOUT only when you need to.
2. Prefix parameters to avoid column-name collisions. p_title beats title when the table has a title column. Saves you from WHERE title = title ambiguity.
3. Use SELECT ... INTO to capture INOUT results in a PL/pgSQL block, not PERFORM. PERFORM discards return values.
4. Don't mix RETURNS with OUT parameters — pick one. Either an explicit return type with RETURN in the body, or OUT parameters that define the shape implicitly.
5. For multi-row outputs, use RETURNS TABLE or SETOF rather than collecting into OUT arrays. Cleaner and more idiomatic.
6. Document the parameter direction in the function name or comment. increment_counter(INOUT cnt) reads more clearly than just increment_counter(cnt).

• Functions support three parameter modes: IN (default, caller-to-function), OUT (function-to-caller), INOUT (both).
• Most parameters are IN — pass data into the function. Changes don't escape.
• OUT parameters let a function return multiple named values without a composite type. No RETURNS clause needed when using OUT.
• INOUT is rare but useful when a function should mutate a passed value in place.
• To capture INOUT/OUT results in a PL/pgSQL block, use SELECT function(...) INTO var, not PERFORM.
• For many rows, prefer RETURNS TABLE or RETURNS SETOF over arrays of OUT parameters.