Обсуждение: aggregate over tables in different schema

I've tables in different schemas all with the same name and
structure.
I'd like to compute an aggregate on the union of those tables.
I don't know the schemas in advance.
The list of the schema will be built selecting all the schemas that
contain a table with that name.

Other than building dynamically the statement as a list of union or
building up a view is there any other way?

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

Try using inheritance.

Il giorno 10 gen, 2010 2:30 m., "Ivan Sergio Borgonovo" <mail@webthatworks.it> ha scritto:

I've tables in different schemas all with the same name and
structure.
I'd like to compute an aggregate on the union of those tables.
I don't know the schemas in advance.
The list of the schema will be built selecting all the schemas that
contain a table with that name.

Other than building dynamically the statement as a list of union or
building up a view is there any other way?

--
Ivan Sergio Borgonovo
http://www.webthatworks.it


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sun, 10 Jan 2010 10:49:48 +0100
Vincenzo Romano <vincenzo.romano@notorand.it> wrote:

> Try using inheritance.

One of the things I didn't mention is: I've to join these tables
with other tables that may or may not (public) belong to the same
schema.

select sum(i.qty) from s1.list_items li
  join public.item i on i.itemid=li.itemid;

Now I'd like to pick up the sum over all list_items tables across
all the schemas.

If I define the summary table as the child of all the sub-tables I'm
going to write dynamic SQL anyway.
So I guess I should define a common ancestor for all the tables
(list_items) in different schema.

create public.list_items (
  itemid int primary key, // trouble
  name varchar(32)
);

create table s1.list_items (
) inherits (public.list_items);

create table s2.list_items (
) inherits (public.list_items);

But I can't see how am I going to write the query.

Furthermore the children should have their own pk and not share them.

thanks

--
Ivan Sergio Borgonovo
http://www.webthatworks.it