mirror of https://github.com/artifacthub/hub.git
29 lines
899 B
SQL
29 lines
899 B
SQL
-- verify_email verifies an email using the provided email verification code,
|
|
-- returning true if the email was verified successfully or false otherwise.
|
|
create or replace function verify_email(p_code uuid)
|
|
returns boolean as $$
|
|
begin
|
|
-- Check if email verification code exists and is not expired
|
|
perform from email_verification_code
|
|
where email_verification_code_id = p_code
|
|
and created_at + '1 day'::interval > current_timestamp;
|
|
if not found then
|
|
return false;
|
|
end if;
|
|
|
|
-- Mark email as verified in user record
|
|
update "user"
|
|
set email_verified = true
|
|
where user_id = (
|
|
select user_id from email_verification_code
|
|
where email_verification_code_id = p_code
|
|
);
|
|
|
|
-- Delete email verification code
|
|
delete from email_verification_code
|
|
where email_verification_code_id = p_code;
|
|
|
|
return true;
|
|
end
|
|
$$ language plpgsql;
|