Postgresql - 如何按特定列选择多个表并 append 它们

Question

我想选择一些表格并在每个表格中选择几何(geom)和名称列并在彼此下方追加。我已经选择了表格及其列，如下所示:

SELECT TABLE_NAME COLUMN_NAME
FROM INFORMATION_SCHEMA.columns
WHERE (TABLE_NAME LIKE '%HESA' OR 
      TABLE_NAME LIKE '%HEWH') AND 
      (COLUMN_NAME = 'geom' AND
       COLUMN_NAME = 'Name');

然后你如何取表:

id | geom  | Name  |  id | geom  | Name  |
____________________  ____________________
1  | geom1 | Name1 |  1  | geom4 | Name4 |
2  | geom2 | Name2 |  2  | geom5 | Name5 |
3  | geom3 | Name3 |  3  | geom6 | Name6 |

然后将第二个表格 append 到第一个表格下方，如下所示:

id | geom  | Name  |
____________________
1  | geom1 | Name1 |
2  | geom2 | Name2 |
3  | geom3 | Name3 |
1  | geom4 | Name4 |
2  | geom5 | Name5 |
3  | geom6 | Name6 |

我是使用 UNION ALL 还是其他东西？

Answer 1

https://www.db-fiddle.com/f/75fgQMEWf9LvPj4xYMGWvA/0

根据您的示例数据:

do
'
declare
 r record;
begin
 for r in (
SELECT a.TABLE_NAME
FROM INFORMATION_SCHEMA.columns a
JOIN INFORMATION_SCHEMA.columns b on a.TABLE_NAME = b.TABLE_NAME and a.COLUMN_NAME = ''geom'' and b.COLUMN_NAME = ''name''
WHERE (a.TABLE_NAME LIKE ''oranges%'' OR a.TABLE_NAME LIKE ''%_db'') 
 ) loop
    execute format(''insert into rslt select geom, name from %I'',r.table_name);
 end loop;
end;
'
;