Stored Procedures

/**
 * Perform a function call
 * @param fn - The function name to call
 * @param args - The arguments to pass to the function call
 * @param options - Named parameters
 * @param options.head - When set to `true`, `data` will not be returned. Useful if you only need the count
 * @param options.get - When set to `true`, the function will be called with read-only access mode
 * @param options.count - Count algorithm to use to count rows returned by the function. Only applicable for set-returning functions
 */
rpc<FnName extends string & keyof Schema['Functions'], Fn extends Schema['Functions'][FnName]>(
  fn: FnName,
  args?: Fn['Args'],
  options?: {
    head?: boolean;
    get?: boolean;
    count?: 'exact' | 'planned' | 'estimated';
  }
): PostgrestFilterBuilder<...>;

import { PostgrestClient } from "@supabase/postgrest-js";

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

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

// Function call with arguments
const { data: userStats } = await client
  .rpc("calculate_user_stats", { 
    user_id: 123,
    start_date: "2023-01-01",
    end_date: "2023-12-31"
  });

// Function call with type safety (when using typed schema)
interface Database {
  public: {
    Functions: {
      get_user_posts: {
        Args: { user_id: number; limit?: number };
        Returns: { id: number; title: string; created_at: string }[];
      };
      calculate_metrics: {
        Args: { date_range: string };
        Returns: { total_users: number; active_users: number };
      };
    };
  };
}

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

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

// POST method (default) - for functions that modify data
rpc(fn: string, args?: object): PostgrestFilterBuilder<...>;

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

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

// Default POST method for data-modifying functions
const { data } = await client
  .rpc("create_user_report", { 
    user_id: 123,
    report_type: "monthly" 
  });

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

// HEAD method when you only need count information
const { count } = await client
  .rpc("get_large_dataset", { 
    filter_criteria: "active" 
  }, { 
    head: true, 
    count: 'exact' 
  });

// GET method with query parameters in URL
const { data: cachedResults } = await client
  .rpc("get_cached_report", { 
    report_id: "monthly_2023",
    format: "summary" 
  }, { get: true });

/**
 * For set-returning functions, you can use count algorithms and apply filters
 * to the returned data
 */
rpc(
  fn: string, 
  args?: object, 
  options?: { count?: 'exact' | 'planned' | 'estimated' }
): PostgrestFilterBuilder<...>;

// Set-returning function with count
const { data: results, count } = await client
  .rpc("get_user_activity", { 
    date_range: "[2023-01-01,2023-12-31]" 
  }, { count: 'exact' });

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

// Complex filtering on function results
const { data: processedData } = await client
  .rpc("analyze_user_behavior", { 
    analysis_type: "engagement",
    time_period: "last_30_days" 
  })
  .select("user_id, engagement_score, last_active")
  .gt("engagement_score", 0.5)
  .order("engagement_score", { ascending: false })
  .range(0, 99); // Top 100 users

// Count-only for large datasets
const { count: totalRecords } = await client
  .rpc("get_audit_logs", { 
    table_name: "users",
    action_type: "UPDATE" 
  }, { 
    head: true, 
    count: 'planned' // Fast approximate count
  });

// Function with optional parameters
const { data } = await client
  .rpc("search_users", { 
    query: "john",
    limit: 20,        // Optional parameter
    offset: 0         // Optional parameter
  });

// Function with complex parameter types
const { data: analysisResult } = await client
  .rpc("complex_analysis", {
    data_points: [1, 2, 3, 4, 5],           // Array parameter
    config: {                                // Object parameter
      include_metadata: true,
      calculation_method: "weighted_average"
    },
    tags: ["analytics", "monthly"],          // String array
    threshold: 0.75                          // Numeric parameter
  });

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

// Function with JSON parameters
const { data: jsonResults } = await client
  .rpc("process_json_data", {
    json_input: {
      filters: { status: "active", category: "premium" },
      sorting: { field: "created_at", direction: "desc" },
      pagination: { page: 1, size: 50 }
    }
  });

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

// Function call with error handling
const { data, error } = await client
  .rpc("validate_and_process", { 
    input_data: "some data" 
  });

if (error) {
  console.error("Function execution failed:", error.message);
  console.error("Error details:", error.details);
  console.error("Error code:", error.code);
  // Handle specific error cases
  if (error.code === "42883") {
    console.log("Function does not exist");
  }
}

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

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

// Chained function calls (if function returns table-like data)
const { data: chainedResults } = await client
  .rpc("get_user_recommendations", { user_id: 123 })
  .select("recommendation_id, score, item_title")
  .gte("score", 0.8)
  .order("score", { ascending: false })
  .limit(10);

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

// Function with CSV output format
const { data: csvReport } = await client
  .rpc("generate_report", { 
    report_type: "sales",
    format: "detailed" 
  })
  .csv(); // Return as CSV string

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

// Batch function calls
const batchPromises = [
  client.rpc("process_batch", { batch_id: 1 }),
  client.rpc("process_batch", { batch_id: 2 }),
  client.rpc("process_batch", { batch_id: 3 })
];

const batchResults = await Promise.all(batchPromises);

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

const { data: longRunningResult } = await client
  .rpc("long_running_analysis", { dataset_id: "large_001" })
  .abortSignal(controller.signal);

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

// Use planned count for large result sets
const { count: approximateCount } = await client
  .rpc("get_massive_dataset", { 
    filter: "active" 
  }, { 
    head: true, 
    count: 'planned' // Fast approximate count
  });

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

// Use specific column selection to reduce data transfer
const { data: optimizedResults } = await client
  .rpc("get_detailed_analytics", { 
    analysis_id: "monthly_2023" 
  })
  .select("id, summary, key_metrics") // Only needed columns
  .limit(100);

// Parameterized function calls (PostgREST handles SQL injection prevention)
const { data: safeResults } = await client
  .rpc("search_secure", { 
    user_input: "'; DROP TABLE users; --", // This is safely handled
    user_id: currentUserId 
  });

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

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

// Use typed schemas to prevent parameter mistakes
interface StrictDatabase {
  public: {
    Functions: {
      secure_user_operation: {
        Args: { 
          user_id: number;           // Required, type-checked
          operation: 'read' | 'write'; // Limited to specific values
          permissions?: string[];     // Optional array
        };
        Returns: { success: boolean; message: string };
      };
    };
  };
}

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

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