Create supabase database function

Two days ago, I had a requirement to create a database function that accepts a single argument, in this case a 'user_id', and returns custom data as a table.

To create this function, we need to open SQL Editor.

Here is the sample code that I used at my function:

create or replace function get_friends(user_id uuid)
 RETURNS TABLE(
    friend_id uuid,
    updated_at timestamptz,
    first_name text,
    last_name text,
    email text,
    profile_image_url text,
    location text
  )
  language plpgsql as
$$

BEGIN
  RETURN QUERY
  select
    friends.friend_id,
    friends.updated_at,
    users.first_name,
    users.last_name,
    users.email,
    users.profile_image_url,
    users.location
  from friends
  inner join users ON users.id = friends.friend_id
  where
    friends.user_id = get_friend_ids.user_id AND
    friend_status = 'approved' AND
    users.info_complete = true

  union

  select
    friends.user_id friend_id,
    friends.updated_at,
    users.first_name,
    users.last_name,
    users.email,
    users.profile_image_url,
    users.location
  from friends
  inner join users ON users.id = friends.user_id
  where
    friends.friend_id = get_friends.user_id AND
    user_status = 'approved' AND
    users.info_complete = true;
end;
$$;

As I want to function to return a custom table, so I used table as return values.

RETURNS TABLE(
    friend_id uuid,
    updated_at timestamptz,
    first_name text,
    last_name text,
    email text,
    profile_image_url text,
    location text
  )

To call this function within a SQL editor.

select * from get_friends(uuid('e7839548-40e8-4c7e-8f6c-2b5cee6bde2d'))

We used uuid() to convert from text to uuid.

Hope this helps.