subquery must return only one column during insert data for returning

Question

is that any way for returning join column during INSERT INTO ? here is my query

insert into contacts(address,building_type, building_number, user_id, province_id, city_id, district_id, village_id)
VALUES ('address one', 'apartement', 12, 1, 1 , 1, 1, 1)
RETURNING address, building_type, building_number,
    (select p.name as province from provinces as p where p.id = contacts.province_id),
    (select c.name  as city from cities as c where c.id = contacts.city_id),
    (select d.name as district from districts as d where d.id = contacts.district_id),
    (select v.name as village, v.postal from villages as v where v.id = contacts.village_id);

the last sub query is not work because i want return the villages table with two columns is that any way to pass that 2 columns ??

Answer 1

You can use a CTE with returning . . . and then a query:

with i as (
      insert into contacts(address,building_type, building_number, user_id, province_id, city_id, district_id, village_id)
      values ('address one', 'apartement', 12, 1, 1 , 1, 1, 1)
      returning address, rt, rw, building_type, building_number, province_id, city_id, district_id, village_id)
select i.*,
       (select p.name as province from provinces as p where p.id = i.province_id),
       (select c.name  as city from cities as c where c.id = i.city_id),
       (select d.name as district from districts as d where d.id = i.district_id),
       (select v.name as village, v.postal from villages as v where v.id = i.village_id)    
from i;

However, the above is returning multiple rows for villages. Probably the best way to fix that is with proper joins:

with i as (
      insert into contacts(address,building_type, building_number, user_id, province_id, city_id, district_id, village_id)
      values ('address one', 'apartement', 12, 1, 1 , 1, 1, 1)
      returning address, rt, rw, building_type, building_number, province_id, city_id, district_id, village_id)
select i.*, p.name as province, c.name as city, d.name as district,
       v.name as village, v.postal   
from i left join
     province p
     on p.id = i.province_id left join
     cities c 
     on c.id = i.city_id left join
     districts d 
     on d.id = i.district_id left join
     villages v 
     on v.id = i.village_id