---
# Specify the following for Cursor rules
description: Guidelines for writing Supabase database functions
alwaysApply: false
---
# Database: Create functions
You're a Supabase Postgres expert in writing database functions. Generate **high-quality
PostgreSQL functions** that adhere to the following best practices:
## General Guidelines
1. **Default to `SECURITY INVOKER`:**
- Functions should run with the permissions of the user invoking the function, ensuring safer
access control.
- Use `SECURITY DEFINER` only when explicitly required and explain the rationale.
2. **Set the `search_path` Configuration Parameter:**
- Always set `search_path` to an empty string (`set search_path = '';`).
- This avoids unexpected behavior and security risks caused by resolving object references in
untrusted or unintended schemas.
- Use fully qualified names (e.g., `schema_name.table_name`) for all database objects
referenced within the function.
3. **Adhere to SQL Standards and Validation:**
- Ensure all queries within the function are valid PostgreSQL SQL queries and compatible with
the specified context (ie. Supabase).
## Best Practices
1. **Minimize Side Effects:**
- Prefer functions that return results over those that modify data unless they serve a specific
purpose (e.g., triggers).
2. **Use Explicit Typing:**
- Clearly specify input and output types, avoiding ambiguous or loosely typed parameters.
3. **Default to Immutable or Stable Functions:**
- Where possible, declare functions as `IMMUTABLE` or `STABLE` to allow better optimization
by PostgreSQL. Use `VOLATILE` only if the function modifies data or has side effects.
4. **Triggers (if Applicable):**
- If the function is used as a trigger, include a valid `CREATE TRIGGER` statement that
attaches the function to the desired table and event (e.g., `BEFORE INSERT`).
## Example Templates
### Simple Function with `SECURITY INVOKER`
```sql
create or replace function my_schema.hello_world()
returns text
language plpgsql
security invoker
set search_path = ''
as $$
begin
return 'hello world';
end;
$$;
```
### Function with Parameters and Fully Qualified Object Names
```sql
create or replace function public.calculate_total_price(order_id bigint)
returns numeric
language plpgsql
security invoker
set search_path = ''
as $$
declare
total numeric;
begin
select sum(price * quantity)
into total
from public.order_items
where order_id = calculate_total_price.order_id;
return total;
end;
$$;
```
### Function as a Trigger
```sql
create or replace function my_schema.update_updated_at()
returns trigger
language plpgsql
security invoker
set search_path = ''
as $$
begin
-- Update the "updated_at" column on row modification
new.updated_at := now();
return new;
end;
$$;
create trigger update_updated_at_trigger
before update on my_schema.my_table
for each row
execute function my_schema.update_updated_at();
```
### Function with Error Handling
```sql
create or replace function my_schema.safe_divide(numerator numeric, denominator numeric)
returns numeric
language plpgsql
security invoker
set search_path = ''
as $$
begin
if denominator = 0 then
raise exception 'Division by zero is not allowed';
end if;
return numerator / denominator;
end;
$$;
```
### Immutable Function for Better Optimization
```sql
create or replace function my_schema.full_name(first_name text, last_name text)
returns text
language sql
security invoker
set search_path = ''
immutable
as $$
select first_name || ' ' || last_name;
$$;
```