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

[Ron <ronljohnsonjr@xxxxxxxxx>]

    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.