sugiartoTwo 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.
