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
"

##</code><code>##


$ ./pm-1123847.sh
DROP FOREIGN TABLE
DROP FOREIGN TABLE
DROP SERVER
CREATE SERVER
CREATE FOREIGN TABLE
CREATE FOREIGN TABLE
 parent_cusp |  cusp  |  isin   | deal | tranche | det_date | col_type
-------------+--------+---------+------+---------+----------+-----------
 38375U      | 36182D | 36182D1 | HMAG | HMBSWEE | 20150416 | mortgage
 383333      | 361333 | 3618333 | HABS | HABSDDE | 20150330 | mortgage2
(2 rows)

 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
(2 rows)

 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
(3 rows)

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


##</code><code>##

copy ( <some SQL> ) to '/tmp/file_output.txt'
with (format csv, delimiter '|', header true)