select
                public.dblink_connect('link2contacts', 'hostaddr=x.x.x.x port=5432 dbname=contacts user=x  password=xxx');


        with
                "qryMailPrep"
                as (
                        select
                                case when "gc_To" like '%rubin%' then 'RECEIVE' else 'SENT' end as "SENT_RECEIVE",
                                case when "gc_To" like '%rubin%' then "gc_SenderEmailAddress" else "gc_To" end as "EMAIL",
                                "Mail".*
                        from
                                "Mail"
                ),
                "tblContacts"
                as (
                        select
                                *
                        from
                                dblink('link2contacts', 'select "Alpha", "ContactID", "Email1", "Email2" from "tblContacts"')
                                AS t (   "Alpha"        varchar
                                        ,"ContactID"    integer
                                        ,"Email1"       varchar
                                        ,"Email2"       varchar
                                        )
                ),
                "tblContactsSubs"
                as (
                        select
                                *
                        from
                                dblink('link2contacts', 'select "ContactID", "Email1" from "tblContactsSubs"')
                                AS t (   "ContactID"    integer
                                        ,"Email1"       varchar
                                        )
                )
        select
                "tblContacts"."Alpha", "tblContacts"."ContactID", "qryMailPrep".*
        from
                "qryMailPrep" INNER JOIN "tblContacts" on "qryMailPrep"."EMAIL" = "tblContacts"."Email1"
        union all
        select
                "tblContacts"."Alpha", "tblContacts"."ContactID", "qryMailPrep".*
        from
                "qryMailPrep" INNER JOIN "tblContacts" on "qryMailPrep"."EMAIL" = "tblContacts"."Email2"
        union all
        select
                "tblContacts"."Alpha", "tblContacts"."ContactID", "qryMailPrep".*
        from
                "qryMailPrep" INNER JOIN ("tblContacts" INNER JOIN "tblContactsSubs"
                                        on "tblContacts"."ContactID" = "tblContactsSubs"."ContactID")
                        on "qryMailPrep"."EMAIL" = "tblContactsSubs"."Email1";

        SELECT dblink_disconnect('link2contacts');

#!/bin/bash

psql contacts -qtA -c > tables_pk.txt "select
        kc.table_name || ',' || kc.column_name
from
        information_schema.table_constraints            tc
join
        information_schema.key_column_usage             kc
on
        tc.table_name = kc.table_name
and
        kc.constraint_name = tc.constraint_name
where
        tc.constraint_type = 'PRIMARY KEY'
and
        tc.constraint_schema = 'public'
and
        tc.table_name like 'tbl%data'
order by
        1;"

tables_pk="tables_pk.txt"

while IFS="," read -r table_name pk_name 
do
	echo
	echo
	echo "create or replace function sync.update_$table_name(schema_name varchar, r audit_history) returns void as \$\$"
	echo
	echo "declare stmt varchar; info varchar; pk_name varchar; this_pk integer;"
	echo
	echo "begin"
	echo
	echo "execute format('select \"%s\" from public.\"$table_name\" where audit_id = \$1','$pk_name')
		into this_pk
                using r.audit_id;"
	echo
	echo "if this_pk is null then

                info := format('In update : no record found in \"$table_name\" where audit_id = %s', r.audit_id);

                insert into sync.log values (schema_name, info);
		
		return;"

	echo "end if;"
	echo
	echo "stmt := 'update public.\"$table_name\" as p set"

	set_clause=`psql -qtA contacts -c  " 
        	select
                           '\"' 
			|| c.column_name 
			|| '\"' 
			|| ' = ' 
			|| 's.' 
			|| '\"' 
			|| c.column_name 
			|| '\"' 
			|| ','
		from 
			information_schema.tables as t 
		join
			information_schema.columns as c on t.table_name = c.table_name 
		where
			t.table_schema = 'public' 
		and 
			c.table_schema = 'public' 
		and 
			c.column_name not in ( 
				select
					kcu.column_name
				from 
					information_schema.table_constraints as tc 
				join 
					information_schema.key_column_usage as kcu 
						on tc.constraint_name = kcu.constraint_name
				join 
					information_schema.constraint_column_usage as ccu 
						on ccu.constraint_name = tc.constraint_name
				where 
					(constraint_type = 'FOREIGN KEY' or constraint_type = 'PRIMARY KEY')
				and 
					tc.table_name = '$table_name'
			)
		and
			c.column_name != 'audit_id'
		and
                        t.table_name = '$table_name'"`

	echo "${set_clause::-1}"	
	echo "';"
	echo
	echo "stmt := stmt || ' from %s.\"$table_name\" as s where p.\"$pk_name\" = s.\"$pk_name\" and p.\"$pk_name\" = \$1';" 
	echo
	echo "execute format(stmt, schema_name) using this_pk;"
	echo
	echo "insert into sync.log values(schema_name, 'Updated \"$table_name\" for \"$pk_name\" = ' || this_pk);"
	echo
	echo "perform sync.update_committed(schema_name, r.id_audit_history);";
	echo
	echo "exception when others then insert into sync.log values(schema_name ,sqlerrm);"
	echo
	echo "end \$\$ language 'plpgsql';"
	echo

done < "$tables_pk"

        stmt :=    'update public.'
                || quote_ident(TG_TABLE_NAME)
                || ' set deleted_when = now() ,deleted_by = '
                || ''''
                || current_user
                || '''';


        case TG_TABLE_NAME 
                when 'tblActions' then execute stmt using old."ActionID";
                when 'tblBlankRows' then execute stmt using old."BlankRowID";
                .../...