Supabase SELECT with function call

Question

From Javascript, I want to call a function in my supabase select statement (funcition name is id_encode in this example):

const { data, error } = await supabase
        .from('table')
        .select("id, id_encode(id, 'salt', 8)")

equivalent in SQL would be:

SELECT id, id_encode(id, 'salt', 8) FROM table;

Answer 1

I faced similar challange. It appear the function must be created via sql with argument that matches table type so postgrest can correctly build relations between table and stored procedure. my_function_name(table_name) Function must be created via SQL as Supabase UI for now does not allow using table type as argument.

CREATE OR REPLACE FUNCTION get_event_statistics(my_table)
RETURNS JSONB AS
$$
SELECT jsonb_build_object(
   'access_count', COUNT(*),
   'first_access', MIN(created_at),
   'last_access', MAX(created_at)
)
FROM my_table_events
WHERE fk_id = $1.id;
$$ LANGUAGE sql STABLE SECURITY DEFINER;

Then it's possible to call it in select just like a column

.select('*, get_event_statistics')