G
Size: a a a
2021 February 25
ДМ
во всех примерах json_to_recordset вижу json_to_recordset(..) as ...
CREATE OR REPLACE FUNCTION public.insert_or_update(data jsonb)
returns void
LANGUAGE plpgsql
AS $function$
begin
insert into public.contacts
(
user_id,
phone_number,
country_code,
email,
contact_name,
company,job_title,
account_name,
contact_group,
created_at
)
select x.userId, x.phoneNumber, x.countryCode, x.contactName,x.email, x.company, x.jobTitle, x.accountName, x.contactGroup, x.createdAt
from jsonb_to_recordset(data) as x(
id int8,
userId int8,
phoneNumber varchar,
countryCode varchar,
contactName varchar,
email varchar,
company varchar,
jobTitle varchar,
accountName varchar,
contactGroup int4,
createdAt timestamp,
updatedTimes int8
)
on conflict on constraint contacts_un do
update set
contact_name = x.contact_name,
company = x.company,
contact_group = x.contact_group,
email = x.email,
account_name = x.account_name,
job_title = x.job_title;
END;
$function$
А
Дмитрий Мачихелян
CREATE OR REPLACE FUNCTION public.insert_or_update(data jsonb)
returns void
LANGUAGE plpgsql
AS $function$
begin
insert into public.contacts
(
user_id,
phone_number,
country_code,
email,
contact_name,
company,job_title,
account_name,
contact_group,
created_at
)
select x.userId, x.phoneNumber, x.countryCode, x.contactName,x.email, x.company, x.jobTitle, x.accountName, x.contactGroup, x.createdAt
from jsonb_to_recordset(data) as x(
id int8,
userId int8,
phoneNumber varchar,
countryCode varchar,
contactName varchar,
email varchar,
company varchar,
jobTitle varchar,
accountName varchar,
contactGroup int4,
createdAt timestamp,
updatedTimes int8
)
on conflict on constraint contacts_un do
update set
contact_name = x.contact_name,
company = x.company,
contact_group = x.contact_group,
email = x.email,
account_name = x.account_name,
job_title = x.job_title;
END;
$function$
Работает?
ДМ
Предполагаю, что нужно опять селект писать на jsonb_to_recordset для update. Но как-то глупым кажется
А
on conflict on constraint contacts_un do
update set
contact_name = excluded.contact_name,
company = excluded.company,
contact_group = excluded.contact_group,
email = excluded.email,
account_name = excluded.account_name,
job_title = excluded.job_title;
update set
contact_name = excluded.contact_name,
company = excluded.company,
contact_group = excluded.contact_group,
email = excluded.email,
account_name = excluded.account_name,
job_title = excluded.job_title;
ДМ
on conflict on constraint contacts_un do
update set
contact_name = excluded.contact_name,
company = excluded.company,
contact_group = excluded.contact_group,
email = excluded.email,
account_name = excluded.account_name,
job_title = excluded.job_title;
update set
contact_name = excluded.contact_name,
company = excluded.company,
contact_group = excluded.contact_group,
email = excluded.email,
account_name = excluded.account_name,
job_title = excluded.job_title;
Спасибо)
IP
в анус себе засунь
Почему именно в 18:14, когда я переключился на телеграм во время скринкаста для компании?
IP
Теперь у нас на проекте все будут обучаться с таким уведомлением, я не буду монтировать