Postgres functions basics in Supabase - Deep Dive

Overview - Postgres functions basics

What is it?

Postgres functions are reusable blocks of code stored inside the database that perform specific tasks. They can take inputs, process data, and return results. These functions help automate repetitive tasks and complex operations directly within the database.

Why it matters

Without functions, every time you want to perform a common task, you'd have to write the same code again and again, which wastes time and risks mistakes. Functions make your database smarter and faster by handling logic close to the data, reducing errors and improving performance.

Where it fits

Before learning Postgres functions, you should understand basic SQL queries and database tables. After mastering functions, you can explore triggers, stored procedures, and advanced database programming to build powerful data-driven applications.

Mental Model

Core Idea

A Postgres function is like a mini-machine inside the database that takes inputs, does work, and gives back results whenever you ask.

Think of it like...

Imagine a coffee machine: you put in coffee beans and water (inputs), press a button (call the function), and it makes coffee (output) for you. You don’t need to know how it works inside, just that it reliably gives you coffee when you want.

┌───────────────┐
│   Inputs      │
│ (parameters)  │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│  Function     │
│  (code block) │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│   Output      │
│ (result/data) │
└───────────────┘

Build-Up - 6 Steps

1

Foundation

What is a Postgres function?

Concept: Introducing the idea of a function as a reusable code block inside the database.

A Postgres function is a named set of instructions stored in the database. You can call it anytime to perform a task, like calculating a value or modifying data. Functions can accept inputs called parameters and return outputs.

Result

You understand that functions help avoid repeating code and keep logic inside the database.

Knowing that functions live inside the database helps you see how they speed up work by reducing repeated effort.

2

Foundation

Basic syntax of a Postgres function

Concept: Learning the simple structure to create a function in Postgres.

A basic function has a name, input parameters, a return type, and a body with SQL or procedural code. For example: CREATE FUNCTION add_numbers(a integer, b integer) RETURNS integer AS $$ BEGIN RETURN a + b; END; $$ LANGUAGE plpgsql; This function adds two numbers and returns the sum.

Result

You can write a simple function that takes inputs and returns a result.

Understanding the syntax is key to creating your own functions and customizing database behavior.

3

Intermediate

Using parameters and return types

🤔Before reading on: do you think a function can return multiple values or only one? Commit to your answer.

Concept: Exploring how functions accept inputs and what kinds of outputs they can produce.

Functions can take zero or more parameters of various types (numbers, text, dates). They must declare what type of data they return, like a single value, a table, or void (no return). For example, a function can return a whole table of rows using RETURNS TABLE.

Result

You know how to design functions that fit your data needs, whether returning one value or many.

Knowing how to use parameters and return types lets you build flexible functions that solve real problems.

4

Intermediate

Calling and using functions in queries

🤔Before reading on: do you think you can use functions inside SELECT statements like normal columns? Commit to your answer.

Concept: How to run functions and use their results in SQL queries.

You call functions by name with parentheses and arguments, like SELECT add_numbers(3, 5);. Functions can be used in SELECT, WHERE, or other clauses to compute values on the fly. For example: SELECT id, add_numbers(score1, score2) AS total_score FROM players; This adds two scores for each player.

Result

You can integrate functions into your SQL queries to automate calculations and logic.

Using functions inside queries makes your SQL more powerful and reusable.

5

Advanced

Function languages and procedural code

🤔Before reading on: do you think Postgres functions can only use SQL, or can they include other programming logic? Commit to your answer.

Concept: Postgres supports multiple languages for functions, including SQL and PL/pgSQL for procedural logic.

Besides plain SQL, Postgres functions can be written in PL/pgSQL, which allows variables, loops, conditions, and complex logic. For example: CREATE FUNCTION check_age(age integer) RETURNS text AS $$ BEGIN IF age >= 18 THEN RETURN 'Adult'; ELSE RETURN 'Minor'; END IF; END; $$ LANGUAGE plpgsql; This function uses IF statements to decide output.

Result

You can write smarter functions that do more than simple queries, adding logic inside the database.

Knowing about procedural languages unlocks the ability to build complex workflows inside Postgres.

6

Expert

Performance and security considerations

🤔Before reading on: do you think functions always run faster than equivalent client-side code? Commit to your answer.

Concept: Understanding how functions affect database performance and security in real systems.

Functions run inside the database, which can reduce data transfer and speed up operations. However, poorly written functions can slow queries or cause locking issues. Functions can also have security settings controlling who can run them and what they can access. Using IMMUTABLE or STABLE keywords helps the planner optimize calls.

Result

You can write efficient, safe functions that improve your application's reliability and speed.

Knowing the tradeoffs of functions helps avoid common pitfalls and ensures your database stays fast and secure.

Under the Hood

Postgres stores functions as database objects with their code saved in system catalogs. When called, the database engine parses the function, executes its code inside the server process, and returns the result. Procedural languages like PL/pgSQL are interpreted by the Postgres engine, allowing control flow and variables. Functions run within transactions and can affect or read data directly.

Why designed this way?

Functions were designed to keep logic close to data, reducing network overhead and improving consistency. Early databases had limited programmability, so Postgres introduced procedural languages to allow complex operations inside the server. This design balances flexibility with performance and security by controlling execution context and permissions.

┌───────────────┐
│ Client Query  │
└──────┬────────┘
       │ Calls function
       ▼
┌───────────────┐
│ Postgres Core │
│  Engine       │
└──────┬────────┘
       │ Executes function code
       ▼
┌───────────────┐
│ Function Code │
│ (SQL/PLpgSQL) │
└──────┬────────┘
       │ Reads/Writes Data
       ▼
┌───────────────┐
│ Data Storage  │
│ (Tables/Rows) │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Do you think Postgres functions always improve performance? Commit to yes or no.

Common Belief: Functions always make queries faster because they run inside the database.

Reality: Functions can improve performance by reducing data transfer, but complex or inefficient functions can slow down queries or cause locking issues.

Why it matters: Assuming functions always speed things up can lead to slow applications and hard-to-debug performance problems.

Quick: Can Postgres functions return multiple rows like a table? Commit to yes or no.

Common Belief: Functions can only return a single value, never multiple rows.

Reality: Functions can return sets of rows or entire tables using RETURNS TABLE or SETOF types.

Why it matters: Not knowing this limits how you design functions and misses powerful ways to encapsulate complex queries.

Quick: Do you think you can write Postgres functions only in SQL? Commit to yes or no.

Common Belief: Postgres functions can only be written in SQL language.

Reality: Postgres supports multiple languages for functions, including PL/pgSQL, which allows procedural logic like loops and conditions.

Why it matters: Believing this restricts you from writing more powerful and flexible functions that handle complex logic.

Quick: Do you think functions automatically handle security for you? Commit to yes or no.

Common Belief: Functions run with the caller's permissions and don't need special security settings.

Reality: Functions can run with the creator's permissions (SECURITY DEFINER) or caller's permissions (SECURITY INVOKER), affecting access control.

Why it matters: Ignoring this can cause security holes or unexpected permission errors in your applications.

Expert Zone

1

Functions marked IMMUTABLE tell Postgres they always return the same result for the same inputs, enabling query optimizations.

2

Using LANGUAGE SQL functions is faster for simple queries, but PL/pgSQL is better for complex logic and control flow.

3

Security DEFINER functions can elevate privileges safely if carefully written, allowing controlled access to sensitive data.

When NOT to use

Avoid using functions for very simple queries that can be written inline, as function call overhead may add latency. For complex workflows, consider using stored procedures or application code for better control. Also, avoid functions that perform heavy computations better suited for specialized processing outside the database.

Production Patterns

In production, functions are used to encapsulate business logic like data validation, calculations, and audit logging. They help maintain consistency and reduce duplication across applications. Functions combined with triggers automate reactions to data changes, enabling reactive systems. Performance tuning often involves marking functions IMMUTABLE or STABLE and minimizing side effects.

Connections

Microservices

Both encapsulate logic into reusable units that can be called independently.

Understanding functions as mini-machines inside the database helps grasp how microservices isolate and expose functionality in distributed systems.

Mathematical Functions

Postgres functions are inspired by mathematical functions that take inputs and produce outputs deterministically.

Knowing this connection clarifies why functions have input parameters and return types, and why some are marked immutable.

Factory Automation

Functions automate repetitive tasks inside the database like machines automate production lines.

Seeing functions as automation tools helps appreciate their role in reducing manual work and errors in data handling.

Common Pitfalls

#1 Writing functions that perform heavy data processing inefficiently inside the database.

Wrong approach:

CREATE FUNCTION slow_sum() RETURNS integer AS $$
DECLARE
total integer := 0;
BEGIN
FOR rec IN SELECT * FROM big_table LOOP
total := total + rec.value;
END LOOP;
RETURN total;
END;
$$ LANGUAGE plpgsql;

Correct approach:

SELECT SUM(value) FROM big_table;

Root cause:

Misunderstanding that set-based SQL operations are faster than row-by-row loops inside functions.

#2 Not specifying function volatility leading to poor query plans.

Wrong approach:

CREATE FUNCTION get_time() RETURNS timestamp AS $$ SELECT now(); $$ LANGUAGE SQL;

Correct approach:

CREATE FUNCTION get_time() RETURNS timestamp AS $$ SELECT now(); $$ LANGUAGE SQL VOLATILE;

Root cause:

Assuming default volatility is correct; Postgres needs explicit volatility to optimize queries properly.

#3 Using SECURITY DEFINER without restricting function access.

Wrong approach:

CREATE FUNCTION secret_data() RETURNS text AS $$ SELECT secret FROM secrets; $$ LANGUAGE SQL SECURITY DEFINER;

Correct approach:

CREATE FUNCTION secret_data() RETURNS text AS $$ SELECT secret FROM secrets; $$ LANGUAGE SQL SECURITY DEFINER;
REVOKE EXECUTE ON FUNCTION secret_data() FROM public;
GRANT EXECUTE ON FUNCTION secret_data() TO trusted_role;

Root cause:

Not managing permissions properly when elevating privileges, risking unauthorized data access.

Key Takeaways

Postgres functions are reusable code blocks inside the database that take inputs and return outputs, helping automate and simplify data tasks.

Functions can be written in SQL or procedural languages like PL/pgSQL, allowing simple queries or complex logic with control flow.

Using functions inside queries makes your SQL more powerful, but writing efficient functions requires understanding performance and security implications.

Functions can return single values, multiple rows, or tables, making them flexible tools for many database operations.

Knowing when and how to use functions, including their limitations, is key to building fast, secure, and maintainable database applications.

Practice

1. What is the main purpose of a Postgres function in Supabase?

easy

A. To store reusable code inside the database

B. To create user interface elements

C. To manage server hardware

D. To backup the entire database automatically

Solution

1. Step 1: Understand what Postgres functions do

   Postgres functions hold code that can be run repeatedly inside the database.

2. Step 2: Compare options with this purpose

   Only To store reusable code inside the database matches this description; others relate to UI, hardware, or backups, which are unrelated.

3. Final Answer:

   To store reusable code inside the database -> Option A

4. Quick Check:

   Postgres functions = reusable database code [OK]

Hint: Functions store code to reuse inside the database [OK]

Common Mistakes:

• Confusing functions with UI components
• Thinking functions manage hardware
• Assuming functions handle backups automatically

2. Which of the following is the correct way to start defining a simple Postgres function?

easy

A. FUNCTION CREATE function_name() RETURNS type AS BEGIN ... END;

B. MAKE FUNCTION function_name RETURNS type { ... }

C. CREATE FUNCTION function_name() RETURNS type AS $$ BEGIN ... END; $$ LANGUAGE plpgsql;

D. DEFINE FUNCTION function_name() RETURNS type BEGIN ... END;

Solution

1. Step 1: Recall Postgres function syntax

   The correct syntax starts with CREATE FUNCTION, includes RETURNS, and uses $$ to wrap the body.

2. Step 2: Match options to syntax

   Only CREATE FUNCTION function_name() RETURNS type AS $$ BEGIN ... END; $$ LANGUAGE plpgsql; matches the exact syntax; others use incorrect keywords or order.

3. Final Answer:

   CREATE FUNCTION function_name() RETURNS type AS $$ BEGIN ... END; $$ LANGUAGE plpgsql; -> Option C

4. Quick Check:

   CREATE FUNCTION + RETURNS + $$ ... $$ = correct syntax [OK]

Hint: Postgres functions start with CREATE FUNCTION and use $$ for body [OK]

Common Mistakes:

• Using MAKE or DEFINE instead of CREATE
• Omitting LANGUAGE plpgsql
• Not wrapping function body with $$

3. Given this function, what will SELECT add_two_numbers(3, 4); return?

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

medium

A. NULL

B. 34

C. Error: function not found

D. 7

Solution

1. Step 1: Understand function behavior

   The function adds two integers a and b and returns their sum.

2. Step 2: Calculate the result for inputs 3 and 4

   3 + 4 equals 7, so the function returns 7.

3. Final Answer:

   7 -> Option D

4. Quick Check:

   3 + 4 = 7 [OK]

Hint: Function adds inputs; 3 + 4 = 7 [OK]

Common Mistakes:

• Concatenating numbers as strings (34)
• Assuming function is missing or returns NULL
• Confusing syntax errors with output

4. Identify the error in this Postgres function:

CREATE FUNCTION multiply_by_two(x integer) RETURNS integer AS $$ BEGIN RETURN x * 2 END; $$ LANGUAGE plpgsql;

medium

A. Missing semicolon after RETURN statement

B. Incorrect function name

C. Wrong language specified

D. No error, function is correct

Solution

1. Step 1: Check function syntax carefully

   In PL/pgSQL, statements inside BEGIN...END must end with semicolons.

2. Step 2: Locate missing semicolon

   The RETURN statement lacks a semicolon at the end, causing a syntax error.

3. Final Answer:

   Missing semicolon after RETURN statement -> Option A

4. Quick Check:

   Statements inside BEGIN...END need semicolons [OK]

Hint: Check for missing semicolons inside BEGIN...END [OK]

Common Mistakes:

• Forgetting semicolon after RETURN
• Assuming function name or language is wrong
• Thinking function is correct without semicolon

5. You want to create a Postgres function that returns the length of a text input only if it is not empty; otherwise, it returns NULL. Which function definition correctly implements this?

hard

A. CREATE FUNCTION text_length_if_not_empty(input text) RETURNS integer AS $$ BEGIN RETURN length(input); END; $$ LANGUAGE plpgsql;

B. CREATE FUNCTION text_length_if_not_empty(input text) RETURNS integer AS $$ BEGIN IF input = '' THEN RETURN NULL; ELSE RETURN length(input); END IF; END; $$ LANGUAGE plpgsql;

C. CREATE FUNCTION text_length_if_not_empty(input text) RETURNS integer AS $$ BEGIN IF input IS NULL THEN RETURN 0; ELSE RETURN length(input); END IF; END; $$ LANGUAGE plpgsql;

D. CREATE FUNCTION text_length_if_not_empty(input text) RETURNS integer AS $$ BEGIN IF input = NULL THEN RETURN NULL; ELSE RETURN length(input); END IF; END; $$ LANGUAGE plpgsql;

Solution

1. Step 1: Understand the condition to check empty string

   The function must check if input equals empty string ('') and return NULL in that case.

2. Step 2: Evaluate each option's condition

   CREATE FUNCTION text_length_if_not_empty(input text) RETURNS integer AS $$ BEGIN IF input = '' THEN RETURN NULL; ELSE RETURN length(input); END IF; END; $$ LANGUAGE plpgsql; correctly checks input = '' and returns NULL; CREATE FUNCTION text_length_if_not_empty(input text) RETURNS integer AS $$ BEGIN RETURN length(input); END; $$ LANGUAGE plpgsql; always returns length; CREATE FUNCTION text_length_if_not_empty(input text) RETURNS integer AS $$ BEGIN IF input IS NULL THEN RETURN 0; ELSE RETURN length(input); END IF; END; $$ LANGUAGE plpgsql; checks NULL instead of empty; CREATE FUNCTION text_length_if_not_empty(input text) RETURNS integer AS $$ BEGIN IF input = NULL THEN RETURN NULL; ELSE RETURN length(input); END IF; END; $$ LANGUAGE plpgsql; uses incorrect NULL comparison.

3. Final Answer:

   CREATE FUNCTION text_length_if_not_empty(input text) RETURNS integer AS $$ BEGIN IF input = '' THEN RETURN NULL; ELSE RETURN length(input); END IF; END; $$ LANGUAGE plpgsql; -> Option B

4. Quick Check:

   Check empty string with = '' and return NULL [OK]

Hint: Use IF input = '' THEN RETURN NULL; ELSE RETURN length(input); [OK]

Common Mistakes:

• Using input = NULL instead of IS NULL
• Not checking for empty string
• Returning 0 instead of NULL for empty input