Skip to content

Instantly share code, notes, and snippets.

@oluwaseye
Last active August 11, 2022 22:56
Show Gist options
  • Save oluwaseye/bb9dafeb27937b825a15c2be6b7e11c8 to your computer and use it in GitHub Desktop.
Save oluwaseye/bb9dafeb27937b825a15c2be6b7e11c8 to your computer and use it in GitHub Desktop.
Supabase Postgres Functions Cheatsheet
/*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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment