if i call ``` create or replace function test returns void a Supabase #help

if i call ``` create or replace function test() re...

jar

04/14/2022, 1:56 PM

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

Needle

04/14/2022, 1:56 PM

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.

Scott P

04/14/2022, 2:01 PM

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;

Needle

04/14/2022, 2:01 PM

jar (2022-04-14)

jar

04/14/2022, 2:08 PM

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

Scott P

04/14/2022, 2:14 PM

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());

)

jar

04/14/2022, 2:18 PM

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

jar

04/14/2022, 2:19 PM

should be origin right? or always

Scott P

04/14/2022, 2:20 PM

I'd generally leave it at whatever the default is. No harm in testing the other option if the first one doesn't work

jar

04/14/2022, 2:25 PM

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

jar

04/14/2022, 2:29 PM

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

jar

04/14/2022, 2:39 PM

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

garyaustin

04/14/2022, 2:41 PM

If you add security definer in your trigger function does it work?

jar

04/14/2022, 2:42 PM

Im still only a few days into learning this so not sure what that means if you have the code snippit I should add

jar

04/14/2022, 2:42 PM

or i can also google it prob

jar

04/14/2022, 2:43 PM

like auth.uid() typ;e hing u meann?

garyaustin

04/14/2022, 2:45 PM

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.

garyaustin

04/14/2022, 2:45 PM

Do you have RLS turned on for any of your tables?

garyaustin

04/14/2022, 2:46 PM

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

jar

04/14/2022, 2:49 PM

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?

jar

04/14/2022, 2:54 PM

not quite sure what i do with log thing. first time using

garyaustin

04/14/2022, 2:54 PM

In the UI the choices would have been security definer or security invoker (default). Anywhere between begin and end for raise.

jar

04/14/2022, 2:54 PM

ya i hit definer on those

garyaustin

04/14/2022, 2:55 PM

your log message should have a real variable name if you want like new.id or don't put that there

jar

04/14/2022, 2:55 PM

and some of. the tests still work throu the enabled rls so should be fine in tha regard

garyaustin

04/14/2022, 2:56 PM

If you have security definer set, RLS will not matter

jar

04/14/2022, 2:56 PM

right.

jar

04/14/2022, 2:57 PM

ok ill just google log stuff idk what any of this means

garyaustin

04/14/2022, 2:59 PM

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.

jar

04/14/2022, 3:04 PM

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;

jar

04/14/2022, 3:05 PM

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;

jar

04/14/2022, 3:06 PM

and then I call something in that log tab?

jar

04/14/2022, 3:08 PM

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;

garyaustin

04/14/2022, 3:23 PM

yeah, sorry... should have been raise log 'testlog' || new.id; to build up the string...

jar

04/14/2022, 3:25 PM

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;

jar

04/14/2022, 3:25 PM

syntax error at or near ||

jar

04/14/2022, 3:30 PM

i dont think new is available outside values area

garyaustin

04/14/2022, 3:36 PM

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.

garyaustin

04/14/2022, 3:38 PM

This shows the correct formats for putting the var in... https://www.enterprisedb.com/postgres-tutorials/how-raise-errors-and-report-messages-within-stored-procedures-and-functions

jar

04/14/2022, 3:43 PM

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

garyaustin

04/14/2022, 3:49 PM

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.

jar

04/14/2022, 7:17 PM

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?

jar

04/14/2022, 7:18 PM

here is func public

jar

04/14/2022, 7:22 PM

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

jar

04/14/2022, 7:23 PM

and good to know for future

Previous Next