Re: bash or sql script to copy foreign table to locale table

Hi  Ron,

It works

Thanks for your  contribution

khoff.

Le 20/05/2022 à 05:17, Ron a écrit :
> Please do not hijack existing threads.
>
> On 5/19/22 18:55, koff wrote:
>> Hi all,
>>
>> I need little help  please,
>>
>> I'v  already   foreign tables (from oracle_fdw) in my postgresql
>> environment.
>>
>> I'm Just  looking for  script shell  or sql  ( easy way)  to migrate 
>> this  foreign tables   to locale  table .
>>
>> My script below  has some issue
>>
>> thanks lot
>>
>> ------script migrate---
>>
>> #!/bin/bash
>> export PGDATA=/pg01/pgdata/9.6/data
>> export PGUSER="postgres"
>> export PGPORT="5433"
>> export PGPASSWORD="ttooe2"
>>
>> for tbl in `psql -qAt   data_2020 -c "select  foreign_table_name 
>> from information_schema.foreign_tables;"`;
>> do
>                   ## note the added semicolon
>>     psql data_2020  -c "create table public.\"$tbl\" as select * from
>> fdw_link.\"$tbl\";"
> Here, either psql COPY the fdw_link.\"$tbl\" data OUT to a local file,
> and then COPY IN to public.\"$tbl\" *OR* INSERT INTO public.\"$tbl\"
> SELECT * FROM fdw_link.\"$tbl\";
>
>> done
>>
>> ----end  of script---
>>
>> the database is :  data_2020
>>
>>  foreign_table schema   is :  fdw_link
>>
>> and want to copy  to public  schema
>>
>>
>>
>>
>>
>>
>
> --
> Angular momentum makes the world go 'round.