73 lines
2.0 KiB
MySQL
73 lines
2.0 KiB
MySQL
|
-- Deploy camper:edit_contact to pg
|
||
|
|
-- requires: roles
|
||
|
|
-- requires: schema_camper
|
||
|
|
-- requires: email
|
||
|
|
-- requires: country_code
|
||
|
|
-- requires: contact
|
||
|
|
-- requires: extension_pg_libphonenumber
|
||
|
|
-- requires: contact_phone
|
||
|
|
-- requires: contact_email
|
||
|
|
|
||
|
|
begin;
|
||
|
|
|
||
|
|
set search_path to camper, public;
|
||
|
|
|
||
|
|
create or replace function edit_contact(contact_slug uuid, name text, id_document_type_id text, id_document_number text, phone text, email text, address text, city text, province text, postal_code text, country_code country_code) returns uuid as
|
||
|
|
$$
|
||
|
|
declare
|
||
|
|
cid integer;
|
||
|
|
begin
|
||
|
|
update contact
|
||
|
|
set name = edit_contact.name
|
||
|
|
, id_document_type_id = edit_contact.id_document_type_id
|
||
|
|
, id_document_number = edit_contact.id_document_number
|
||
|
|
, address = edit_contact.address
|
||
|
|
, city = edit_contact.city
|
||
|
|
, province = edit_contact.province
|
||
|
|
, postal_code = edit_contact.postal_code
|
||
|
|
, country_code = edit_contact.country_code
|
||
|
|
where slug = contact_slug
|
||
|
|
returning contact_id
|
||
|
|
into cid
|
||
|
|
;
|
||
|
|
|
||
|
|
if cid is null then
|
||
|
|
return null;
|
||
|
|
end if;
|
||
|
|
|
||
|
|
if phone is null or trim(phone) = '' then
|
||
|
|
delete from contact_phone
|
||
|
|
where contact_id = cid
|
||
|
|
;
|
||
|
|
else
|
||
|
|
insert into contact_phone (contact_id, phone)
|
||
|
|
values (cid, parse_packed_phone_number(phone, coalesce(country_code, 'ES')))
|
||
|
|
on conflict (contact_id) do update
|
||
|
|
set phone = excluded.phone
|
||
|
|
;
|
||
|
|
end if;
|
||
|
|
|
||
|
|
if email is null or trim(email) = '' then
|
||
|
|
delete from contact_email
|
||
|
|
where contact_id = cid
|
||
|
|
;
|
||
|
|
else
|
||
|
|
insert into contact_email (contact_id, email)
|
||
|
|
values (cid, email)
|
||
|
|
on conflict (contact_id) do update
|
||
|
|
set email = excluded.email
|
||
|
|
;
|
||
|
|
end if;
|
||
|
|
|
||
|
|
return contact_slug;
|
||
|
|
end
|
||
|
|
$$
|
||
|
|
language plpgsql
|
||
|
|
;
|
||
|
|
|
||
|
|
revoke execute on function edit_contact(uuid, text, text, text, text, text, text, text, text, text, country_code) from public;
|
||
|
|
grant execute on function edit_contact(uuid, text, text, text, text, text, text, text, text, text, country_code) to employee;
|
||
|
|
grant execute on function edit_contact(uuid, text, text, text, text, text, text, text, text, text, country_code) to admin;
|
||
|
|
|
||
|
|
commit;
|