AI Prompt: Specify the following for Cursor rules
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
_134---_134# Specify the following for Cursor rules_134description: Guidelines for writing Supabase database functions_134globs: "**/*.sql"_134---_134_134You're a Supabase Postgres expert in writing database functions. Generate **high-quality PostgreSQL functions** that adhere to the following best practices:_134_134## General Guidelines_134_1341. **Default to `SECURITY INVOKER`:**_134_134 - Functions should run with the permissions of the user invoking the function, ensuring safer access control._134 - Use `SECURITY DEFINER` only when explicitly required and explain the rationale._134_1342. **Set the `search_path` Configuration Parameter:**_134_134 - Always set `search_path` to an empty string (`set search_path = '';`)._134 - This avoids unexpected behavior and security risks caused by resolving object references in untrusted or unintended schemas._134 - Use fully qualified names (e.g., `schema_name.table_name`) for all database objects referenced within the function._134_1343. **Adhere to SQL Standards and Validation:**_134 - Ensure all queries within the function are valid PostgreSQL SQL queries and compatible with the specified context (ie. Supabase)._134_134## Best Practices_134_1341. **Minimize Side Effects:**_134_134 - Prefer functions that return results over those that modify data unless they serve a specific purpose (e.g., triggers)._134_1342. **Use Explicit Typing:**_134_134 - Clearly specify input and output types, avoiding ambiguous or loosely typed parameters._134_1343. **Default to Immutable or Stable Functions:**_134_134 - 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._134_1344. **Triggers (if Applicable):**_134 - 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`)._134_134## Example Templates_134_134### Simple Function with `SECURITY INVOKER`_134_134```sql_134create or replace function my_schema.hello_world()_134returns text_134language plpgsql_134security invoker_134set search_path = ''_134as $$_134begin_134 return 'hello world';_134end;_134$$;_134```_134_134### Function with Parameters and Fully Qualified Object Names_134_134```sql_134create or replace function public.calculate_total_price(order_id bigint)_134returns numeric_134language plpgsql_134security invoker_134set search_path = ''_134as $$_134declare_134 total numeric;_134begin_134 select sum(price * quantity)_134 into total_134 from public.order_items_134 where order_id = calculate_total_price.order_id;_134_134 return total;_134end;_134$$;_134```_134_134### Function as a Trigger_134_134```sql_134create or replace function my_schema.update_updated_at()_134returns trigger_134language plpgsql_134security invoker_134set search_path = ''_134as $$_134begin_134 -- Update the "updated_at" column on row modification_134 new.updated_at := now();_134 return new;_134end;_134$$;_134_134create trigger update_updated_at_trigger_134before update on my_schema.my_table_134for each row_134execute function my_schema.update_updated_at();_134```_134_134### Function with Error Handling_134_134```sql_134create or replace function my_schema.safe_divide(numerator numeric, denominator numeric)_134returns numeric_134language plpgsql_134security invoker_134set search_path = ''_134as $$_134begin_134 if denominator = 0 then_134 raise exception 'Division by zero is not allowed';_134 end if;_134_134 return numerator / denominator;_134end;_134$$;_134```_134_134### Immutable Function for Better Optimization_134_134```sql_134create or replace function my_schema.full_name(first_name text, last_name text)_134returns text_134language sql_134security invoker_134set search_path = ''_134immutable_134as $$_134 select first_name || ' ' || last_name;_134$$;_134```