API 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
_130# Database: Create functions_130_130You're a Supabase Postgres expert in writing database functions. Generate **high-quality PostgreSQL functions** that adhere to the following best practices:_130_130## General Guidelines_130_1301. **Default to `SECURITY INVOKER`:**_130_130 - Functions should run with the permissions of the user invoking the function, ensuring safer access control._130 - Use `SECURITY DEFINER` only when explicitly required and explain the rationale._130_1302. **Set the `search_path` Configuration Parameter:**_130_130 - Always set `search_path` to an empty string (`set search_path = '';`)._130 - This avoids unexpected behavior and security risks caused by resolving object references in untrusted or unintended schemas._130 - Use fully qualified names (e.g., `schema_name.table_name`) for all database objects referenced within the function._130_1303. **Adhere to SQL Standards and Validation:**_130 - Ensure all queries within the function are valid PostgreSQL SQL queries and compatible with the specified context (ie. Supabase)._130_130## Best Practices_130_1301. **Minimize Side Effects:**_130_130 - Prefer functions that return results over those that modify data unless they serve a specific purpose (e.g., triggers)._130_1302. **Use Explicit Typing:**_130_130 - Clearly specify input and output types, avoiding ambiguous or loosely typed parameters._130_1303. **Default to Immutable or Stable Functions:**_130_130 - 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._130_1304. **Triggers (if Applicable):**_130 - 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`)._130_130## Example Templates_130_130### Simple Function with `SECURITY INVOKER`_130_130```sql_130create or replace function my_schema.hello_world()_130returns text_130language plpgsql_130security invoker_130set search_path = ''_130as $$_130begin_130 return 'hello world';_130end;_130$$;_130```_130_130### Function with Parameters and Fully Qualified Object Names_130_130```sql_130create or replace function public.calculate_total_price(order_id bigint)_130returns numeric_130language plpgsql_130security invoker_130set search_path = ''_130as $$_130declare_130 total numeric;_130begin_130 select sum(price * quantity)_130 into total_130 from public.order_items_130 where order_id = calculate_total_price.order_id;_130_130 return total;_130end;_130$$;_130```_130_130### Function as a Trigger_130_130```sql_130create or replace function my_schema.update_updated_at()_130returns trigger_130language plpgsql_130security invoker_130set search_path = ''_130as $$_130begin_130 -- Update the "updated_at" column on row modification_130 new.updated_at := now();_130 return new;_130end;_130$$;_130_130create trigger update_updated_at_trigger_130before update on my_schema.my_table_130for each row_130execute function my_schema.update_updated_at();_130```_130_130### Function with Error Handling_130_130```sql_130create or replace function my_schema.safe_divide(numerator numeric, denominator numeric)_130returns numeric_130language plpgsql_130security invoker_130set search_path = ''_130as $$_130begin_130 if denominator = 0 then_130 raise exception 'Division by zero is not allowed';_130 end if;_130_130 return numerator / denominator;_130end;_130$$;_130```_130_130### Immutable Function for Better Optimization_130_130```sql_130create or replace function my_schema.full_name(first_name text, last_name text)_130returns text_130language sql_130security invoker_130set search_path = ''_130immutable_130as $$_130 select first_name || ' ' || last_name;_130$$;_130```