https://supabase.com/ logo
Powered by
if i call ``` create or replace function test() re...
# help
j
if i call
Copy code
create or replace function test() returns void as $$
  declare
    new_username text;
  begin
    new_username = generate_available_username();
    insert into public.profiles(belongs_to_person_id, username)
    values('9395c81d-ff88-421e-9d8a-5a31675d4418', new_username);
  end;
$$ language plpgsql

select test()
it adds a profile with a username fine. But if i use a function which Id like to do sign up fails. Does my trigger version look correct? Are there rules in triggers Im not aware of?
Copy code
declare
  new_username text;
begin
  new_username = generate_available_username();
  insert into public.profiles(belongs_to_person_id, username)
  values(new.id, new_username);
  return new;
end;
I have a trigger that creates a person on user sign up and on person signup create profile. Do they prevent too many strung together tasks or something? Although without including usename it woks fine
n
Hello @jar! This thread has been automatically created from your message in #843999948717555735 a ``few seconds ago``. Pinging @User so that they see this as well! Want to unsubscribe from this thread? Right-click the thread in Discord (or use the ... menu) and select Leave Thread to unsubscribe from future updates. Want to change the title? Use the 
/title
command! We have solved your problem? Click the button below to archive it.
s
You can use functions directly as values. I'm not sure if it'll fix the issue, but try this:
Copy code
sql
begin
  insert into public.profiles(belongs_to_person_id, username)
  values(new.id, generate_available_username());
  return new;
end;
n
jar (2022-04-14)
j
Ok I tired that first and it didnt work and wasnt sure so changed to this. Either way still doesnt work like that also. Is there a good way to debug or get more info than 
{message: 'Database error saving new user', status: 500
s
Debugging triggers is a nightmare, and the only reliable way I've found is the logs in the dashboard (but finding the correct log entry can be quite time consuming). Does 
SELECT * FROM generate_available_username()
return data that's the same type as your 
username
column (e.g. if 
username
type is 
text
, is the type returned from that function also 
text
)? If you try to run the SQL directly with dummy data, does it work? (e.g.:
Copy code
sql
insert into public.profiles(belongs_to_person_id, username)
  values('some-valid-id', generate_available_username());
)
j
running like this in sql editor does add profile for the existing person id but let me try an existing person_id on the creation of a new person trigger like that
should be origin right? or always
s
I'd generally leave it at whatever the default is. No harm in testing the other option if the first one doesn't work
j
the sign up succeeds when I add a preexisting person_id in there. The generate_username function does make calls on the profile table to make sure the generated username does not exist. I'm confused why not working with a new.id. Also I have default value of uuid_generate_v4() in the username field and its not null and unique
and if I dont input username on trigger it works
Copy code
begin
  insert into public.profiles(belongs_to_person_id)
  -- values(new.id, generate_available_username());
  values(new.id);
  -- values('9395c81d-ff88-421e-9d8a-5a31675d4418', generate_available_username());
  return new;
end;
so new.id works alone. generate works with prexisting person_id but not together
this is the gen username func
Copy code
create or replace function generate_available_username2() returns text as $$
  declare
    count_of_adjectives int;
    count_of_nouns int;
    rand_num_adjective int := 0;
    rand_num_noun int := 0;
    chosen_adjective text;
    chosen_noun text;
    try_new_username varchar;
  begin
    select count(*) from adjectives into count_of_adjectives;
    select count(*) from nouns into count_of_nouns;
    for i in 1..10 loop
      rand_num_adjective = random_between(1,count_of_adjectives);
      rand_num_noun = random_between(1,count_of_nouns);
      select text from adjectives order by text offset rand_num_adjective limit 1 into chosen_adjective;
      select text from nouns order by text offset rand_num_noun limit 1 into chosen_noun;
      try_new_username = chosen_adjective || '-' || chosen_noun;
      if not exists (select 1 from profiles where username = try_new_username) then
        return try_new_username;
      end if;
    end loop;
    return uuid_generate_v4();
  end;
$$ language plpgsql
g
If you add security definer in your trigger function does it work?
j
Im still only a few days into learning this so not sure what that means if you have the code snippit I should add
or i can also google it prob
like auth.uid() typ;e hing u meann?
g
https://supabase.com/docs/guides/auth/managing-user-data#advanced-techniques shows a security definer trigger function, basically just add those keywords in function definition. This bypasses RLS, just wondering if you have an RLS issue going on in your other tables. When you run your function in SQL you bypass them.
Do you have RLS turned on for any of your tables?
Did you look thru the log like Steve suggested for any errors? You can also add
Copy code
raise log 'My Function Label' variable
to get extra info in log
j
I made through ui but i should use sql editor in order to add that right? (I did click definer or something on creating the func) no i didnt look at logs and do i place that raise in the func at any place?
not quite sure what i do with log thing. first time using
g
In the UI the choices would have been security definer or security invoker (default). Anywhere between begin and end for raise.
j
ya i hit definer on those
g
your log message should have a real variable name if you want like new.id or don't put that there
j
and some of. the tests still work throu the enabled rls so should be fine in tha regard
g
If you have security definer set, RLS will not matter
j
right.
ok ill just google log stuff idk what any of this means
g
Ah, no you don't use that in looking at the logs, you use that in your function to add more info TO the logs. Put them anywhere between BEGIN and END. I would think you could skim the logs pretty quickly if you have a page open on the logs, then run your user call thru in another window. Then refresh logs. There is also an option in the log viewer to show just errors.
j
ok I honestly feel like there is a lot of ways I can take this...but lemme try
Copy code
begin
  insert into public.profiles(belongs_to_person_id,username)
  values(new.id, generate_available_username());
  raise log 'My Function Label' variable
  return new;
end;
So im turning this into
Copy code
begin
  insert into public.profiles(belongs_to_person_id,username)
  values(new.id, generate_available_username());
  raise log 'testlog' new.id;
  return new;
end;
?
and then I call something in that log tab?
idk man. says failed to create func error near new
Copy code
begin  
  raise log 'testlog' new.id;
  insert into public.profiles(belongs_to_person_id,username)
  values(new.id, generate_available_username());
  return new;
end;
g
yeah, sorry... should have been raise log 'testlog' || new.id; to build up the string...
j
Copy code
begin  
  raise log 'testlog' || new.id;
  -- insert into public.profiles(belongs_to_person_id,username)
  -- values(new.id, generate_available_username());
  insert into public.profiles(belongs_to_person_id)
  values(new.id);
  return new;
end;
syntax error at or near ||
i dont think new is available outside values area
g
Just drop the var for now. So you can look at the logs for your initial error. I just looked at my function I had raise in and have not used variables yet, so I'll have to go look at correct format.
j
ok so i channged to that and signed in which failed obvi so where do i see said log. in log explorer? what do i type in
g
under database there is postgres logs. Go to that page then run your test in another tab. Then refresh the log screen, your function messages/errors should be in the new batch of log messages loaded. Is easiest way. You can also click Severity at top and select Error to see real quickly if there was a db error.
j
nice. so it looks like it doesnnt think that. func exists. you may have mentioned something to me about a prefix to use what was that again?
here is func public
well. I had to prefix a bunch of stuff in funcs with public. and it worked! make sure every var has public. in beginning. I g2g for now but will look more later. Thanks for help! Logs super helpful in tracking down
and good to know for future
PreviousNext
Powered by