#!/bin/bash
db1=testdb1 db2=testdb2 port=6969 dir=/tmp
sql_dropdb="drop database if exists $db1; drop database if exists $db2;"
sql_createdb="create database $db1; create database $db2;"
echo "$sql_dropdb $sql_createdb" | psql -qX  # -a
t1=gutenberg.ireise1 file1=$dir/generated1.txt
t2=gutenberg.ireise2 file2=$dir/generated2.txt
for n in `seq 1 250`; do echo "file 1, line $n"; done > $file1
for n in `seq 1 250`; do echo "file 2, line $n"; done > $file2
echo "
drop   foreign table if exists $t1 cascade;
drop   foreign table if exists $t2 cascade;
drop server if exists gutenberg cascade;
drop table if exists table0, test.table1;
" | psql -Xd $db1
(echo "
create schema if not exists gutenberg;
create server if not exists goethe foreign data wrapper file_fdw;") | psql -qX -a -d $db1
echo "
create foreign table $t1 (line text) server goethe options (delimiter E'\t', format 'csv', quote '@', header 'FALSE', filename '$file1');
create foreign table $t2 (line text) server goethe options (delimiter E'\t', format 'csv', quote '@', header 'FALSE', filename '$file2');
create schema if not exists test;
create table      table0 (id integer); -- schema public
create table test.table1 (id integer); -- schema test
insert into       table0 select n from generate_series(1,2) as f(n); -- schema public
insert into  test.table1 select n from generate_series(1,3) as f(n); -- schema test" | psql -aqXd $db1

echo "
include table table0            # ok   public
include table test.table1       # 
include foreign_data goethe     # foreign server 'goethe' (file_fdw), restore ERRORs when commented in
include table gutenberg.ireise1 # foreign table
include table gutenberg.ireise2 # foreign table" > inputfile1.txt

echo "-- pg_dump --create -Fc -c -p $port -d $db1 -f dump1 --filter=inputfile1.txt"
         pg_dump --create -Fc -c -p $port -d $db1 -f dump1 --filter=inputfile1.txt

echo "
create schema if not exists test;
create schema if not exists gutenberg;
create server if not exists goethe foreign data wrapper file_fdw;" | psql -qaXd $db2

echo "-- pg_restore --if-exists -cvd $db2 dump1"
         pg_restore --if-exists -cvd $db2 dump1
rc=$?
echo "-- rc [$rc]"
sql="select * from table0; select * from test.table1; select * from gutenberg.ireise1 order by 1;"
diff -s <(echo "${sql}" | psql -qXd $db1) \
        <(echo "${sql}" | psql -qXd $db2)
echo "$sql" | psql -aqXd $db1 | md5sum
echo "$sql" | psql -aqXd $db2 | md5sum
echo "select current_database(), '$db1' db1, count(*) rowcount from gutenberg.ireise1;" | psql -qXd $db1
echo "select current_database(), '$db2' db2, count(*) rowcount from gutenberg.ireise1;" | psql -qXd $db2