User-defined functions in SQL - Step-by-Step Execution

Concept Flow - User-defined functions

Define function with name and parameters

↓

Write function body with SQL statements

↓

Save function in database

↓

Call function in SQL query

↓

Function executes and returns result

↓

Result used in query output

This flow shows how you create a function, save it, then call it in a query to get results.

Execution Sample

SQL

CREATE FUNCTION add_numbers(a INT, b INT) RETURNS INT AS $$
BEGIN
  RETURN a + b;
END;
$$ LANGUAGE plpgsql;

SELECT add_numbers(3, 5);

Defines a function that adds two numbers and then calls it with 3 and 5.

Execution Table

Step	Action	Input Parameters	Operation	Output/Result
1	Define function add_numbers	a, b	Store function logic: RETURN a + b	Function saved in database
2	Call function add_numbers	3, 5	Calculate 3 + 5	8
3	Return result	N/A	Output value from function	8

💡 Function call completes and returns the sum of inputs.

Variable Tracker

Variable	Start	After Call	Final
a	undefined	3	3
b	undefined	5	5
return_value	undefined	8	8

Key Moments - 3 Insights

Why do we need to define the function before calling it?

The function must be saved in the database first (see execution_table step 1) so SQL knows what code to run when called.

What happens if we call the function with different numbers?

The function uses the new input values for a and b and returns their sum (like step 2), so output changes accordingly.

Why is the return value important?

The return value is what the function outputs to the query (step 3), so it must be correctly calculated inside the function.

Visual Quiz - 3 Questions

Look at the execution_table, what is the output when add_numbers is called with inputs 3 and 5?

A 15

B 8

C 35

D Error

💡 Hint

Check execution_table row 2 where inputs 3 and 5 are added.

At which step is the function logic stored in the database?

A Step 1

B Step 2

C Step 3

D After the query

💡 Hint

Look at execution_table row 1 describing function definition.

If we call add_numbers(10, 20), what will be the return_value in variable_tracker after call?

A 10

B 20

C 30

D Undefined

💡 Hint

Return value is sum of inputs, see variable_tracker return_value after call.

Concept Snapshot

CREATE FUNCTION name(params) RETURNS type AS $$ BEGIN ... END; $$ LANGUAGE plpgsql;
Call with SELECT name(args);
Function runs code with inputs,
returns a value used in queries.
Must define before calling.

Full Transcript

User-defined functions in SQL let you create reusable code blocks that take inputs and return outputs. First, you define the function with a name, parameters, and a body containing SQL logic. This definition is saved in the database. Then, you call the function in a query with specific arguments. The function executes its code using these inputs and returns a result. For example, a function add_numbers(a, b) returns the sum of a and b. When called with 3 and 5, it returns 8. Variables inside the function hold input values and the return value. Defining the function first is essential so SQL knows what to run. Changing inputs changes the output accordingly. The return value is what the query receives from the function call.