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.

Practice

1. What is the main purpose of OUT parameters in PostgreSQL functions?

easy

A. To define the function's return type as a single value

B. To declare input variables for the function

C. To create temporary tables inside the function

D. To allow a function to return multiple values as columns

Solution

1. Step 1: Understand OUT parameters role

   OUT parameters are used to return multiple values from a function as separate columns.

2. Step 2: Compare with other options

   Input variables are declared with IN, not OUT. Temporary tables and single return types are unrelated to OUT parameters.

3. Final Answer:

   To allow a function to return multiple values as columns -> Option D

4. Quick Check:

   OUT parameters = multiple return columns [OK]

Hint: OUT parameters return multiple columns from a function [OK]

Common Mistakes:

• Confusing OUT with IN parameters
• Thinking OUT creates tables
• Assuming OUT returns a single value

2. Which of the following is the correct syntax to declare an OUT parameter in a PostgreSQL function?

easy

A. CREATE FUNCTION f(a INT OUT) RETURNS VOID AS $$ ... $$ LANGUAGE plpgsql;

B. CREATE FUNCTION f(OUT a INT) RETURNS RECORD AS $$ ... $$ LANGUAGE plpgsql;

C. CREATE FUNCTION f(a INT) RETURNS OUT INT AS $$ ... $$ LANGUAGE plpgsql;

D. CREATE FUNCTION f(a INT) RETURNS TABLE(OUT a INT) AS $$ ... $$ LANGUAGE plpgsql;

Solution

1. Step 1: Check correct OUT parameter syntax

   OUT parameters are declared inside the parameter list as OUT name type. CREATE FUNCTION f(OUT a INT) RETURNS RECORD AS $$ ... $$ LANGUAGE plpgsql; matches this.

2. Step 2: Analyze other options

   CREATE FUNCTION f(a INT OUT) RETURNS VOID AS $$ ... $$ LANGUAGE plpgsql; wrongly places OUT after type. CREATE FUNCTION f(a INT) RETURNS OUT INT AS $$ ... $$ LANGUAGE plpgsql; misuses RETURNS OUT. CREATE FUNCTION f(a INT) RETURNS TABLE(OUT a INT) AS $$ ... $$ LANGUAGE plpgsql; misuses RETURNS TABLE with OUT inside parentheses.

3. Final Answer:

   CREATE FUNCTION f(OUT a INT) RETURNS RECORD AS $$ ... $$ LANGUAGE plpgsql; -> Option B

4. Quick Check:

   OUT parameters declared as 'OUT name type' [OK]

Hint: OUT parameters go inside parentheses before RETURNS [OK]

Common Mistakes:

• Placing OUT after the type
• Using RETURNS OUT instead of parameter list
• Confusing RETURNS TABLE syntax

3. Given the function:

CREATE FUNCTION get_person() RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
  RETURN QUERY SELECT 1, 'Alice';
END;
$$ LANGUAGE plpgsql;

What will be the output of SELECT * FROM get_person();?

medium

A. One row with columns id=1 and name='Alice'

B. No rows returned

C. Error: function does not have OUT parameters

D. One row with a single column containing a record

Solution

1. Step 1: Understand RETURNS TABLE behavior

   RETURNS TABLE defines OUT parameters implicitly, so the function returns rows with columns id and name.

2. Step 2: Analyze the RETURN QUERY statement

   The query returns one row with values (1, 'Alice'), so SELECT * FROM function returns that row.

3. Final Answer:

   One row with columns id=1 and name='Alice' -> Option A

4. Quick Check:

   RETURNS TABLE returns rows with named columns [OK]

Hint: RETURNS TABLE means function returns rows with named columns [OK]

Common Mistakes:

• Thinking RETURNS TABLE needs explicit OUT keyword
• Expecting no rows or error
• Assuming single column record output

4. Consider this function definition:

CREATE FUNCTION calc_sum(a INT, b INT, OUT result INT) AS $$
BEGIN
  result := a + b;
END;
$$ LANGUAGE plpgsql;

Which of the following calls will cause an error?

medium

A. SELECT * FROM calc_sum(2, 3);

B. SELECT calc_sum(2, 3);

C. CALL calc_sum(2, 3, result_var);

D. SELECT result FROM calc_sum(2, 3);

Solution

1. Step 1: Understand how OUT parameters affect function call

   Functions with OUT parameters return a record, so SELECT * FROM calc_sum(2, 3); works and returns result column.

2. Step 2: Analyze each call

   SELECT calc_sum(2, 3); returns a record but as a single column, valid. SELECT result FROM calc_sum(2, 3); tries to select a column named 'result' directly from function call without FROM, which is invalid syntax. CALL calc_sum(2, 3, result_var); is invalid syntax for CALL with OUT parameters and will cause an error.

3. Final Answer:

   CALL calc_sum(2, 3, result_var); -> Option C

4. Quick Check:

   CALL with OUT parameters requires different syntax [OK]

Hint: Use SELECT * FROM function() with OUT params, not SELECT column directly or CALL with extra params [OK]

Common Mistakes:

• Selecting OUT column without FROM clause
• Using CALL incorrectly with OUT parameters
• Assuming function returns scalar value

5. You want to create a function that returns both the length and uppercase version of a text input using OUT parameters. Which of the following function definitions correctly achieves this?

hard

A. CREATE FUNCTION text_info(input TEXT, OUT len INT, OUT upper TEXT) AS $$ BEGIN len := length(input); upper := upper(input); END; $$ LANGUAGE plpgsql;

B. CREATE FUNCTION text_info(input TEXT) RETURNS TABLE(len INT, upper TEXT) AS $$ BEGIN len := length(input); upper := upper(input); END; $$ LANGUAGE plpgsql;

C. CREATE FUNCTION text_info(input TEXT, OUT len INT, OUT upper TEXT) RETURNS RECORD AS $$ BEGIN len := length(input); upper := upper(input); END; $$ LANGUAGE plpgsql;

D. CREATE FUNCTION text_info(input TEXT) RETURNS RECORD AS $$ BEGIN RETURN QUERY SELECT length(input), upper(input); END; $$ LANGUAGE plpgsql;

Solution

1. Step 1: Check OUT parameter declaration and assignment

   CREATE FUNCTION text_info(input TEXT, OUT len INT, OUT upper TEXT) AS $$ BEGIN len := length(input); upper := upper(input); END; $$ LANGUAGE plpgsql; declares OUT parameters in the signature and assigns values inside the function body, which is correct.

2. Step 2: Compare other options

   CREATE FUNCTION text_info(input TEXT) RETURNS TABLE(len INT, upper TEXT) AS $$ BEGIN len := length(input); upper := upper(input); END; $$ LANGUAGE plpgsql; uses RETURNS TABLE but does not assign values properly. CREATE FUNCTION text_info(input TEXT, OUT len INT, OUT upper TEXT) RETURNS RECORD AS $$ BEGIN len := length(input); upper := upper(input); END; $$ LANGUAGE plpgsql; mixes OUT parameters with RETURNS RECORD incorrectly. CREATE FUNCTION text_info(input TEXT) RETURNS RECORD AS $$ BEGIN RETURN QUERY SELECT length(input), upper(input); END; $$ LANGUAGE plpgsql; returns a record but does not use OUT parameters as requested.

3. Final Answer:

   CREATE FUNCTION text_info(input TEXT, OUT len INT, OUT upper TEXT) AS $$ BEGIN len := length(input); upper := upper(input); END; $$ LANGUAGE plpgsql; -> Option A

4. Quick Check:

   OUT params declared and assigned inside function [OK]

Hint: Declare OUT params in signature and assign inside function [OK]

Common Mistakes:

• Mixing RETURNS TABLE with OUT params incorrectly
• Not assigning OUT parameters inside function
• Using RETURNS RECORD without OUT params