Re: Aggregating data from stored procedures across multiple schemas

Thanks for the response. Sorry I was not clear. We have a function
defined in all schemas but we need to call them from outside the schema
using a connection to the database without a search path. We were using a
union all between schemas. Ex: select * from schema1.function1() union all
select * from schema2.function1(). Since the function is not defined
outside the schemas I was getting an error. I

Either way I think we have found the solution. We are using Dynamic SQL
function from this post
http://clarkdave.net/2015/06/aggregate-queries-across-postgresql-schemas/

Thanks for the response.

On Thu, Jun 2, 2016 at 2:53 AM Ashutosh Bapat <
ashutosh(dot)bapat(at)enterprisedb(dot)com> wrote:

> On Thu, Jun 2, 2016 at 4:10 AM, Rich Compton <rich(dot)compton(at)curvedental(dot)com
> > wrote:
>
>> This is my first post so bear with me. I will most likely miss
>> information.
>>
>> Our current architecture is that each of our clients has their own
>> schemas. We have some clients considered enterprise clients that actually
>> own several offices (schemas). We are looking into aggregating data for
>> them for an enterprise portal of sorts. Each of our schemas have
>> procedures that tally all the production data we need. I am looking for a
>> way to aggregate all the data across multiple schemas using the stored
>> procedures.
>>
>> My first vain attempt was with union all which works with views and
>> tables but not functions since they are not defined outside the schemas
>> themselves.
>>
>
> I don't understand this. Set returning functions can be queried just like
> views or tables. They can be schema qualified as well. An example might
> help.
>
> --
> Best Wishes,
> Ashutosh Bapat
> EnterpriseDB Corporation
> The Postgres Database Company
>
--

Rich Compton
Senior Software Developer - Curve Capture
Curvedental LTD