https://supabase.com/ logo
Join Discord
Powered by
can you call other functions in a trigger? I want ...
# help
j
can you call other functions in a trigger? I want to fill the username field on insert of profile
Copy code
-- declare
  -- newusername text := generate_available_username();
  -- newusername text := uuid_generate_v4();
begin
  insert into public.profiles(username)
  values(uuid_generate_v4());
  return new;
end
or
Copy code
begin
  new.username := uuid_generate_v4();
  return new;
end
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.
g
Sure.
n
jar (2022-04-12)
j
K, well something is not working for it in the trigger on prifile creation which is causing signup to fail
g
You may need to set path in function if you have not, or put schema in front of each call.
You might also see the error in the database logs. Do your function return types match your table column types? No uuid type stored to string for example.
j
these are types and username was force unique and not null which failed before I put the uuid_generate_v4 in there and now this attempt failing for trigger although maybe ill try putting directly in there. New still didnt thinnk to check logs also not sure if there is a console.log style thing like js i can put in funcs. And idk what is meant by set path in func/schema
g
Please check the log for errors, also you can output to log with
Copy code
raise log 'message' || variable
like you use public.profiles you might need extensions.function() if that is where they are. You set search path like in the example of an auth trigger function: https://supabase.com/docs/guides/auth/managing-user-data#advanced-techniques You might need to add ,extensions to it.
you might also try uuid_generate_v4()::varchar
j
oh ok ya i wasnt sure why I saw some places use path like that and some not. And I'll try the log as well
I also tried putting the name generating fun in the field default directly and failed. will look into log soon here. This func seems to work well (my first real sql func i made, worked on all. day yeserday) does it look correct to you basically have one table of adjectives and one of nouns and get length of each, gen random num, call where those ids (although if delete an id its null so had to account for that), and try up to 10 unique values otherwise just give a uuid
Copy code
create or replace function generate_available_username() 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;
    new_username text := uuid_generate_v4();
    try_new_username text;
  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 where id = rand_num_adjective into chosen_adjective;
      select text from nouns where id = rand_num_noun into chosen_noun;
      if not (coalesce(chosen_adjective, '') = '' or coalesce(chosen_noun, '') = '') then
        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 if;
    END LOOP;
    return new_username;
  end;
$$ language plpgsql
can I see the plpgsql code that the ui used to create these tables anywhere?
g
I have a database tool as part of my ide similar to datagrip that I use to see the data base and it can show the sql. I think pgadmin can also do that.
j
Ok I will look into that thanks. This went from trying out supabase to learning plpgsql and all this but tbh I do like it so I think worth it
g
You cannot put a function as default if it does database calls and such. You will have to use a trigger function. Also not sure why you would create random names versus use a pattern based on id. So 001 is adj1-noun1 002 is adj2-noun1. If you have 10 adjectives then noun would be id/10+1. id would be auto incrementing id for the row.
j
That sounds like a much better approach. I'm not used to these auto-incrementing ids. Very useful. Ok that makes sense for default didnt know thanks! Ya I think I will try that approach now and then try and get it working again. does it matter if do trigger before or after really?
g
Not sure what table the trigger is on. If this is on auth.users to generate an entry in another table then trigger after. You have trigger on your profile table to set the name on insert then you would do trigger before and use new.id (defined sequential so it increments) and set new.username and then return new. There would be a slight hole in this sequential id idea though because it also increases anytime you update a row in the table, so you would waste name combo's if you do a lot of updating.
j
ohh right. I actually have person that gets created on trigger of users insert and then on person insert have profile insert and on profile insert have generate_username. but i should just put username in the profile one not as trigger. Ok I have some things to work on here
so a user has a person row that can have many profiles each with usernames and such
g
Here is how to solve the sequence number issue, It is like a function call, but you set it up first. https://www.postgresql.org/docs/current/sql-createsequence.html
PreviousNext
Powered by