Supabase Postgres Functions Cheatsheet

supabase_db_functions_cheatsheet.sql

/*Create db function to query a table or relational query*/
CREATE OR REPLACE FUNCTION public.handle_get_user_info() 
    RETURNS TABLE(email text, fullname text) as $$
    BEGIN RETURN QUERY
            SELECT 
            profiles.email, 
            profiles.fullname 
            FROM public.profiles WHERE profiles.id = auth.uid() 
            LIMIT 1;  
    END;
$$  LANGUAGE plpgsql

/*Create db function to update a column on a table */
CREATE OR REPLACE FUNCTION public.handle_update_fullname(full_name text) 
    RETURNS bool
    as $$
    BEGIN 
            UPDATE public.profiles SET fullname = full_name WHERE profiles.id = auth.uid();
            RETURN TRUE;
    END;
$$  LANGUAGE plpgsql

/*Create db function to update a column on a table with column and value as paremeters */
/*this is for value of type boolean */
/*More info here. https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN*/
CREATE OR REPLACE FUNCTION public.handle_update_single_bool_setting(colname text, colval boolean) 
    RETURNS bool
    as $$
    BEGIN 
               EXECUTE 'UPDATE public.settings SET '
                || quote_ident(colname)
                || ' = '
                || quote_nullable(colval)
                || ' WHERE settings.id = '
                || quote_literal(auth.uid());

            RETURN TRUE;
    END;
$$  LANGUAGE plpgsql