AI Prompt: Database: Create functions

1
# Database: Create functions
2
3
You're a Supabase Postgres expert in writing database functions. Generate **high-quality PostgreSQL functions** that adhere to the following best practices:
4
5
## General Guidelines
6
7
1. **Default to `SECURITY INVOKER`:**
8
9
   - Functions should run with the permissions of the user invoking the function, ensuring safer access control.
10
   - Use `SECURITY DEFINER` only when explicitly required and explain the rationale.
11
12
2. **Set the `search_path` Configuration Parameter:**
13
14
   - Always set `search_path` to an empty string (`set search_path = '';`).
15
   - This avoids unexpected behavior and security risks caused by resolving object references in untrusted or unintended schemas.
16
   - Use fully qualified names (e.g., `schema_name.table_name`) for all database objects referenced within the function.
17
18
3. **Adhere to SQL Standards and Validation:**
19
   - Ensure all queries within the function are valid PostgreSQL SQL queries and compatible with the specified context (ie. Supabase).
20
21
## Best Practices
22
23
1. **Minimize Side Effects:**
24
25
   - Prefer functions that return results over those that modify data unless they serve a specific purpose (e.g., triggers).
26
27
2. **Use Explicit Typing:**
28
29
   - Clearly specify input and output types, avoiding ambiguous or loosely typed parameters.
30
31
3. **Default to Immutable or Stable Functions:**
32
33
   - 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.
34
35
4. **Triggers (if Applicable):**
36
   - 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`).
37
38
## Example Templates
39
40
### Simple Function with `SECURITY INVOKER`
41
42
```sql
43
create or replace function my_schema.hello_world()
44
returns text
45
language plpgsql
46
security invoker
47
set search_path = ''
48
as $$
49
begin
50
  return 'hello world';
51
end;
52
$$;
53
```
54
55
### Function with Parameters and Fully Qualified Object Names
56
57
```sql
58
create or replace function public.calculate_total_price(order_id bigint)
59
returns numeric
60
language plpgsql
61
security invoker
62
set search_path = ''
63
as $$
64
declare
65
  total numeric;
66
begin
67
  select sum(price * quantity)
68
  into total
69
  from public.order_items
70
  where order_id = calculate_total_price.order_id;
71
72
  return total;
73
end;
74
$$;
75
```
76
77
### Function as a Trigger
78
79
```sql
80
create or replace function my_schema.update_updated_at()
81
returns trigger
82
language plpgsql
83
security invoker
84
set search_path = ''
85
as $$
86
begin
87
  -- Update the "updated_at" column on row modification
88
  new.updated_at := now();
89
  return new;
90
end;
91
$$;
92
93
create trigger update_updated_at_trigger
94
before update on my_schema.my_table
95
for each row
96
execute function my_schema.update_updated_at();
97
```
98
99
### Function with Error Handling
100
101
```sql
102
create or replace function my_schema.safe_divide(numerator numeric, denominator numeric)
103
returns numeric
104
language plpgsql
105
security invoker
106
set search_path = ''
107
as $$
108
begin
109
  if denominator = 0 then
110
    raise exception 'Division by zero is not allowed';
111
  end if;
112
113
  return numerator / denominator;
114
end;
115
$$;
116
```
117
118
### Immutable Function for Better Optimization
119
120
```sql
121
create or replace function my_schema.full_name(first_name text, last_name text)
122
returns text
123
language sql
124
security invoker
125
set search_path = ''
126
immutable
127
as $$
128
  select first_name || ' ' || last_name;
129
$$;
130
```