Subject: Append a list of tables to an empty table to form a whole table

From: Shaozhong SHI <[email-masked]>
Date: Fri, 14 Oct 2022 07:00:24 +0100

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

---

From: Frank Gard <[email-masked]>
Date: Fri, 14 Oct 2022 09:13:05 +0200

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.

-- 
Vielen Dank und viele Grüße,

Frank Gard
Zum Brünnchen 27
D-66583 Spiesen-Elversberg
Tel. : +49(6821)790880
E-Mail:[email-masked]

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

---

From: Frank Gard <[email-masked]>
Date: Fri, 14 Oct 2022 10:50:06 +0200

Sorry,

c&p-error (was in hurry this morning). Omit "as" and "table", please:

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

Frank.

Frank Gard
Zum Brünnchen 27

GnuPG / PGP info
================
Schlüssel-ID: 0xC8C1A552