Inconsistent null-handling for 1-1 embedded functions with RETURNS vs RETURNS SETOF ... ROWS 1

[avallete]

Overview:

When using computed relationships (embedded functions), the behavior differs based on whether the function is declared with RETURNS <table> or RETURNS SETOF <table> ROWS 1. Specifically, when there is no matching row:

• RETURNS SETOF <table> ROWS 1 → returns null as expected
• RETURNS <table> → returns an object with all fields set to null

This is inconsistent and makes type inference and runtime handling more error-prone.

Steps to Reproduce:

1. Define two functions with identical logic but different return types:

-- Schema setup
create table users (
  id serial primary key,
  username text unique,
  status text
);

create table user_profiles (
  id serial primary key,
  username text unique,
  bio text
);

insert into users (username, status) values
  ('supabot', 'ONLINE'),
  ('kiwicopple', 'OFFLINE');

insert into user_profiles (username, bio) values
  ('supabot', 'Supabase bot');

-- Function A: RETURNS SETOF ... ROWS 1
CREATE OR REPLACE FUNCTION get_user_profile_setof(user_row users)
RETURNS SETOF user_profiles
LANGUAGE SQL STABLE
ROWS 1
AS $$
  SELECT * FROM user_profiles WHERE username = user_row.username;
$$;

-- Function B: RETURNS (no SETOF)
CREATE OR REPLACE FUNCTION get_user_profile_single(user_row users)
RETURNS user_profiles
LANGUAGE SQL STABLE
AS $$
  SELECT * FROM user_profiles WHERE username = user_row.username;
$$;

2. Query with an embedded function:

const res = await postgrest
  .from('users')
  .select(`
    username,
    profile_setof:get_user_profile_setof(*),
    profile_single:get_user_profile_single(*)
  `);

3. For users without a matching profile, the result is:

{
  "username": "kiwicopple",
  "profile_setof": null,
  "profile_single": {
    "id": null,
    "username": null
  }
}

Expected Behavior:

Both embedded function forms should return null if no match is found — not an object with null fields. That would make the behavior consistent and more intuitive, especially when used for 1:1 relationships.

Impact:

• Confusing semantics for end users
• Forces type inference to branch based on PostgREST version and return type
• Breaks consistency between .rpc() and embedded queries
• Likely unintentional behavior for computed relationships using RETURNS <table>

Suggested Fix:

Make the computed relationship behaviour uniform:

• If the function returns no row, return null, regardless of RETURNS vs RETURNS SETOF ... ROWS 1
• Possibly emit an error or warning if RETURNS <table> is used as an embedded function, if it’s considered unsupported

Reference: Related: #4232