My WHERE clause in my Supabase request doesn't work

May 3, 2023

I’m discovering how Supabase works with NextJS and I am able to retrieve results from a table in a given geographical area with this Supabase function :

create or replace function jobs_in_view(min_lat float, min_long float, max_lat float, max_long float)
returns setof record
language sql
as $$
  SELECT *, st_astext(location) AS location
  FROM public.jobs
  WHERE location && ST_SetSRID(ST_MakeBox2D(ST_Point(min_long, min_lat), ST_Point(max_long, max_lat)),4326)
$$;

const { data: jobs } = await supabase.rpc('jobs_in_view', bounds);

This code works perfectly well, but now, I want to add another filter clause like the contract type so I updated my function as follows :

create or replace function jobs_in_view(min_lat float, min_long float, max_lat float, max_long float, contract_type varchar)
returns setof record
language sql
as $$
  SELECT *, st_astext(location) AS location
  FROM public.jobs
  WHERE contract_type=contract_type
  AND location && ST_SetSRID(ST_MakeBox2D(ST_Point(min_long, min_lat), ST_Point(max_long, max_lat)),4326)
$$;

const { data: jobs } = await supabase.rpc('jobs_in_view', { ...bounds, 'contract_type': 'full-time' });

the filtering does not work and returns all the results inside the bounds but the type of contract is ignored by the query. What did I miss in writing my function?

>Solution :

Since you are using the same name for the column name and the variable name, you need to disambiguate them. You can do so by adding the table name or the function name before the variable like this.

create or replace function jobs_in_view(min_lat float, min_long float, max_lat float, max_long float, contract_type varchar)
returns setof record
language sql
as $$
  SELECT *, st_astext(location) AS location
  FROM public.jobs
  WHERE jobs.contract_type= jobs_in_view.contract_type
  AND location && ST_SetSRID(ST_MakeBox2D(ST_Point(min_long, min_lat), ST_Point(max_long, max_lat)),4326)
$$;

Notice that on the where clause I have jobs.contract_type on one side and jobs_in_view.contract_type on the other. The first one is targeting the column name of the table and the later is targeting the variable of the function.