If you have unique column "name" than
run the below command to install dblink extension
create extension if not exists dblink;
First we will get the data from remote server using dblink and store result in cte
with cte_remote_data as (SELECT * FROM dblink('host=remotostip/name port=remote_port user=remote_user password=remote_password dbname=remot_dbname','select name,email from users u join user_emails ue on u.id=ue.user_id')AS data( name text,email text))
select * from cte_remote_data
this will give select result from the remote database
Now insert these name in users table and get inserted id,name using another cte_ins_usr
cte_ins_usr as(insert into users(name) select name from cte_remote_data returning name,id)
now join cte_ins_usr with name with cte_remote_data on name (assuming that name is unique in table) and insert in user_email table
insert into user_emails(user_id,email) select id,email from cte_remote_data crd join cte_ins_usr ciu on crd.name=ciu.name returning *
All in one for your case:
with cte_remote_data as (SELECT * FROM dblink('host=remotostip/name port=remote_port user=remote_user password=remote_password dbname=remot_dbname','select name,email from users u join user_emails ue on u.id=ue.user_id')AS data( name text,email text)),
cte_ins_usr as(insert into users(name) select name from cte_remote_data returning name,id)
insert into user_emails(user_id,email) select id,email from cte_remote_data crd join cte_ins_usr ciu on crd.name=ciu.name returning *
will do job for you
