Combine similar data from multiple schemas

Question

I have a table in a common schema which contain details about all enterprises of the application like id, name, logo, web_site_address, schema_name etc. Then there are schemas for each enterprise sharing same data structure. Now there is a requirement to find some information for all enterprises. I have written the following query in a function to do this. However, I feel there must be a better way to do this without using a CREATE TEMP TABLE.
Any help refactoring this would be highly appreciated.

CREATE TEMPORARY TABLE IF NOT EXISTS temp_vehicles(plateno character varying, vehiclesurrogate_id bigint, devicesurrogate_id bigint, startdate  timestamp with time zone, enddate  timestamp with time zone);
    EXECUTE 'TRUNCATE TABLE temp_vehicles';

FOR r IN SELECT * FROM platformdb.entreprise_details LOOP
    EXECUTE 'SET SEARCH_PATH TO ' || r.schema_name || ', public';
    EXECUTE 'INSERT INTO temp_vehicles SELECT v.plateno, v.vehiclesurrogate_id, vda.devicesurrogate_id, vda.startdate, vda.enddate
                                FROM ' || r.schema_name || '.object_access_vehicle() v
                                INNER JOIN ' || r.schema_name || '.vehicledeviceassignment vda on vda.vehiclesurrogate_id = v.vehiclesurrogate_id AND overlap(startdate, enddate, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
                                INNER JOIN platformdb.lt_enterprise_device lted ON lted.device_surrogate_id = vda.devicesurrogate_id AND overlap(lted.startdate, lted.enddate, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP) AND enterprise_surrogate_id = ' || r.entreprise_surrogate_id;
END LOOP;

Thanks,

Jayadevan · Accepted Answer · 2013-11-18 12:46:50Z

1

You can create a view which is a union of these tables and use that? Create view myview () as select ... from schema1.table1 union select ... from schema2.table2 union..

answered Nov 18, 2013 at 12:46

Jayadevan

1,4342 gold badges16 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user2459396 Over a year ago

These schema are not fixed, if a schema is added then I need to update the view.

Jayadevan Over a year ago

OK. What about having a parent table in public and all the other schema tables inheriting the parent table? The you can query the parent table to get all the data?

user2459396 Over a year ago

Unfortunately, we cant do that. We just need to refactor the query to get rid of temporary table creation

Jayadevan Over a year ago

If it is just the temp table creation that is an issue, you could create a union query and execute it using the prepare and execute? link

Collectives™ on Stack Overflow

Combine similar data from multiple schemas

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related