Skip to content
Advertisement

How to create a function that returns a value if id is in an array?

I’m trying to create a new function in supabase. I’m quit new to SQL and postgreSQL so I’m not sure what I’m doing. Anyway I want to make a function that checks if one value is in an array of ints. If it is then return 0.8 if it’s not then return 1.0.

create or replace function is_liked(id bigint, liked bigint[])
returns float 
language plpgsql
as 
$$
DECLARE 
    addedNum float
begin
   if exists(SELECT $1 = ANY($2))
   THEN
      set nuaddedNumm := 0.8
   else
      set addedNum := 1
   return addedNum
end;
$$

Here is my code I get the error message “Failed to run sql query: syntax error at or near “begin””

Advertisement

Answer

You are missing a ; after the variable declaration and all other statements. And as documented in the manual the assignment operator in PL/pgSQL is := (or =), not SET.

As also documented in the manual an IF needs an END IF.

So the correct syntax would be:

create or replace function is_liked(id bigint, liked bigint[])
  returns float 
  language plpgsql
as 
$$
DECLARE 
    addedNum float; --<< missing ;
begin
   if exists(SELECT $1 = ANY($2))
   THEN
      nuaddedNumm := 0.8; --<< no SET, missing ;
   else
      addedNum := 1; --<< no SET, missing ; 
   end if; --<< missing END IF
   return addedNum; --<< missing ;
end;
$$

However you don’t need the variable or the EXISTS at all. There is also no need to refer to parameters by their number

create or replace function is_liked(id bigint, liked bigint[])
  returns float 
  language plpgsql
as 
$$
begin
   if id = ANY(liked) THEN
      return 0.8; 
   else
      return 1; 
   end if;
end;
$$

In fact you don’t even need PL/pgSQL for this:

create or replace function is_liked(id bigint, liked bigint[])
  returns float 
  language sql
as 
$$
  select case 
           when id = ANY(liked) then 0.8
           else 1.0
         end;
$$;
Advertisement