0
# Stored Procedures
1

2
Remote procedure call (RPC) functionality for executing PostgreSQL stored procedures and functions with parameter handling and result processing.
3

4
## Capabilities
5

6
### Remote Procedure Calls
7

8
Execute PostgreSQL stored procedures and functions remotely through PostgREST.
9

10
```typescript { .api }
11
/**
12
 * Perform a function call
13
 * @param fn - The function name to call
14
 * @param args - The arguments to pass to the function call
15
 * @param options - Named parameters
16
 * @param options.head - When set to `true`, `data` will not be returned. Useful if you only need the count
17
 * @param options.get - When set to `true`, the function will be called with read-only access mode
18
 * @param options.count - Count algorithm to use to count rows returned by the function. Only applicable for set-returning functions
19
 */
20
rpc<FnName extends string & keyof Schema['Functions'], Fn extends Schema['Functions'][FnName]>(
21
  fn: FnName,
22
  args?: Fn['Args'],
23
  options?: {
24
    head?: boolean;
25
    get?: boolean;
26
    count?: 'exact' | 'planned' | 'estimated';
27
  }
28
): PostgrestFilterBuilder<...>;
29
```
30

31
**Usage Examples:**
32

33
```typescript
34
import { PostgrestClient } from "@supabase/postgrest-js";
35

36
const client = new PostgrestClient("https://api.example.com");
37

38
// Simple function call with no arguments
39
const { data, error } = await client
40
  .rpc("get_current_timestamp");
41

42
// Function call with arguments
43
const { data: userStats } = await client
44
  .rpc("calculate_user_stats", { 
45
    user_id: 123,
46
    start_date: "2023-01-01",
47
    end_date: "2023-12-31"
48
  });
49

50
// Function call with type safety (when using typed schema)
51
interface Database {
52
  public: {
53
    Functions: {
54
      get_user_posts: {
55
        Args: { user_id: number; limit?: number };
56
        Returns: { id: number; title: string; created_at: string }[];
57
      };
58
      calculate_metrics: {
59
        Args: { date_range: string };
60
        Returns: { total_users: number; active_users: number };
61
      };
62
    };
63
  };
64
}
65

66
const typedClient = new PostgrestClient<Database>("https://api.example.com");
67

68
const { data: posts } = await typedClient
69
  .rpc("get_user_posts", { user_id: 456, limit: 10 });
70
// posts is fully typed based on the Returns type
71
```
72

73
### Function Call Methods
74

75
Control how functions are executed with different HTTP methods.
76

77
```typescript { .api }
78
// POST method (default) - for functions that modify data
79
rpc(fn: string, args?: object): PostgrestFilterBuilder<...>;
80

81
// GET method - for read-only functions  
82
rpc(fn: string, args?: object, { get: true }): PostgrestFilterBuilder<...>;
83

84
// HEAD method - for count-only operations
85
rpc(fn: string, args?: object, { head: true }): PostgrestFilterBuilder<...>;
86
```
87

88
**Usage Examples:**
89

90
```typescript
91
// Default POST method for data-modifying functions
92
const { data } = await client
93
  .rpc("create_user_report", { 
94
    user_id: 123,
95
    report_type: "monthly" 
96
  });
97

98
// GET method for read-only functions (better caching, cleaner logs)
99
const { data: readOnlyData } = await client
100
  .rpc("get_statistics", { 
101
    category: "sales",
102
    year: 2023 
103
  }, { get: true });
104

105
// HEAD method when you only need count information
106
const { count } = await client
107
  .rpc("get_large_dataset", { 
108
    filter_criteria: "active" 
109
  }, { 
110
    head: true, 
111
    count: 'exact' 
112
  });
113

114
// GET method with query parameters in URL
115
const { data: cachedResults } = await client
116
  .rpc("get_cached_report", { 
117
    report_id: "monthly_2023",
118
    format: "summary" 
119
  }, { get: true });
120
```
121

122
### Set-Returning Functions
123

124
Handle functions that return multiple rows with counting and filtering capabilities.
125

126
```typescript { .api }
127
/**
128
 * For set-returning functions, you can use count algorithms and apply filters
129
 * to the returned data
130
 */
131
rpc(
132
  fn: string, 
133
  args?: object, 
134
  options?: { count?: 'exact' | 'planned' | 'estimated' }
135
): PostgrestFilterBuilder<...>;
136
```
137

138
**Usage Examples:**
139

140
```typescript
141
// Set-returning function with count
142
const { data: results, count } = await client
143
  .rpc("get_user_activity", { 
144
    date_range: "[2023-01-01,2023-12-31]" 
145
  }, { count: 'exact' });
146

147
// Filter results from set-returning function
148
const { data: filteredResults } = await client
149
  .rpc("get_all_transactions", { user_id: 123 })
150
  .gte("amount", 100)
151
  .eq("status", "completed")
152
  .order("created_at", { ascending: false })
153
  .limit(50);
154

155
// Complex filtering on function results
156
const { data: processedData } = await client
157
  .rpc("analyze_user_behavior", { 
158
    analysis_type: "engagement",
159
    time_period: "last_30_days" 
160
  })
161
  .select("user_id, engagement_score, last_active")
162
  .gt("engagement_score", 0.5)
163
  .order("engagement_score", { ascending: false })
164
  .range(0, 99); // Top 100 users
165

166
// Count-only for large datasets
167
const { count: totalRecords } = await client
168
  .rpc("get_audit_logs", { 
169
    table_name: "users",
170
    action_type: "UPDATE" 
171
  }, { 
172
    head: true, 
173
    count: 'planned' // Fast approximate count
174
  });
175
```
176

177
### Function Parameter Handling
178

179
Handle different parameter types and optional parameters.
180

181
**Usage Examples:**
182

183
```typescript
184
// Function with optional parameters
185
const { data } = await client
186
  .rpc("search_users", { 
187
    query: "john",
188
    limit: 20,        // Optional parameter
189
    offset: 0         // Optional parameter
190
  });
191

192
// Function with complex parameter types
193
const { data: analysisResult } = await client
194
  .rpc("complex_analysis", {
195
    data_points: [1, 2, 3, 4, 5],           // Array parameter
196
    config: {                                // Object parameter
197
      include_metadata: true,
198
      calculation_method: "weighted_average"
199
    },
200
    tags: ["analytics", "monthly"],          // String array
201
    threshold: 0.75                          // Numeric parameter
202
  });
203

204
// Function with date/time parameters
205
const { data: timeBasedData } = await client
206
  .rpc("get_time_series_data", {
207
    start_time: "2023-01-01T00:00:00Z",
208
    end_time: "2023-12-31T23:59:59Z",
209
    interval: "1 day",
210
    timezone: "UTC"
211
  });
212

213
// Function with JSON parameters
214
const { data: jsonResults } = await client
215
  .rpc("process_json_data", {
216
    json_input: {
217
      filters: { status: "active", category: "premium" },
218
      sorting: { field: "created_at", direction: "desc" },
219
      pagination: { page: 1, size: 50 }
220
    }
221
  });
222

223
// Function with no parameters (empty object or omit args)
224
const { data: noParams1 } = await client.rpc("get_system_status");
225
const { data: noParams2 } = await client.rpc("get_system_status", {});
226
```
227

228
### Error Handling in RPC
229

230
Handle function execution errors and parameter validation.
231

232
**Usage Examples:**
233

234
```typescript
235
// Function call with error handling
236
const { data, error } = await client
237
  .rpc("validate_and_process", { 
238
    input_data: "some data" 
239
  });
240

241
if (error) {
242
  console.error("Function execution failed:", error.message);
243
  console.error("Error details:", error.details);
244
  console.error("Error code:", error.code);
245
  // Handle specific error cases
246
  if (error.code === "42883") {
247
    console.log("Function does not exist");
248
  }
249
}
250

251
// Function with validation
252
const { data: validatedData, error: validationError } = await client
253
  .rpc("strict_data_validator", {
254
    email: "user@example.com",
255
    age: 25,
256
    preferences: { newsletter: true }
257
  });
258

259
// Throwing errors instead of returning them
260
const { data: strictData } = await client
261
  .rpc("critical_operation", { operation_id: "abc123" })
262
  .throwOnError(); // Will throw if function returns error
263
```
264

265
### Advanced RPC Patterns
266

267
Complex patterns for sophisticated database operations.
268

269
**Usage Examples:**
270

271
```typescript
272
// Chained function calls (if function returns table-like data)
273
const { data: chainedResults } = await client
274
  .rpc("get_user_recommendations", { user_id: 123 })
275
  .select("recommendation_id, score, item_title")
276
  .gte("score", 0.8)
277
  .order("score", { ascending: false })
278
  .limit(10);
279

280
// Function returning single object
281
const { data: singleResult } = await client
282
  .rpc("get_user_summary", { user_id: 456 })
283
  .single(); // Ensure single object return
284

285
// Function with CSV output format
286
const { data: csvReport } = await client
287
  .rpc("generate_report", { 
288
    report_type: "sales",
289
    format: "detailed" 
290
  })
291
  .csv(); // Return as CSV string
292

293
// Function with transaction control
294
const { data: testResult } = await client
295
  .rpc("test_data_migration", { 
296
    migration_id: "test_001" 
297
  })
298
  .rollback(); // Execute but don't commit changes
299

300
// Batch function calls
301
const batchPromises = [
302
  client.rpc("process_batch", { batch_id: 1 }),
303
  client.rpc("process_batch", { batch_id: 2 }),
304
  client.rpc("process_batch", { batch_id: 3 })
305
];
306

307
const batchResults = await Promise.all(batchPromises);
308

309
// Function with request cancellation
310
const controller = new AbortController();
311
setTimeout(() => controller.abort(), 30000); // 30 second timeout
312

313
const { data: longRunningResult } = await client
314
  .rpc("long_running_analysis", { dataset_id: "large_001" })
315
  .abortSignal(controller.signal);
316
```
317

318
### Performance Optimization
319

320
Optimize function calls for better performance.
321

322
**Usage Examples:**
323

324
```typescript
325
// Use GET method for cacheable read-only functions
326
const { data: cachedStats } = await client
327
  .rpc("get_daily_statistics", { 
328
    date: "2023-12-01" 
329
  }, { get: true }); // Can be cached by CDN/proxy
330

331
// Use planned count for large result sets
332
const { count: approximateCount } = await client
333
  .rpc("get_massive_dataset", { 
334
    filter: "active" 
335
  }, { 
336
    head: true, 
337
    count: 'planned' // Fast approximate count
338
  });
339

340
// Limit results for pagination
341
const { data: pagedResults } = await client
342
  .rpc("search_comprehensive", { 
343
    search_term: "postgresql" 
344
  })
345
  .order("relevance_score", { ascending: false })
346
  .range(0, 49); // First 50 results
347

348
// Use specific column selection to reduce data transfer
349
const { data: optimizedResults } = await client
350
  .rpc("get_detailed_analytics", { 
351
    analysis_id: "monthly_2023" 
352
  })
353
  .select("id, summary, key_metrics") // Only needed columns
354
  .limit(100);
355
```
356

357
### Security Considerations
358

359
Best practices for secure function calls.
360

361
**Usage Examples:**
362

363
```typescript
364
// Parameterized function calls (PostgREST handles SQL injection prevention)
365
const { data: safeResults } = await client
366
  .rpc("search_secure", { 
367
    user_input: "'; DROP TABLE users; --", // This is safely handled
368
    user_id: currentUserId 
369
  });
370

371
// Validate parameters before function calls
372
function validateUserId(userId: number): boolean {
373
  return Number.isInteger(userId) && userId > 0;
374
}
375

376
if (validateUserId(userId)) {
377
  const { data } = await client
378
    .rpc("get_user_data", { user_id: userId });
379
}
380

381
// Use typed schemas to prevent parameter mistakes
382
interface StrictDatabase {
383
  public: {
384
    Functions: {
385
      secure_user_operation: {
386
        Args: { 
387
          user_id: number;           // Required, type-checked
388
          operation: 'read' | 'write'; // Limited to specific values
389
          permissions?: string[];     // Optional array
390
        };
391
        Returns: { success: boolean; message: string };
392
      };
393
    };
394
  };
395
}
396

397
const secureClient = new PostgrestClient<StrictDatabase>("https://api.example.com");
398

399
// Type-safe function call
400
const { data: secureResult } = await secureClient
401
  .rpc("secure_user_operation", {
402
    user_id: 123,                    // ✅ Type-safe
403
    operation: "read",               // ✅ Limited to valid values
404
    permissions: ["read", "write"]   // ✅ Optional typed array
405
    // invalid_param: "value"        // ❌ TypeScript error
406
  });
407
```