On Thu, Jan 27, 2011 at 2:10 PM, Rich Shepard <[email protected]> wrote:
> Has anyone set up L123 and postgres so that each year's data are stored in
> separate schema? Rather than having all data in the same set of tables, I'd
> like to have schema for each year containing only that year's data. Being
> able to dynamically switch among years, or create comparison reports from
> different years would be nice, too.
I haven't done this as such but I have looked at doing similar things.
I think the simple way to do this would be with a set of RULEs,
inherited tables, and check constraints. So for example you might do
something like (pseudocode here):
CREATE SCHEMA yr_2011;
CREATE TABLE yr_2011.acc_trans (CHECK EXTRACT('YEAR' from transdate) =
'2010' )INHERITS (public.acc_trans);
CREATE DROP RULE acc_trans_i;
CREATE RULE acc_trans_i AS ON INSERT TO public.acc_trans DO INSTEAD
INSERT INTO yr_2011.....
In this way SQL-Ledger or LedgerSMB would continue to run as expected,
and only the tables needed to be scanned in the partition schemas
would be scanned. Usually this would be done when trying to partition
data by year in order to save on vacuum or clustering overhead.
Best Wishes,
Chris Travers
_______________________________________________
SQL-Ledger mailing list
[email protected]
http://lists.ledger123.com/mailman/listinfo/sql-ledger
