Content-Type: multipart/alternative;
 boundary="------------Zc2o1wCvyPOOUSdhtSFoB3M3"
Message-ID: <d3bc0de9-6058-4643-30af-6937453ac870@gmail.com>
Date: Thu, 19 May 2022 22:17:58 -0500
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101
 Thunderbird/91.8.1
Subject: Re: bash or sql script to copy foreign table to locale table
Content-Language: en-US
To: pgsql-admin@lists.postgresql.org
References: 
 <MW4PR17MB4513FBC690156566FB0E5A17ABED9@MW4PR17MB4513.namprd17.prod.outlook.com>
 <CAMa=N=NBvJv9vtCPz67a4jEP57ioVJwfTvscKXpA3JNidC-pKw@mail.gmail.com>
 <MW4PR17MB4513397D5F05F0013DDDC150ABEC9@MW4PR17MB4513.namprd17.prod.outlook.com>
 <PAXP193MB15339A6E149E3CB509AD2827CAD09@PAXP193MB1533.EURP193.PROD.OUTLOOK.COM>
From: Ron <ronljohnsonjr@gmail.com>
In-Reply-To: 
 <PAXP193MB15339A6E149E3CB509AD2827CAD09@PAXP193MB1533.EURP193.PROD.OUTLOOK.COM>
Archived-At: 
 <https://www.postgresql.org/message-id/d3bc0de9-6058-4643-30af-6937453ac870%40gmail.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------Zc2o1wCvyPOOUSdhtSFoB3M3
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

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.
--------------Zc2o1wCvyPOOUSdhtSFoB3M3
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    Please do not hijack existing threads.<br>
    <br>
    <div class="moz-cite-prefix">On 5/19/22 18:55, koff wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:PAXP193MB15339A6E149E3CB509AD2827CAD09@PAXP193MB1533.EURP193.PROD.OUTLOOK.COM">Hi
      all,
      <br>
      <br>
      I need little help  please,
      <br>
      <br>
      I'v  already   foreign tables (from oracle_fdw) in my  postgresql
      environment.
      <br>
      <br>
      I'm Just  looking for  script shell  or sql  ( easy way)  to
      migrate  this  foreign tables   to locale  table .
      <br>
      <br>
      My script below  has some issue
      <br>
      <br>
      thanks lot
      <br>
      <br>
      ------script migrate---
      <br>
      <br>
      #!/bin/bash
      <br>
      export PGDATA=/pg01/pgdata/9.6/data
      <br>
      export PGUSER="postgres"
      <br>
      export PGPORT="5433"
      <br>
      export PGPASSWORD="ttooe2"
      <br>
      <br>
      <font face="monospace">for tbl in `psql -qAt   data_2020
        -c "select  foreign_table_name  from
        information_schema.foreign_tables;"`;
        <br>
        do</font><br>
    </blockquote>
    <font face="monospace">                  ## note the added semicolon</font><br>
    <blockquote type="cite"
cite="mid:PAXP193MB15339A6E149E3CB509AD2827CAD09@PAXP193MB1533.EURP193.PROD.OUTLOOK.COM"><font
        face="monospace">    psql data_2020  -c "create table
        public.\"$tbl\" as select * from fdw_link.\"$tbl\";"</font></blockquote>
    Here, either psql COPY the <font face="monospace">fdw_link.\"$tbl\"</font>
    data OUT to a local file, and then COPY IN to <font
      face="monospace">public.\"$tbl\"</font> <b>OR</b> INSERT INTO <font
      face="monospace">public.\"$tbl\"</font> SELECT * FROM <font
      face="monospace">fdw_link.\"$tbl\";</font><br>
    <br>
    <blockquote type="cite"
cite="mid:PAXP193MB15339A6E149E3CB509AD2827CAD09@PAXP193MB1533.EURP193.PROD.OUTLOOK.COM"><font
        face="monospace">done
        <br>
      </font><br>
      ----end  of script---
      <br>
      <br>
      the database is :  data_2020
      <br>
      <br>
       foreign_table schema   is :  fdw_link
      <br>
      <br>
      and want to copy  to public  schema
      <br>
      <br>
      <br>
      <br>
      <br>
      <br>
      <br>
    </blockquote>
    <br>
    <div class="moz-signature">-- <br>
      Angular momentum makes the world go 'round.</div>
  </body>
</html>

--------------Zc2o1wCvyPOOUSdhtSFoB3M3--