sql - Select same values from different schema dynamically based on a common table -

i have database consisting of mutiple schemas same structure. enables segregate data based on enterprise. there mutiple schemas db_client1, db_client2 etc. , common properties defined in public schema. there 1 such common table definition below:

create table enterprises(     ent_id bigint,     schema_name character varying ) 

there requirement pick similar information tables schemas. each entry in table enterprises need select information corresponding schema_name. query fired each enterprise

select v1.no, v1.surrogate_id, v2.startdate, v2.enddate   table1 v1 inner join table2 v2 on v2.surrogate_id = v1.surrogate_id  

we require schema_name appended each table in above query table enterprises. how can data enterprises without using cursor. sample output looking

ent_id    no        surrogate_id         startdate         enddate 100001    1001      2001                 2014-03-01        2014-03-05 100001    1002      2002                 2014-03-01        2014-03-05 100001    1003      2003                 2014-03-01        2014-03-05 100002    1001      4001                 2014-03-01        2014-03-05 100002    1002      4002                 2014-03-01        2014-03-05 100003    1001      4001                 2014-03-01        2014-03-05 100003    1002      4002                 2014-03-01        2014-03-05 100004    1001      4001                 2014-03-01        2014-03-05 100004    1002      4002                 2014-03-01        2014-03-05 

thanks in advance,

you can use pl/pgsql stored function (with multiple return query execute) this.

something like:

create or replace function run_sql_all_enterprises(sql text) returns setof record language plpgsql $function$ declare     row public.enterprises%rowtype; begin     row in select * public.enterprises loop         return query execute format(sql, row.schema_name);     end loop; end $function$; 

and call like

select * run_sql_all_enterprises($sql$     select v1.no, v1.surrogate_id, v2.startdate, v2.enddate       %1$i.table1 v1     inner join %1$i.table2 v2 on v2.surrogate_id = v1.surrogate_id $sql$) (     no int,     surrogate_id int,     startdate date,     enddate date ); 

but, if (to crawl same structured data form multiple schemas), suggest re-normalize structure use 1 schema - column in each table suitable & points current row's enterprise.