CODEWITHSUNDEEP
sqlpostgresqlcurlsupabasesupabase-database
j___.___j
j___.___j

Reputation: 326

Supabase SQL Database Functions and Parameters

In my supabase database, I have a list of words along with their uuids in the form of

uuid word unique_chars found_count
x1 apple 4 0
y2 banana 3 0

I want to return all words, which contain a specific letter – say "E" (called "main char") – from the table. Therefore, I tried using an SQL Database Function like this:

create or replace function get_possible_words()
returns setof dict
language sql
as $$
  select *
  from dict
  where word ilike (%e%)
$$;

which works perfectly fine and would return "apple".

However, when trying to pass the letter "E" ("main char") as a parameter, it is not working anymore, whatever I try:

## Get Results (Supabase)
curl -X "POST" "https://abc.supabase.co/rest/v1/rpc/get_possible_words" \
     -H 'apikey: key' \
     -H 'Content-Type: application/json; charset=utf-8' \
     -d $'{
  "mainchar": "E"
}'

together with a slightly altered version of the function

create or replace function get_possible_words(mainchar text)
returns setof dict
language sql
as $$
  select *
  from dict
  where word like (mainchar)
$$;

does not work. Another attempt was

drop function if exists test_query;
create function test_query (parameter text)
RETURNS TABLE(uuid uuid, word text) AS
$$
BEGIN
  return query SELECT uuid, word FROM "dict"
    INNER JOIN "word" ON "dict".column = "word".column 
    WHERE "word".column = parameter;
END;
$$
language plpgsql volatile;

which did not work as well ("relation "word" does not exist"). Could someone please help me out with the syntax? Thanks a lot and all the best from Germany!

Upvotes: 2

Views: 2762

Answers (1)

dshukertjr
dshukertjr

Reputation: 18670

Would this do it?

create or replace function get_possible_words(mainchar text)
returns setof dict
language sql
as $$
  select *
  from dict
  where word ilike (mainchar)
$$;

curl -X "POST" "https://abc.supabase.co/rest/v1/rpc/get_possible_words" \
     -H 'apikey: key' \
     -H 'Content-Type: application/json; charset=utf-8' \
     -d $'{
  "mainchar": "%e%"
}'

Upvotes: 1

Related Questions