cat pm-1123847.sh
#!/bin/sh
#
# File_Deal - the key here is parent_cusp,deal,tranche
#
echo "parent_cusp|cusp|isin|deal|tranche|det_date|col_type
38375U|36182D|36182D1|HMAG|HMBSWEE|20150416|mortgage
383333|361333|3618333|HABS|HABSDDE|20150330|mortgage2" > /tmp/file_deal.txt

#
# File_ATT - the key here is Vendor, deal, tranche 
#
echo "Vendor|visp|barnembly|deal|tranche|Fund|subamt|colamt|basamt
38375U|3DD82D|36FF333|HMAG|HMBSWEE|9010|765423|364633|46566
38EE33|361DD3|36LLE33|H99S|HAOOODE|2330|377233|347433|34488" > /tmp/file_att.txt

echo "
drop foreign table if exists t1 cascade;
drop foreign table if exists t2 cascade;
drop server        if exists s  cascade;
" | psql -X

echo "
create server s foreign data wrapper file_fdw;

create foreign table t1 (
    parent_cusp text,
    cusp text,
    isin text,
    deal text,
    tranche text,
    det_date text,
    col_type text
) server s
options (filename '/tmp/file_deal.txt', format 'csv', delimiter E'|', header 'true');

create foreign table t2 (
    vendor text,
    visp text,
    barnembly text,
    deal text,
    tranche text,
    fund text,
    subamt text,
    colamt text,
    basamt text
) server s
options (filename '/tmp/file_att.txt', format 'csv', delimiter E'|', header 'true');

select * from t1;

select * from t2;

select 
    coalesce(t1.parent_cusp, t2.vendor) as parent_cusp
  , t1.cusp
  , t1.isin
  , coalesce(t1.deal, t2.deal)          as deal
  , coalesce(t1.tranche, t2.tranche)    as tranche
  , t1.det_date
  , t1.col_type
  , t2.fund
  , t2.subamt
  , t2.colamt
  , t2.basamt

from t1 full join t2 
  on (t1.parent_cusp, t1.deal, t1.tranche) =
     (t2.vendor, t2.deal, t2.tranche)
;
" | psql -X 

echo "File_Output

parent_cusp|cusp|isin|deal|tranche|det_date|col_type|Fund|subamt|colamt|basamt
38375U|36182D|36182D1|HMAG|HMBSWEE|20150416|mortgage|9010|765423|364633|46566
383333|361333|3618333|HABS|HABSDDE|20150330|mortgage2||||
38EE33|||H99S|HAOOODE|||2330|377233|347433|34488
"