Content-Type: multipart/alternative;
 boundary="------------5tfyAIUPVVeXOApWLqlRDG4w"
Message-ID: <d4468415-90b4-7560-849b-d35acc94de56@familie-gard.de>
Date: Fri, 14 Oct 2022 09:13:05 +0200
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.3.0
Subject: Re: Append a list of tables to an empty table to form a whole table
Content-Language: de-DE
To: pgsql-sql@lists.postgresql.org
References: 
 <CA+i5JwYX9jDXv2fS9u6JP24=xpaTX_r6SXMjUCg5JGwTA2cPog@mail.gmail.com>
 <CA+i5JwaD2rz-jJdm9_SK=88z=J0SDNmij98hMZxCwLuHxbz2Mw@mail.gmail.com>
From: Frank Gard <frank.von.postgresql.org@familie-gard.de>
In-Reply-To: 
 <CA+i5JwaD2rz-jJdm9_SK=88z=J0SDNmij98hMZxCwLuHxbz2Mw@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/d4468415-90b4-7560-849b-d35acc94de56%40familie-gard.de>
Precedence: bulk

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

Hi,

again: Use EXECUTE! It's well documented here: https://www.postgresql.org/docs/15/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

In your case, you can simply replace your INSERT statement by the following:

     EXECUTE 'insert into em_table as select * from table ' || a_name;

Hope this helps.

Frank.

Am 14.10.22 um 08:00 schrieb Shaozhong SHI:
>
>
> How best to append a list of tables to an empty table to form a whole table?
>
> Pseudo codes show the desirable logic.
>
>
>
> There are a list of tables with exactly same colums
> table_a
> table_b
> table_c
>
> Create an empty table  emp_table
>
> Foreach a_name in ARRAY ARRAY['table_a', 'table_b', 'table_c'] loop
>    insert into em_table as select * from table a_name
>
> end loop;
>
> Can something like that be done is PostGIS?
>
> Regards,
>
> David

-- 
Vielen Dank und viele Grüße,

Frank Gard
Zum Brünnchen 27
D-66583 Spiesen-Elversberg
Tel. : +49(6821)790880
E-Mail:frank@familie-gard.de

Bitte denken Sie über Ihre Verantwortung gegenüber der Umwelt nach,
bevor Sie diese Mail ausdrucken.

Bitte senden Sie mir keine Word-, Excel- oder PowerPoint-Anhänge.
Siehehttp://www.gnu.org/philosophy/no-word-attachments.de.html

GnuPG / PGP info
================
Schlüssel-ID: 0xC8C1A552
Fingerabdruck / fingerprint:
3EFD EF94 4841 38B5 DB40 95D8 C69C 71C5 C8C1 A552

--------------5tfyAIUPVVeXOApWLqlRDG4w
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>
    <p>Hi,</p>
    <p>again: Use EXECUTE! It's well documented here:
<a class="moz-txt-link-freetext" href="https://www.postgresql.org/docs/15/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN">https://www.postgresql.org/docs/15/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN</a></p>
    <p>In your case, you can simply replace your INSERT statement by the
      following:</p>
    <p>    EXECUTE 'insert into em_table as select * from table ' ||
      a_name;</p>
    <p>Hope this helps.</p>
    <p>Frank.<br>
    </p>
    <div class="moz-cite-prefix">Am 14.10.22 um 08:00 schrieb Shaozhong
      SHI:<br>
    </div>
    <blockquote type="cite"
cite="mid:CA+i5JwaD2rz-jJdm9_SK=88z=J0SDNmij98hMZxCwLuHxbz2Mw@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div dir="ltr">
        <div class="gmail_quote"><br>
          <br>
          <div dir="ltr">
            <div dir="ltr">
              <div dir="ltr">How best to append a list of tables to an
                empty table to form a whole table?
                <div><br>
                </div>
                <div>Pseudo codes show the desirable logic.</div>
                <div><br>
                </div>
                <div><br>
                </div>
                <div>
                  <div><br>
                  </div>
                  <div>There are a list of tables with exactly same
                    colums</div>
                  <div>table_a</div>
                  <div>table_b</div>
                  <div>table_c</div>
                  <div><br>
                  </div>
                  <div>Create an empty table  emp_table</div>
                  <div><br>
                  </div>
                  <div>Foreach a_name in ARRAY ARRAY['table_a',
                    'table_b', 'table_c'] loop</div>
                  <div>   insert into em_table as select * from table
                    a_name</div>
                  <div><br>
                  </div>
                  <div>end loop;</div>
                </div>
                <div><br>
                </div>
                <div>Can something like that be done is PostGIS?</div>
                <div><br>
                </div>
                <div>Regards,</div>
                <div><br>
                </div>
                <div>David</div>
              </div>
            </div>
          </div>
        </div>
      </div>
    </blockquote>
    <pre class="moz-signature" cols="272">-- 
Vielen Dank und viele Grüße,

Frank Gard
Zum Brünnchen 27
D-66583 Spiesen-Elversberg
Tel. : +49(6821)790880
E-Mail: <a class="moz-txt-link-abbreviated" href="mailto:frank@familie-gard.de">frank@familie-gard.de</a>

Bitte denken Sie über Ihre Verantwortung gegenüber der Umwelt nach,
bevor Sie diese Mail ausdrucken.

Bitte senden Sie mir keine Word-, Excel- oder PowerPoint-Anhänge.
Siehe <a class="moz-txt-link-freetext" href="http://www.gnu.org/philosophy/no-word-attachments.de.html">http://www.gnu.org/philosophy/no-word-attachments.de.html</a>

GnuPG / PGP info
================
Schlüssel-ID: 0xC8C1A552
Fingerabdruck / fingerprint:
3EFD EF94 4841 38B5 DB40 95D8 C69C 71C5 C8C1 A552
</pre>
  </body>
</html>

--------------5tfyAIUPVVeXOApWLqlRDG4w--