From: | Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: R: aggregate over tables in different schema |
Date: | 2010-01-11 11:35:56 |
Message-ID: | 20100111123556.53c2e8e3@dawn.webthatworks.it |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Sun, 10 Jan 2010 10:49:48 +0100
Vincenzo Romano <vincenzo(dot)romano(at)notorand(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Alban Hertroys | 2010-01-11 11:41:45 | Re: Get Unix timestamp from SQL timestamp through libpq |
Previous Message | Konrad Garus | 2010-01-11 11:30:32 | Re: Rows missing from table despite FK constraint |