3 added 13 characters in body
source | link

In case you need assistance in merging all data, you could do it this way:

DO $DO$
DECLARE
  _tab text;
  _r record;
BEGIN
  FOR _r IN
    -- refine the following query according to your particular needs, if the following doesn't work well
    SELECT a.attrelid::regclass::text AS table_name
    FROM pg_attribute a
    JOIN pg_class c ON (c.oid = a.attrelid)
    WHERE a.attrelid IN (SELECT attrelid FROM pg_attribute WHERE attname ILIKE 'geom') AND -- any table with a "geom" column name
          c.relnamespace > 11 AND c.relkind = 'r'
    GROUP BY a.attrelid
    HAVING COUNT(*) = 3 -- only tables with three columns
    ORDER BY 1
   LOOP
     IF _tab IS NULL THEN
       _tab := _r.table_name;
       EXECUTE 'CREATE TEMP TABLE merged_multi_polygons (tablename text, LIKE '||_tab||');';
     END IF;
     EXECUTE 'INSERT INTO merged_multi_polygons SELECT '||quote_literal(_r.table_name)||', * FROM '||_r.table_name
             -- ||' WHERE size < 20' -- UNCOMMENT THIS LINE TO PRODUCE THE TABLE ONLY FOR SMALL-SIZED ENTRIES
             ;|| ' LIMIT 1';
   END LOOP;
END;
$DO$;

SELECT * FROM merged_multi_polygons WHERE size < 20;

In case you need assistance in merging all data, you could do it this way:

DO $DO$
DECLARE
  _tab text;
  _r record;
BEGIN
  FOR _r IN
    -- refine the following query according to your particular needs, if the following doesn't work well
    SELECT a.attrelid::regclass::text AS table_name
    FROM pg_attribute a
    JOIN pg_class c ON (c.oid = a.attrelid)
    WHERE a.attrelid IN (SELECT attrelid FROM pg_attribute WHERE attname ILIKE 'geom') AND -- any table with a "geom" column name
          c.relnamespace > 11 AND c.relkind = 'r'
    GROUP BY a.attrelid
    HAVING COUNT(*) = 3 -- only tables with three columns
    ORDER BY 1
   LOOP
     IF _tab IS NULL THEN
       _tab := _r.table_name;
       EXECUTE 'CREATE TEMP TABLE merged_multi_polygons (tablename text, LIKE '||_tab||');';
     END IF;
     EXECUTE 'INSERT INTO merged_multi_polygons SELECT '||quote_literal(_r.table_name)||', * FROM '||_r.table_name
             -- ||' WHERE size < 20' -- UNCOMMENT THIS LINE TO PRODUCE THE TABLE ONLY FOR SMALL-SIZED ENTRIES
             ;
   END LOOP;
END;
$DO$;

SELECT * FROM merged_multi_polygons WHERE size < 20;

In case you need assistance in merging all data, you could do it this way:

DO $DO$
DECLARE
  _tab text;
  _r record;
BEGIN
  FOR _r IN
    -- refine the following query according to your particular needs, if the following doesn't work well
    SELECT a.attrelid::regclass::text AS table_name
    FROM pg_attribute a
    JOIN pg_class c ON (c.oid = a.attrelid)
    WHERE a.attrelid IN (SELECT attrelid FROM pg_attribute WHERE attname ILIKE 'geom') AND -- any table with a "geom" column name
          c.relnamespace > 11 AND c.relkind = 'r'
    GROUP BY a.attrelid
    HAVING COUNT(*) = 3 -- only tables with three columns
    ORDER BY 1
   LOOP
     IF _tab IS NULL THEN
       _tab := _r.table_name;
       EXECUTE 'CREATE TEMP TABLE merged_multi_polygons (tablename text, LIKE '||_tab||');';
     END IF;
     EXECUTE 'INSERT INTO merged_multi_polygons SELECT '||quote_literal(_r.table_name)||', * FROM '||_r.table_name
             -- ||' WHERE size < 20' -- UNCOMMENT THIS LINE TO PRODUCE THE TABLE ONLY FOR SMALL-SIZED ENTRIES
             || ' LIMIT 1';
   END LOOP;
END;
$DO$;

SELECT * FROM merged_multi_polygons WHERE size < 20;
2 Fixed bug: quote_ident => quote_literal
source | link

In case you need assistance in merging all data, you could do it this way:

DO $DO$
DECLARE
  _tab text;
  _r record;
BEGIN
  FOR _r IN
    -- refine the following query according to your particular needs, if the following doesn't work well
    SELECT a.attrelid::regclass::text AS table_name
    FROM pg_attribute a
    JOIN pg_class c ON (c.oid = a.attrelid)
    WHERE a.attrelid IN (SELECT attrelid FROM pg_attribute WHERE attname ILIKE 'geom') AND -- any table with a "geom" column name
          c.relnamespace > 11 AND c.relkind = 'r'
    GROUP BY a.attrelid
    HAVING COUNT(*) = 3 -- only tables with three columns
    ORDER BY 1
   LOOP
     IF _tab IS NULL THEN
       _tab := _r.table_name;
       EXECUTE 'CREATE TEMP TABLE merged_multi_polygons (tablename text, LIKE '||_tab||');';
     END IF;
     EXECUTE 'INSERT INTO merged_multi_polygons SELECT '||quote_ident'||quote_literal(_r.table_name)||', * FROM '||_r.table_name
             -- ||' WHERE size < 20' -- UNCOMMENT THIS LINE TO PRODUCE THE TABLE ONLY FOR SMALL-SIZED ENTRIES
             ;
   END LOOP;
END;
$DO$;

SELECT * FROM merged_multi_polygons WHERE size < 20;

In case you need assistance in merging all data, you could do it this way:

DO $DO$
DECLARE
  _tab text;
  _r record;
BEGIN
  FOR _r IN
    -- refine the following query according to your particular needs, if the following doesn't work well
    SELECT a.attrelid::regclass::text AS table_name
    FROM pg_attribute a
    JOIN pg_class c ON (c.oid = a.attrelid)
    WHERE a.attrelid IN (SELECT attrelid FROM pg_attribute WHERE attname ILIKE 'geom') AND -- any table with a "geom" column name
          c.relnamespace > 11 AND c.relkind = 'r'
    GROUP BY a.attrelid
    HAVING COUNT(*) = 3 -- only tables with three columns
    ORDER BY 1
   LOOP
     IF _tab IS NULL THEN
       _tab := _r.table_name;
       EXECUTE 'CREATE TEMP TABLE merged_multi_polygons (tablename text, LIKE '||_tab||');';
     END IF;
     EXECUTE 'INSERT INTO merged_multi_polygons SELECT '||quote_ident(_r.table_name)||', * FROM '||_r.table_name
             -- ||' WHERE size < 20' -- UNCOMMENT THIS LINE TO PRODUCE THE TABLE ONLY FOR SMALL-SIZED ENTRIES
             ;
   END LOOP;
END;
$DO$;

SELECT * FROM merged_multi_polygons WHERE size < 20;

In case you need assistance in merging all data, you could do it this way:

DO $DO$
DECLARE
  _tab text;
  _r record;
BEGIN
  FOR _r IN
    -- refine the following query according to your particular needs, if the following doesn't work well
    SELECT a.attrelid::regclass::text AS table_name
    FROM pg_attribute a
    JOIN pg_class c ON (c.oid = a.attrelid)
    WHERE a.attrelid IN (SELECT attrelid FROM pg_attribute WHERE attname ILIKE 'geom') AND -- any table with a "geom" column name
          c.relnamespace > 11 AND c.relkind = 'r'
    GROUP BY a.attrelid
    HAVING COUNT(*) = 3 -- only tables with three columns
    ORDER BY 1
   LOOP
     IF _tab IS NULL THEN
       _tab := _r.table_name;
       EXECUTE 'CREATE TEMP TABLE merged_multi_polygons (tablename text, LIKE '||_tab||');';
     END IF;
     EXECUTE 'INSERT INTO merged_multi_polygons SELECT '||quote_literal(_r.table_name)||', * FROM '||_r.table_name
             -- ||' WHERE size < 20' -- UNCOMMENT THIS LINE TO PRODUCE THE TABLE ONLY FOR SMALL-SIZED ENTRIES
             ;
   END LOOP;
END;
$DO$;

SELECT * FROM merged_multi_polygons WHERE size < 20;
1
source | link

In case you need assistance in merging all data, you could do it this way:

DO $DO$
DECLARE
  _tab text;
  _r record;
BEGIN
  FOR _r IN
    -- refine the following query according to your particular needs, if the following doesn't work well
    SELECT a.attrelid::regclass::text AS table_name
    FROM pg_attribute a
    JOIN pg_class c ON (c.oid = a.attrelid)
    WHERE a.attrelid IN (SELECT attrelid FROM pg_attribute WHERE attname ILIKE 'geom') AND -- any table with a "geom" column name
          c.relnamespace > 11 AND c.relkind = 'r'
    GROUP BY a.attrelid
    HAVING COUNT(*) = 3 -- only tables with three columns
    ORDER BY 1
   LOOP
     IF _tab IS NULL THEN
       _tab := _r.table_name;
       EXECUTE 'CREATE TEMP TABLE merged_multi_polygons (tablename text, LIKE '||_tab||');';
     END IF;
     EXECUTE 'INSERT INTO merged_multi_polygons SELECT '||quote_ident(_r.table_name)||', * FROM '||_r.table_name
             -- ||' WHERE size < 20' -- UNCOMMENT THIS LINE TO PRODUCE THE TABLE ONLY FOR SMALL-SIZED ENTRIES
             ;
   END LOOP;
END;
$DO$;

SELECT * FROM merged_multi_polygons WHERE size < 20;