OUT parameters in PostgreSQL - Step-by-Step Execution

Concept Flow - OUT parameters

Start Procedure Call

↓

Execute Procedure Body

↓

Assign Values to OUT Parameters

↓

Return OUT Parameters as Result

↓

End Procedure Call

When a procedure with OUT parameters is called, it runs its code, assigns values to the OUT parameters, and returns those values as the result.

Execution Sample

PostgreSQL

CREATE PROCEDURE get_user_info(IN user_id INT, OUT user_name TEXT, OUT user_age INT)
LANGUAGE plpgsql
AS $$
BEGIN
  SELECT name, age INTO user_name, user_age FROM users WHERE id = user_id;
END;
$$;

This procedure takes a user ID and returns the user's name and age using OUT parameters.

Execution Table

Step	Action	user_id	user_name	user_age	Result
1	Procedure called with user_id=2	2	NULL	NULL	No output yet
2	SELECT name, age INTO user_name, user_age FROM users WHERE id=2	2	'Alice'	30	Values assigned to OUT parameters
3	Procedure ends and returns OUT parameters	2	'Alice'	30	Returns (user_name='Alice', user_age=30)

💡 Procedure ends after assigning values to OUT parameters and returns them as result

Variable Tracker

Variable	Start	After Step 1	After Step 2	Final
user_id	NULL	2	2	2
user_name	NULL	NULL	'Alice'	'Alice'
user_age	NULL	NULL	30	30

Key Moments - 2 Insights

Why are OUT parameters NULL before the SELECT statement?

Because OUT parameters start empty and only get values assigned during procedure execution, as shown in step 2 of the execution_table.

How does the procedure return multiple values?

The procedure returns all OUT parameters together as a result after execution, as seen in step 3 of the execution_table.

Visual Quiz - 3 Questions

Look at the execution_table, what is the value of user_name after step 2?

A 'Alice'

B NULL

C 'Bob'

D 30

💡 Hint

Check the 'user_name' column in row for step 2 in execution_table

At which step does the procedure return the OUT parameters as the result?

A Step 2

B Step 3

C Step 1

D Procedure never returns

💡 Hint

Look at the 'Result' column in execution_table rows

If the SELECT query finds no matching user, what will be the value of user_name after step 2?

A 'Alice'

B Empty string

C NULL

D Error

💡 Hint

If SELECT INTO finds no row, variables remain NULL as per PostgreSQL behavior

Concept Snapshot

CREATE PROCEDURE proc_name(IN param1 TYPE, OUT param2 TYPE)
LANGUAGE plpgsql
AS $$
BEGIN
  -- assign values to OUT params
END;
$$;

- OUT params return values automatically
- Call with CALL proc_name(args)
- Result is a row of OUT param values

Full Transcript

This visual execution shows how PostgreSQL procedures with OUT parameters work. When the procedure is called with an input user_id, it runs the SELECT query to find the user's name and age. These values are assigned to the OUT parameters user_name and user_age. After the procedure finishes, it returns these OUT parameters as the result. Initially, OUT parameters are NULL until assigned. If no matching user is found, OUT parameters remain NULL. This way, procedures can return multiple values easily.