jar
04/14/2022, 1:56 PMcreate 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?
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 fineNeedle
04/14/2022, 1:56 PM/title
command!
We have solved your problem?
Click the button below to archive it.Scott P
04/14/2022, 2:01 PMsql
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 PMjar
04/14/2022, 2:08 PM{message: 'Database error saving new user', status: 500
Scott P
04/14/2022, 2:14 PMSELECT * 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.:
sql
insert into public.profiles(belongs_to_person_id, username)
values('some-valid-id', generate_available_username());
)jar
04/14/2022, 2:18 PMjar
04/14/2022, 2:19 PMScott P
04/14/2022, 2:20 PMjar
04/14/2022, 2:25 PMjar
04/14/2022, 2:29 PMbegin
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 togetherjar
04/14/2022, 2:39 PMcreate 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 PMjar
04/14/2022, 2:42 PMjar
04/14/2022, 2:42 PMjar
04/14/2022, 2:43 PMgaryaustin
04/14/2022, 2:45 PMgaryaustin
04/14/2022, 2:45 PMgaryaustin
04/14/2022, 2:46 PMraise log 'My Function Label' variable
to get extra info in logjar
04/14/2022, 2:49 PMjar
04/14/2022, 2:54 PMgaryaustin
04/14/2022, 2:54 PMjar
04/14/2022, 2:54 PMgaryaustin
04/14/2022, 2:55 PMjar
04/14/2022, 2:55 PMgaryaustin
04/14/2022, 2:56 PMjar
04/14/2022, 2:56 PMjar
04/14/2022, 2:57 PMgaryaustin
04/14/2022, 2:59 PMjar
04/14/2022, 3:04 PMbegin
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 PMbegin
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 PMjar
04/14/2022, 3:08 PMbegin
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 PMjar
04/14/2022, 3:25 PMbegin
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 PMjar
04/14/2022, 3:30 PMgaryaustin
04/14/2022, 3:36 PMgaryaustin
04/14/2022, 3:38 PMjar
04/14/2022, 3:43 PMgaryaustin
04/14/2022, 3:49 PMjar
04/14/2022, 7:17 PMjar
04/14/2022, 7:18 PMjar
04/14/2022, 7:22 PMjar
04/14/2022, 7:23 PM