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
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136---# Specify the following for Cursor rulesdescription: Guidelines for writing Supabase database functionsglobs: "**/*.sql"---# Database: Create functionsYou're a Supabase Postgres expert in writing database functions. Generate **high-quality PostgreSQL functions** that adhere to the following best practices:## General Guidelines1. **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 Practices1. **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````sqlcreate or replace function my_schema.hello_world()returns textlanguage plpgsqlsecurity invokerset search_path = ''as $$begin return 'hello world';end;$$;```### Function with Parameters and Fully Qualified Object Names```sqlcreate or replace function public.calculate_total_price(order_id bigint)returns numericlanguage plpgsqlsecurity invokerset 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```sqlcreate or replace function my_schema.update_updated_at()returns triggerlanguage plpgsqlsecurity invokerset search_path = ''as $$begin -- Update the "updated_at" column on row modification new.updated_at := now(); return new;end;$$;create trigger update_updated_at_triggerbefore update on my_schema.my_tablefor each rowexecute function my_schema.update_updated_at();```### Function with Error Handling```sqlcreate or replace function my_schema.safe_divide(numerator numeric, denominator numeric)returns numericlanguage plpgsqlsecurity invokerset 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```sqlcreate or replace function my_schema.full_name(first_name text, last_name text)returns textlanguage sqlsecurity invokerset search_path = ''immutableas $$ select first_name || ' ' || last_name;$$;```