https://supabase.com/ logo
i created this function, but im getting ``"No operator matches the given name and argument types. Yo
f

formigueiro

05/25/2023, 5:23 PM
sql
DECLARE
  slash_position INT;
  value int;
  entry_id int;
BEGIN
  -- Encontra a posição da última ocorrência do caractere '/'
  slash_position := strpos(reverse(new.short_url), '/');
  
  -- Extrai o valor após a última ocorrência do caractere '/'
  value := reverse(substring(reverse(new.short_url) from 1 for slash_position - 1));

  select id 
  from url_data 
  into entry_id
  where slug = value;

  update public.report_data
  set id = entry_id
  where id = new.id;

  RETURN new;
END;
erro :
"No operator matches the given name and argument types. You might need to add explicit type casts."
ive updated to
sql
DECLARE
  slash_position INT;
  value TEXT;
  entry_id UUID;
BEGIN
  -- Encontra a posição da última ocorrência do caractere '/'
  slash_position := strpos(reverse(new.short_url), '/');
  
  -- Extrai o valor após a última ocorrência do caractere '/'
  value := reverse(substring(reverse(new.short_url) from 1 for slash_position - 1));

  select id
  into entry_id
  from url_data
  WHERE slug = value;

  update public.report_data
  set url_id = entry_id
  where id = new.id;

  -- raise exception 'zzzz %', entry_id;

  RETURN new;
END;
but now its not updating report_data
g

garyaustin

05/25/2023, 5:55 PM
Do you have RLS involved on report_data table if this is called initially from client code versus the sql editor?
You can use
raise log 'value =%',variable;
in your code to help debug. Results will be in Postgres log from dashboard.
f

formigueiro

05/25/2023, 5:57 PM
sql
DECLARE
  slash_position INT;
  value TEXT;
  entry_id UUID;
BEGIN
  -- Encontra a posição da última ocorrência do caractere '/'
  slash_position := strpos(reverse(new.short_url), '/');
  
  -- Extrai o valor após a última ocorrência do caractere '/'
  value := reverse(substring(reverse(new.short_url) from 1 for slash_position - 1));

  select id
  into entry_id
  from url_data
  WHERE slug = value;

  raise exception 'zzzz %', new;
  
  -- update public.report_data
  -- set url_id = 'eeff7d6b-6cd3-42ee-bab6-05b63262fb88'
  -- where id = new.id;

  -- raise exception 'zzzz %', entry_id;

  RETURN new;
END;
yes im loggin to check, but im gettin null on new

https://cdn.discordapp.com/attachments/1111343911659638886/1111352413207658529/image.png

i just have RLS to insert
g

garyaustin

05/25/2023, 5:57 PM
Do you have the trigger as row level versus statement level?
new will be null on a statement level trigger.
f

formigueiro

05/25/2023, 5:58 PM
What do I need to do, sorry?
g

garyaustin

05/25/2023, 5:59 PM
How did you create the trigger?
f

formigueiro

05/25/2023, 5:59 PM
from function tab
so i copied the code that i created on sql editor
g

garyaustin

05/25/2023, 6:00 PM

https://cdn.discordapp.com/attachments/1111343911659638886/1111353094828204143/image.png

Default is Statement
That is the trigger, not the function
f

formigueiro

05/25/2023, 6:01 PM
let me create again, so
what do i need to select here?

https://cdn.discordapp.com/attachments/1111343911659638886/1111353572756557845/image.png

g

garyaustin

05/25/2023, 6:02 PM
That is the function not the trigger
f

formigueiro

05/25/2023, 6:02 PM
ok im creating my function again
do i need to select some option?
leave trigger?
g

garyaustin

05/25/2023, 6:03 PM
My guess is your problem is in the trigger, that is a different screen
f

formigueiro

05/25/2023, 6:03 PM

https://cdn.discordapp.com/attachments/1111343911659638886/1111353861630853213/image.png

i know but im doin all stepp again
g

garyaustin

05/25/2023, 6:03 PM

https://cdn.discordapp.com/attachments/1111343911659638886/1111353925958910052/image.png

f

formigueiro

05/25/2023, 6:04 PM

https://cdn.discordapp.com/attachments/1111343911659638886/1111354083060760677/image.png

https://cdn.discordapp.com/attachments/1111343911659638886/1111354170914639982/image.png

g

garyaustin

05/25/2023, 6:04 PM
That is the trigger screen and statement at bottom is wrong.
Needs to be Row
f

formigueiro

05/25/2023, 6:05 PM
i changed
g

garyaustin

05/25/2023, 6:05 PM
Yes
f

formigueiro

05/25/2023, 6:05 PM
now worked
ty my friend, you are awesome