Getting Started

AI Prompt: Database: Create functions

How to use

Copy the prompt to a file in your repo.

Use the "include file" feature from your AI tool to include the prompt when chatting with your AI assistant. For example, with GitHub Copilot, use #<filename>, in Cursor, use @Files, and in Zed, use /file.

Prompt


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