On Sun, Dec 16, 2001 at 02:54:43AM +0100, Peter Pilsl wrote:
> I've a very big table that is read very frequently and I want to split
> this table in more subtables with different permissions for
> insert/update/delete. I need to keep the big table too, cause the
> reading application can only deal with one table.
Sounds like you might be interested in inheritence. Only it works the other
way round. Each select on the main table is equivalent to a union of selects
on all the subtables.
Like so:
create table maintable ( yadda );
create subtable1 () inherits (maintable);
create subtable2 () inherits (maintable);
insert into subtable1 values ( blah );
insert into subtable2 values ( blah );
Now, selecting either subtable1 one or suntable2 will produce one row each,
but selecting from maintable will produce both rows.
> So I am thinking about generating a set of update,insert,delete-rules
> for each of the 'subtables' that will update,insert,delete the
> 'maintable'.
> I'm not worried about the needed time, cause write access is very
> seldom, but I'm needed about the effort to implement this solution.
> First I've to add a new oid-column to establish the relation between
> the subtables and the maintable, then I've to create three long rules
> for each subtable with long insert,update,delete-commands and in fact
> I dont only have 1 table to split but a load.
Although this is probably doable in the end, the solution given above is
certainly much easier to code, though I give guarentees about performance.
Oh, and foreign keys and unique indicies won't work on the table either.
HTH,
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/
> Terrorists can only take my life. Only my government can take my freedom.