Does row return limit apply to database functions ( rpc )?

[erenpal]

Does row return limit apply to database functions ( rpc )?

[cekuu35]

Short answer: yes for table-returning RPCs, no for scalar/JSON returns — and the function's own LIMIT is the one that wins if both exist.

The longer version, because the layers matter:

1. PostgREST's db-max-rows applies to collection responses. If your function is declared returns setof <table> (or table(...)) and you call it via .rpc(), PostgREST treats the response as a collection — the same row limit that applies to direct table reads applies to the RPC result. So max-rows: 1000 truncates an RPC returning 5,000 rows the same way it truncates a table query.

2. Scalar and JSON returns sidestep it. If the function returns a single row type or json/jsonb (a common pattern for paginated or aggregated RPCs), the response is one document — there's no collection for PostgREST to limit. A function that manually builds and returns a jsonb array can return any size, regardless of db-max-rows.

3. The function's own LIMIT is authoritative at the SQL layer. If the function body contains an explicit limit N (or select ... limit inside return query), that runs inside Postgres — PostgREST's limit then applies to whatever the function already returned. They compose: min(function's LIMIT, PostgREST's max-rows) is effectively what the client sees.

4. RLS interacts, SECURITY DEFINER doesn't. For an security invoker function, RLS on the underlying tables filters what the function can see — so the limit applies to post-RLS rows. For security definer, RLS is bypassed (the owner's privileges), and the limit applies to the unfiltered result — worth knowing when a definer function with no internal LIMIT can return far more than a table query ever would.

Practical pattern when you need guaranteed bounds on an RPC:

create or replace function public.get_recent_items(p_limit int default 50)
returns setof public.items
language sql
security invoker
as $$
  select * from public.items
  order by created_at desc
  limit greatest(1, least(p_limit, 500));  -- server-side clamp
$$;

The server-side clamp means the client can never request unbounded results even if db-max-rows is set generously or the caller passes p_limit => 999999.