Dynamic UNION ALL query in Postgres

Question

We are using a Postgres / PostGis connection to get data that is published via a geoserver.

The Query looks like this at the moment:

SELECT 
    row_number() over (ORDER BY a.ogc_fid) AS qid, a.wkb_geometry AS geometry
FROM
(
   SELECT * FROM test
   UNION ALL
   SELECT * FROM test1
   UNION ALL
   SELECT * FROM test2
)a

In our db only valid shapefiles will be imported each in a single table so it would make sense to make the UNION ALL part dynamic (loop over each table and make the UNION ALL statement). Is there a way to do this in a standard Postgres way or do I need to write a function and how would the syntax look like? I am pretty new to SQL.

The shapefiles have a different data structure and only the ogc_fid column and the wkb_geometry column are always available and we would like to union all tables from the DB.

Answer 1

One solution is to serialize the rest of the columns to json with row_to_json(). (available since PostgreSQL9.2). For PG9.1 (and earlier) you can use hstore, but note that all values are cast to text.

Why serialize? It is not possible to union rows where the number of colums vary, or the datatypes do not match between the union queries.

I created a quick example to illustrate:

--DROP SCHEMA testschema CASCADE;
CREATE SCHEMA testschema;

CREATE TABLE testschema.test1 (
    id integer,
    fid integer,
    metadata text
);

CREATE TABLE testschema.test2 (
    id integer,
    fid integer,
    city text,
    count integer
);

CREATE TABLE testschema.test3 (
    id integer,
    fid integer
);


INSERT INTO testschema.test1 VALUES (1,   4450, 'lala');
INSERT INTO testschema.test2 VALUES (33,  6682, 'London', 12345);
INSERT INTO testschema.test3 VALUES (185, 8991);


SELECT 
    row_number() OVER (ORDER BY a.fid) AS qid, a.*
FROM
(
    SELECT id, fid, row_to_json(t.*) AS jsondoc FROM testschema.test1 t
    UNION ALL 
    SELECT id, fid, row_to_json(t.*) AS jsondoc FROM testschema.test2 t
    UNION ALL 
    SELECT id, fid, row_to_json(t.*) AS jsondoc FROM testschema.test3 t    
) a

SELECT output:

qid  id    fid    jsondoc
1;   1;    4450;  "{"id":1,"fid":4450,"metadata":"lala"}"
2;   33;   6682;  "{"id":33,"fid":6682,"city":"London","count":12345}"
3;   185;  8991;  "{"id":185,"fid":8991}"

Answer 2

This is just general guidelines you need work in the details specially syntaxis.

You need create a store procedure

Create a loop checking information_schema.tables filter for the tablenames you want

DECLARE    
    rec record;
    strSQL text;
BEGIN

Then create a strSQL with each table

 FOR rec IN SELECT table_schema, table_name
            FROM information_schema.tables                
 LOOP
     strSQL := strSQL || 'SELECT ogc_fid, wkb_geometry FROM ' || 
               rec.table_schema || '.' || rec.table_name || ' UNION ';
 END LOOP;

-- have to remove the last ' UNION ' from strSQL    

strSQL := 'SELECT  row_number() over (ORDER BY a.ogc_fid) AS qid,
         a.wkb_geometry AS geometry FROM (' || strSQL || ')';

EXECUTE strSQL;