Hey! Anyone can help with a sample code to use in creating a supabase function for rpc?

Postgres functions can do pretty much anything inside Postgres, so if you have an idea of what you're trying to achieve, it'll be easier to put something together

I want to get the total of

netAmount

. Here's what i did:

declare
   total integer;
begin
   select count(netAmount) 
   into total
   from donations
   where beneficiaryId = id;
   
   return total;
end;

Sort of - for basic queries like this, you can usually copy what's there. For your specific use case, something like this should work:

sql
CREATE OR REPLACE FUNCTION public.get_net_amount_total(in_id uuid)
RETURNS money
AS $BODY$
BEGIN
  RETURN QUERY
  SELECT count(netAmount) AS total
  FROM public.donations
  WHERE id = in_id;
END;
$BODY$ language 'plpgsql';

When you call it from Supabase, you'd call:

supabase.rpc("get_net_amount_total", {in_id: "some_uuid"})

I'm assuming that

id

is a UUID - if not, change the data type in the arguments of the function.

money

is the data type that's returned (https://www.postgresql.org/docs/9.1/datatype-money.html)

I see. Well

id

is a UUID. But the code i showed above kinda work but i get this error

column "netamount" does not exist

. I have a

netAmount

column in my table. Here's how i call it

const { data, error } = await supabase.rpc("get_balance", { id });

You have 2 options: - Put quotes around

netAmount

in the query (e.g.

"netAmount"

) - Rename the column to

net_amount

The first option is easier, but the second option would be my recommendation. Postgres is easier to work with if you use all-lowercase, or snake-case for column names

First option worked, had to do same for

beneficiaryId

. Now got this error:

column reference "id" is ambiguous

If you get issues about ambiguous columns, you can assign an alias to a table with

AS

, like this:

sql
CREATE OR REPLACE FUNCTION public.get_net_amount_total(in_id uuid)
RETURNS money
AS $BODY$
BEGIN
  RETURN QUERY
  SELECT count(netAmount) AS total
  FROM public.donations AS donations
  WHERE donations.id = in_id;
END;
$BODY$ language 'plpgsql';

getting this error

The one I wrote can be run from the SQL editor directly - you should then be able to see it in the functions in the dashboard

oh!

If you have an

id

column on the same table, it might be trying to match against that. Whenever you're defining arguments for a function, it's best to make sure they don't share the name with any column - prefixing them with

in_

is how I prevent that from happening.

Found about that right now. Here's my working code:

sql
DECLARE
  total integer;

BEGIN
  SELECT SUM("netAmount")
  INTO total
  FROM public.donations as donations
  WHERE donations."beneficiaryId" = userid; //params is now `userid`
  RETURN total;
END;

Thanks for your help and time