Database functions

Database functions are reusable logic that runs inside PostgreSQL. You can wrap complex SQL, transactional writes, aggregate statistics, permission helpers, and other data-layer capabilities in functions, then call them through SQL, server-side direct connections, or HTTP API RPC.

Database functions are suitable for logic that should run close to the data. They are not suitable for slow external calls. Use Cloud Functions or CloudBase Run for third-party APIs, long-running tasks, or complex orchestration.

Use cases

Use case	Description
Reuse complex queries	Wrap joins, filters, sorting, and aggregation in one function
Transactional writes	Complete multi-table writes or state transitions in one database transaction
Data validation	Check inventory, quota, status, and other business constraints before writing
RPC APIs	Call functions through HTTP API and expose controlled capabilities to clients or third-party systems
Permission helpers	Wrap checks for team membership, resource ownership, and similar authorization logic

Create a database function

You can create database functions in the console or with SQL statements. The CloudBase PostgreSQL management UI is currently consistent with the Supabase console, so it is suitable for viewing, creating, and managing database functions.

Console

1. Go to the CloudBase Console / PostgreSQL Database management page.
2. Select the target environment and open the database management UI.
3. Open the Database Functions management page.
4. Click Create Function.
5. Enter the function name, schema, arguments, return type, and function body.
6. Select the language based on the function logic: prefer LANGUAGE SQL for simple SQL expressions or queries; use LANGUAGE plpgsql when variables, branches, exception handling, or multiple statements are required.
7. Configure volatility, security mode, and other options as needed, then confirm and submit.

The console is suitable for creating common functions and viewing existing functions. For migration scripts, bulk releases, or changes that require code review, manage functions with SQL statements.

SQL

Prefer LANGUAGE SQL by default. Functions that only contain SQL expressions or queries do not need a procedural language, and LANGUAGE SQL is easier to review for permissions and execution behavior.

create or replace function public.add_numbers(a integer, b integer)
returns integer
LANGUAGE SQL
stable
as $$
  select a + b;
$$;

Call the function:

select public.add_numbers(1, 2);

stable means the function returns the same result for the same input during one statement execution. Pure calculation and read-only query functions can usually be marked as stable.

Use PL/pgSQL for procedural logic

Use LANGUAGE plpgsql only when a function needs variables, branches, exception handling, or multiple statements. The example below needs DECLARE variables, IF branches, RAISE EXCEPTION, and multiple write statements, so it must use LANGUAGE plpgsql.

create or replace function public.create_order(
  p_user_id varchar,
  p_product_id bigint,
  p_quantity integer
)
returns bigint
LANGUAGE plpgsql
as $$
declare
  v_order_id bigint;
  v_stock integer;
begin
  select stock into v_stock
  from public.products
  where id = p_product_id
  for update;

  if v_stock is null then
    raise exception 'product not found';
  end if;

  if v_stock < p_quantity then
    raise exception 'insufficient stock';
  end if;

  update public.products
  set stock = stock - p_quantity
  where id = p_product_id;

  insert into public.orders(user_id, product_id, quantity, status)
  values (p_user_id, p_product_id, p_quantity, 'created')
  returning id into v_order_id;

  return v_order_id;
end;
$$;

A function runs in one database transaction. If it raises an exception, the current call rolls back.

Return table data

Functions can return table-shaped data. This is useful for search, filtering, and statistics queries.

create or replace function public.search_articles(keyword text)
returns table (
  id bigint,
  title text,
  created_at timestamptz
)
LANGUAGE SQL
stable
as $$
  select a.id, a.title, a.created_at
  from public.articles a
  where a.title ilike '%' || keyword || '%'
  order by a.created_at desc
  limit 20;
$$;

Call it:

select * from public.search_articles('cloudbase');

When a table-returning function is exposed through HTTP API RPC, it can still use PostgREST-supported field selection, filtering, sorting, and pagination.

Return JSON

Use json or jsonb for aggregate results or nested structures.

create or replace function public.get_order_stats()
returns jsonb
LANGUAGE SQL
stable
as $$
  select jsonb_build_object(
    'total', count(*),
    'paid', count(*) filter (where status = 'paid'),
    'pending', count(*) filter (where status = 'pending')
  )
  from public.orders;
$$;

Call through HTTP API

The CloudBase PostgreSQL HTTP API is based on PostgREST. Use /rpc/:function_name to call database functions.

curl -X POST 'https://<envId>.api.tcloudbasegateway.com/v1/rdb/rest/rpc/add_numbers' \
  -H 'Authorization: Bearer <access_token>' \
  -H 'Content-Type: application/json' \
  -d '{ "a": 1, "b": 2 }'

For complete request, response, and filtering examples, see Call RPC and Access via HTTP API.

Permissions

Function calls are controlled by execute permissions. In production, revoke default permissions first, then grant access by role.

revoke execute on function public.add_numbers(integer, integer) from public;

grant execute on function public.add_numbers(integer, integer) to authenticated;

If the function is called through HTTP API, make sure the caller's role has execute permission and that table access inside the function is compatible with RLS or the function security mode.

Security invoker and security definer

PostgreSQL functions run with caller permissions by default, which is equivalent to security invoker. This is usually safer because table permissions and RLS apply to the caller.

create or replace function public.list_my_orders()
returns setof public.orders
LANGUAGE SQL
security invoker
stable
as $$
  select *
  from public.orders
  where user_id = (select auth.uid());
$$;

security definer runs with the function owner's permissions and may bypass the caller's permission boundary. Use it only when you need to wrap a controlled privileged operation.

create or replace function public.get_team_member_count(p_team_id bigint)
returns integer
LANGUAGE SQL
security definer
set search_path = public
stable
as $$
  select count(*)::integer
  from public.team_members
  where team_id = p_team_id;
$$;

When using security definer, explicitly set search_path and strictly control execute grants to prevent users from accessing data they should not access.

Debugging and error handling

Use raise notice for debug output and raise exception to stop a call.

raise notice 'creating order for user %, product %', p_user_id, p_product_id;
raise exception 'insufficient stock';

Remove unnecessary notice output before launch. Keep exception messages concise and actionable, and do not include passwords, tokens, or sensitive data.

Changes and releases

create or replace function can update the function body, but changing parameter types, parameter order, or return type usually requires dropping the old function first.

drop function if exists public.add_numbers(integer, integer);

If the function is exposed through HTTP API, changing its signature may require waiting for or refreshing the Schema Cache. For diagnostics, see Schema cache issues.

Best practices

• Keep functions small and focused on database logic.
• Use clear parameter names instead of relying on positional meaning.
• Control execute permissions for externally exposed functions.
• Prefer security invoker; use security definer carefully.
• Index fields that are frequently filtered inside functions.
• Put complex orchestration in Cloud Functions or CloudBase Run, and keep database functions for data-local consistency logic.