Functions returning TABLE in PostgreSQL - Step-by-Step Execution

Concept Flow - Functions returning TABLE

Call function

↓

Function starts

↓

Execute RETURN QUERY

↓

Build result rows

↓

Return table result

↓

Receive rows in caller

The function is called, runs its query, builds rows, and returns them as a table to the caller.

Execution Sample

PostgreSQL

CREATE FUNCTION get_users()
RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
  RETURN QUERY SELECT user_id, user_name FROM users;
END;
$$ LANGUAGE plpgsql;

This function returns a table of user IDs and names from the users table.

Execution Table

Step	Action	Query Executed	Rows Built	Output
1	Function called	N/A	N/A	Start function execution
2	Execute RETURN QUERY	SELECT user_id, user_name FROM users	[(1, 'Alice'), (2, 'Bob'), (3, 'Carol')]	Rows prepared to return
3	Return rows	N/A	[(1, 'Alice'), (2, 'Bob'), (3, 'Carol')]	Output table returned to caller
4	Function ends	N/A	N/A	Execution complete

💡 Function ends after returning all rows from RETURN QUERY

Variable Tracker

Variable	Start	After RETURN QUERY	Final
result_rows	empty	[(1, 'Alice'), (2, 'Bob'), (3, 'Carol')]	[(1, 'Alice'), (2, 'Bob'), (3, 'Carol')]

Key Moments - 2 Insights

Why does the function return multiple rows instead of just one?

Because RETURN QUERY runs a SELECT that returns multiple rows, the function collects all rows into the output table as shown in execution_table step 2.

What happens if the SELECT inside RETURN QUERY returns no rows?

The function returns an empty table result, meaning no rows are output, as RETURN QUERY passes through whatever the SELECT returns.

Visual Quiz - 3 Questions

Look at the execution_table at step 2, how many rows does the RETURN QUERY produce?

A 1 row

B 3 rows

C 0 rows

D Unknown

💡 Hint

Check the 'Rows Built' column in step 2 of the execution_table

At which step does the function actually send the rows back to the caller?

A Step 3

B Step 2

C Step 1

D Step 4

💡 Hint

Look at the 'Output' column describing when rows are returned

If the SELECT query inside RETURN QUERY returned no rows, what would 'result_rows' be after step 2?

A [(1, 'Alice')]

B [(2, 'Bob'), (3, 'Carol')]

C empty

D NULL

💡 Hint

Refer to variable_tracker for 'result_rows' state after RETURN QUERY

Concept Snapshot

CREATE FUNCTION name() RETURNS TABLE(col1 type, col2 type) AS $$
BEGIN
  RETURN QUERY SELECT ...;
END;
$$ LANGUAGE plpgsql;

- RETURN QUERY runs a SELECT and returns all rows as a table
- Caller receives multiple rows as function output
- Empty SELECT returns empty table result

Full Transcript

This visual execution shows how a PostgreSQL function returning TABLE works. When the function is called, it executes the RETURN QUERY statement which runs a SELECT query. The rows from this SELECT are collected and returned as the function's output table. The execution table traces each step: function call, query execution, rows built, and return to caller. The variable tracker shows how the result rows variable changes from empty to holding all selected rows. Key moments clarify why multiple rows are returned and what happens if no rows are found. The quiz tests understanding of when rows are produced and returned, and the state of variables after query execution.