Database Functions

Scenarios

Tables store data, CRUD operations implement data reads and writes, and row-level security (RLS) controls which rows are visible and accessible. However, not all requirements can be simplified to "reading or writing a single row": Cross-table statistics require aggregating data from multiple tables, publishing or unpublishing a product requires writing to multiple tables within the same transaction, and timestamps need to be updated automatically when data changes. Such logic is difficult to express in a single SQL statement and may require procedural control or automatic triggering, which automatically generated Data API endpoints cannot cover. If this logic is implemented on the application side, the frontend and backend must repeatedly align fields and business logic, and a single change may affect multiple places.

This section introduces database functions, also referred to as stored procedures, which encapsulate business logic in reusable SQL or PL/pgSQL code stored in the database for applications to invoke as needed. Database functions can be used for scenarios such as cross-table aggregation, transaction linkage, data validation, automatic change-triggered actions, and controlled external access. These scenarios can be defined once and executed at the data side, eliminating the need to write code repeatedly. Database functions can be exposed as RPC endpoints through PostgREST and called directly from the frontend using .rpc(). They can also be bound to triggers to execute automatically when data changes, such as refreshing updated_at values.

Related Concepts

For more information, see Reference.

• Database Functions

  A database function is a callable procedure stored in a PostgreSQL database. It accepts input parameters, executes within the database, and returns results. Database functions complement the CRUD operations provided by the Data API: CRUD operations handle reads and writes on individual tables, whereas functions handle scenarios that require procedural control, transactional guarantees, or automatic triggering, or involve logic that is difficult to express in a single SQL statement. Functions execute within the database and can use PostgreSQL capabilities, including JSONB, window functions, transactions, other functions, and triggers.

• Function Languages

  Table 1 Function languages

  Language	Characteristics	Use Cases
  sql	Completes tasks using a single SQL query without procedural control; simple and efficient.	Encapsulating queries, calculations, and joins.
  plpgsql	Supports variables, branching, loops, exception handling, and transaction control.	Multi-step logic, conditional checks, and batch processing.

  Both are built-in PostgreSQL languages. SQL uses a standard SQL query as the function body and does not support procedural control, making it suitable for encapsulating queries. plpgsql (PL/pgSQL) is PostgreSQL's procedural language. It extends SQL with variables, branching, loops, and exception handling, making it suitable for multi-step and conditional logic. For details, see PostgreSQL Procedural Languages. Use SQL for simple encapsulation, and use plpgsql when procedural control is required.

• Return Types

  Table 2 Common return types

  Return Type	Description	Typical Use Cases
  void	Returns no value.	Performing write or update operations only, such as batch maintenance.
  Scalar (bigint, numeric, text, etc.)	Returns a single value.	Counting, finding minimum or maximum values, and returning calculation results.
  SETOF	Returns multiple rows from a specific table.	Returning data sets that match specified conditions.
  TABLE(column type, ...)	Returns multiple rows with a user-defined structure.	Returning result sets that combine data from multiple tables or include calculated columns.
  trigger	Used exclusively by triggers.	Automatic field maintenance, auditing, and validation.

  For functions that return a SETOF or TABLE result, you can query the results through RPC and use the same chained filtering and pagination capabilities available for tables.

• Security Modes

  Functions support two security modes that determine whose privileges are used for execution and whether RLS constraints apply.

  Table 3 Security modes

  Mode	Execution Privileges	Subject to RLS?	Use Cases
  SECURITY INVOKER (default)	Caller	Yes	Processing only data that the caller is already authorized to access.
  SECURITY DEFINER	Function owner (typically able to bypass RLS)	Bypass RLS	Perform specific operations with elevated privileges while restricting the privileges granted to the function.

  SECURITY DEFINER executes with the owner's privileges and can bypass RLS. Although convenient, it introduces security risks. PostgreSQL grants EXECUTE privileges to PUBLIC by default, so a SECURITY DEFINER function in the public schema may be callable by the anon and authenticated roles, effectively creating a publicly accessible entry point with elevated privileges. Use SECURITY DEFINER only when bypassing RLS is necessary, and follow these restrictions:

  • Revoke the default execution privilege with REVOKE ... FROM PUBLIC.
  • Grant EXECUTE only to target roles.
  • Validate the caller within the function body using auth.uid().
  • Set the schema search path using SET search_path to prevent objects with the same name from being hijacked.
• Invocation Methods

  Functions support two invocation methods: RPC and triggers.

  Table 4 Invocation methods

  Method	When It Executes	Description
  RPC	Called by an application	The frontend or server initiates an .rpc() call through the SDK, passing parameters as needed to retrieve results.
  Trigger	Automatically executed upon data changes	Bound to INSERT, UPDATE, or DELETE operations on a table, without application intervention.

  Functions that return the trigger type are intended exclusively for trigger invocation and cannot be called through RPC. Regular functions can be called through RPC but cannot be directly bound as triggers.

Use Cases of Functions and CRUD Operations

Database functions do not replace the Data API's CRUD operations. Instead, they handle complex logic that CRUD operations alone cannot address. Table 5 describes scenarios suited to functions and CRUD operations.

Key benefits: Encapsulating complex logic for reuse, guaranteeing transaction consistency, enabling automatic execution, and providing controlled access

Creating Functions

There are two ways to create database functions: write and execute a CREATE FUNCTION statement in the Dashboard's SQL Editor, or fill in a graphical form on the Database > Functions page. Both methods create the same type of database object and can be used together.

Recommendation: Use the Database > Functions page to quickly create a basic function. Use the SQL Editor for complex logic, privilege restrictions for SECURITY DEFINER functions, and trigger functions.

Calling Functions (RPC)

Functions defined in exposed schemas (such as public) are automatically exposed as RPC endpoints by PostgREST. RPC stands for Remote Procedure Call. In this context, it refers to calling database functions through HTTP APIs. The frontend can invoke these functions using .rpc() provided by the Supabase SDK. The following examples use JavaScript.

Invocation permissions follow the same rules as CRUD operations: Requests made with a Publishable Key by unauthenticated users execute under the anon role(subject to RLS). After login, requests execute under the authenticated role. SECURITY DEFINER functions execute with the owner's privileges.

1. No-Argument Calls

   Example scenario: Retrieve all products and their pricing details.

   const { data, error } = await supabase.rpc('get_products_with_pricing')
   if (error) { console.error(error); return }
   // Example data: [{ id: 1, name: 'Elastic Cloud Server (ECS)', category: 'Compute', tier: 'Monthly', price: 99.00 }, ...]

   The second argument can be omitted when calling a function without parameters. The structure of the returned value maps directly to the function's RETURNS TABLE definition.

2. Parameterized Calls

   Example scenario: Query the lowest price for the product with ID 1.

   // Pass parameters by name (recommended; parameter names must match the function definition).
   const { data, error } = await supabase.rpc('get_min_price', { pid: 1 })
   if (error) { console.error(error); return }
   // data is a scalar returned by the function, such as 0 or 99.00.
   
   // Count products in a category.
   const { data: count } = await supabase.rpc('get_product_count_by_category', { cat: 'Compute' })

   The second argument of .rpc() is an object whose keys correspond to the function's parameter names. PostgREST binds the arguments by name, regardless of parameter order.

3. Result Filtering and Pagination

   Functions that return sets support chained filtering, sorting, and pagination methods similar to those used for table queries. This avoids transferring all results to the frontend for filtering.

   Example scenario: Retrieve only products in the Compute category, sorted by name, and return the first 10 results from the product pricing list.

   // Chained filtering, sorting, and pagination on the results of get_products_with_pricing()
   const { data, error } = await supabase
     .rpc('get_products_with_pricing')
     .eq('category', 'Compute')
     .order('name')
     .limit(10)

   Chained methods, such as .eq(), .order(), and .limit(), apply to the set returned by the function, just as they do to table queries. For details, see basic SDK operations in Database Data APIs.

Trigger Functions (Automatic Execution on Data Changes)

Trigger functions return the trigger type and are executed automatically by the database when data changes. They do not need to be called by the application. They are commonly used to let the database handle tasks that must occur automatically when data changes. As mentioned in Database Data APIs, the updated_at column is assigned its default value on insertion but is not automatically refreshed on updates. This is one of the typical use cases for triggers.

1. Automatic Field Maintenance

   Example scenario: Automatically set updated_at to the current time when product information changes, so the application does not need to provide the value manually on every update.

   First, define a function that returns trigger.

   CREATE OR REPLACE FUNCTION set_updated_at()
   RETURNS trigger
   LANGUAGE plpgsql
   AS $$
   BEGIN
     NEW.updated_at = now();   -- Set updated_at to the current time for the new row.
     RETURN NEW;               -- Return the new row so the operation can proceed.
   END;
   $$;

   Then bind the function to UPDATE operations on the target table.

   CREATE TRIGGER trg_products_updated_at
     BEFORE UPDATE ON products      -- Execute before the update to modify updated_at.
     FOR EACH ROW                  -- Trigger once for each row.
     EXECUTE FUNCTION set_updated_at();

   From then on, updated_at is automatically updated whenever a row in the products table is updated, without any application-layer intervention.

   • If the products table already has an updated_at column, you can bind the trigger directly.
   • If the product_pricings table does not have an updated_at column, add the column to product_pricings first, then bind the same trigger to automatically maintain the column.

     -- Add an updated_at column to product_pricings (the table originally has only created_at).
     ALTER TABLE product_pricings ADD COLUMN updated_at TIMESTAMPTZ DEFAULT now();
     
     CREATE TRIGGER trg_pricings_updated_at
       BEFORE UPDATE ON product_pricings
       FOR EACH ROW
       EXECUTE FUNCTION set_updated_at();

   Note: Trigger functions cannot be called through RPC. For a BEFORE trigger, returning NEW applies the changes, whereas returning NULL cancels the operation for that row. Triggers can also be managed visually on the Triggers page of the Dashboard. For details, see Database Triggers.

2. Change Auditing

   Example scenario: Record the old and new prices when a product's price changes for traceability. First, create an audit table. Then, use a trigger to write records when prices are updated.

   CREATE TABLE product_price_audit (
     id          BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
     product_id  BIGINT NOT NULL,
     old_price   NUMERIC(10,2),
     new_price   NUMERIC(10,2),
     changed_at  TIMESTAMPTZ DEFAULT now()
   );
   
   CREATE OR REPLACE FUNCTION log_price_change()
   RETURNS trigger
   LANGUAGE plpgsql
   AS $$
   BEGIN
     IF NEW.price IS DISTINCT FROM OLD.price THEN   -- Record only when the price changes.
       INSERT INTO product_price_audit (product_id, old_price, new_price)
       VALUES (NEW.product_id, OLD.price, NEW.price);
     END IF;
     RETURN NEW;
   END;
   $$;
   
   CREATE TRIGGER trg_pricing_audit
     AFTER UPDATE ON product_pricings   -- Record the change after the update to ensure that new values have been written to the database.
     FOR EACH ROW
     EXECUTE FUNCTION log_price_change();

   Note: An AFTER trigger records the change after the row has been updated. IS DISTINCT FROM securely compares values that contain NULL. You can configure RLS separately on the audit table so that only operations personnel can view its records.

Management and Maintenance

Routine management tasks after creating functions and triggers, including viewing, modifying, deleting, and refreshing the schema cache, are primarily performed through the SQL Editor and Database interfaces.

1. Viewing Functions

   Navigate to Database > Functions to view the functions in the current schema, including their names, parameters, return types, and definitions. The interface is primarily for viewing functions but also supports GUI-based creation. For details, see Method 2: Create a Function on the Database > Functions Page. Editing and iteratively refining function bodies is recommended in the SQL Editor. For details, see Method 1: Create Functions in the SQL Editor (Recommended for Full Control).

2. Modifying and Deleting Functions

   Modifying functions: Use CREATE OR REPLACE FUNCTION to replace a function's definition. You can edit and rerun the SQL statement in the SQL Editor or edit the function through the Database > Functions form. After modifying a function, refresh the schema cache as described in 3.

   Deleting functions: Delete functions that are no longer needed using DROP FUNCTION. Specify the parameter types to distinguish between overloaded functions with the same name.

   DROP FUNCTION get_min_price(bigint);

3. Refreshing the Schema Cache

   After creating or modifying a function, refresh the PostgREST schema cache to make the function available through RPC. A delay of several seconds may occur the first time.

   NOTIFY pgrst, 'reload schema';

4. Managing Triggers

   Navigate to Database > Triggers to create triggers through the GUI. Select the target table, trigger timing (BEFORE or AFTER), event (INSERT, UPDATE, or DELETE), and the function to trigger. You can also enable, disable, or delete existing triggers on this page.

   Note: To restrict the execution privileges of SECURITY DEFINER functions using REVOKE and GRANT, you must execute SQL statements. For details, see Method 1: Create Functions in the SQL Editor (Recommended for Full Control). The GUI does not provide a graphical interface for these operations.

Case

Example scenario: The operations team needs a Product Overview API that summarizes the number of products, minimum price, and maximum price by category for display on a backend dashboard. Because this involves joins and aggregation across multiple tables, CRUD operations alone are not sufficient to express the logic in a single operation. Encapsulating the logic in a function is a suitable approach.

Define a function that groups products by category and joins the pricing table to retrieve the price range.

CREATE OR REPLACE FUNCTION get_category_summary()
RETURNS TABLE (
  category      text,
  product_count bigint,
  min_price     numeric,
  max_price     numeric
)
LANGUAGE sql
AS $$
  SELECT p.category,
         count(DISTINCT p.id),
         min(pr.price),
         max(pr.price)
  FROM products p
  LEFT JOIN product_pricings pr ON pr.product_id = p.id
  GROUP BY p.category
  ORDER BY p.category;
$$;

Refresh the cache and call the function through the SDK (using JavaScript as an example).

NOTIFY pgrst, 'reload schema';

const { data, error } = await supabase.rpc('get_category_summary')
if (error) { console.error(error); return }
// Example data:
// [
//   { category: 'Compute', product_count: 2, min_price: 99.00, max_price: 588.00 },
//   { category: 'Storage', product_count: 1, min_price: 45.00, max_price: 45.00 }
// ]

To retrieve results for a specific category, you can chain a filter onto the function call or modify the function to accept a category parameter.

// Reuse the chained filtering approach from 3 to retrieve only the Compute category.
const { data } = await supabase.rpc('get_category_summary').eq('category', 'Compute')

This example encapsulates joins, aggregation, and sorting in the database. The frontend can retrieve the aggregated results with a single RPC call, without building a separate backend API or transferring detailed records to the frontend for aggregation.

Reference

The following open-source Supabase documents are provided for reference only.

• Supabase Database Functions
• Supabase JavaScript SDK — rpc
• PostgreSQL — CREATE FUNCTION
• PostgreSQL — Triggers