DEV Community

sugiarto
sugiarto

Posted on

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.

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;
$$;

Enter fullscreen mode Exit fullscreen mode

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
  )
Enter fullscreen mode Exit fullscreen mode

To call this function within a SQL editor.

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

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

Hope this helps.

Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read more

Top comments (0)

Image of Docusign

🛠️ Bring your solution into Docusign. Reach over 1.6M customers.

Docusign is now extensible. Overcome challenges with disconnected products and inaccessible data by bringing your solutions into Docusign and publishing to 1.6M customers in the App Center.

Learn more