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

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.