Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ojGOU-0003AL-Db for pgsql-sql@arkaria.postgresql.org; Fri, 14 Oct 2022 08:50:14 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ojGOT-0006wB-9G for pgsql-sql@arkaria.postgresql.org; Fri, 14 Oct 2022 08:50:13 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ojGOS-0006w2-Rp for pgsql-sql@lists.postgresql.org; Fri, 14 Oct 2022 08:50:13 +0000 Received: from mail.familiegard.de ([2a01:4f8:192:1144::88]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ojGOP-0006vf-MV for pgsql-sql@lists.postgresql.org; Fri, 14 Oct 2022 08:50:11 +0000 Received: from [IPV6:2a02:6d40:2143:b901:92f6:52ff:fe00:a664] (unknown [IPv6:2a02:6d40:2143:b901:92f6:52ff:fe00:a664]) (using TLSv1.3 with cipher TLS_AES_128_GCM_SHA256 (128/128 bits) key-exchange X25519 server-signature RSA-PSS (4096 bits) server-digest SHA256) (No client certificate requested) (Authenticated sender: frank@familie-gard.de) by mail.familiegard.de (Postfix) with ESMTPSA id 7C03E2A00DCD for ; Fri, 14 Oct 2022 10:50:07 +0200 (CEST) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=familie-gard.de; s=202201; t=1665737407; bh=zrH331KX+WHk7mtTae81DCtCB/PiJzM1Cdlci9lEnWk=; h=Date:Subject:To:References:From:In-Reply-To:From; b=hsO3OAB1lYe1Ea8QMJ7PKnxwMQbi9SyXH8b44b8AcUwGGGBGvqjAp9DckP/1Xs6uS f7n/BUzo8SGWfM2dKD2aFqy9m/RORy7PdOV3UOR551g7dzXhwFeeM4qBRRq3OSXgUK nnJ+VqW1G383VQubtAhxZjJjNPb8pWbDMV3JBta8= Content-Type: multipart/alternative; boundary="------------bD89ZQ0QQ8MJ904iPUHfIEWG" Message-ID: Date: Fri, 14 Oct 2022 10:50:06 +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: From: Frank Gard In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------bD89ZQ0QQ8MJ904iPUHfIEWG Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Sorry, c&p-error (was in hurry this morning). Omit "as" and "table", please:     EXECUTE 'insert into em_table select * from ' || a_name; Frank. Am 14.10.22 um 09:13 schrieb Frank Gard: > > 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 -- 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 --------------bD89ZQ0QQ8MJ904iPUHfIEWG Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit

Sorry,

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

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

Frank.

Am 14.10.22 um 09:13 schrieb Frank Gard:

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.
Siehe http://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

-- 
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.
Siehe http://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
--------------bD89ZQ0QQ8MJ904iPUHfIEWG--