function is not working
this is my function
userprofile creation query :
error i :
create
or replace function add_organization_member (
name_input text,
email_input text,
orgid_input uuid,
role_input org_roles
) returns table (success boolean, is_joined boolean) as $$
begin
DECLARE
user_id uuid;
name text;
-- Check if the user exists in the users table
select user_id,name into strict user_id,name
from user_profile
where email = email_input;
-- If the user exists, add them to org_team_members with is_joined = TRUE
if found then
INSERT INTO org_team_members (user_id, org_id, role, user_name,user_email,is_joined)
VALUES (user_id, orgid_input, role_input, name,email_input,true);
else
-- If the user doesn't exist, add them with is_joined = FALSE
INSERT INTO org_team_members (org_id, role,user_email, ,user_name,is_joined)
VALUES (orgid_input, role_input, email_input,name_input,FALSE);
end if;
return query select
TRUE AS success,
is_joined;
end;
$$ language plpgsql;
userprofile creation query :
create table
public.user_profile (
user_id uuid not null,
created_at timestamp with time zone not null default now(),
name text null,
phone text null,
email text null,
about character varying null,
profession text null,
updated_at timestamp with time zone null,
organization_id uuid null default gen_random_uuid (),
profile_image character varying null,
constraint user_profile_pkey primary key (user_id),
constraint user_profile_user_id_fkey foreign key (user_id) references auth.users (id) on delete cascade
) tablespace pg_default;
error i :
ERROR: 42704: type "user_id" does not exist
LINE 16: select user_id,name into user_id,name