Hi all,
first let me tell you that this nice "whole-row comparison" feature of
postgres 8.2 allowed me to create a versionned database model very
neatly. The SQL statement that inserts in the destination table only
the one rows that are new or that have changed since last time is very
simply written:
INSERT INTO bsc_table
SELECT nextval('version_seq'), <fields>
FROM load.bsc_table AS ld LEFT JOIN bsc_view AS nt USING (obj_id)
WHERE nt.obj_id IS NULL OR row(nt.*) <> row(ld.*);
bsc_view is a view that returns the latest version of each object in the
bsc table:
CREATE VIEW bsc_view AS
SELECT <fields>
FROM bsc_table
WHERE (obj_id, ver_id) IN (SELECT obj_id, max(ver_id) FROM bsc_table
GROUP BY obj_id);
This is all nice as long as I only want to access the very last version
of the table. However what should be done if I now need to access an
earlier version ? The most elegant way would be to pass a ver_id
parameter to bsc_view, something like :
CREATE VIEW bsc_view(int) AS
SELECT <fields>
FROM bsc_table
WHERE (obj_id, ver_id) IN
(SELECT obj_id, max(ver_id)
FROM bsc_table
WHERE ver_id <= $1
GROUP BY obj_id));
However postgres doesn't allow parameters in views as far as I know. I
guess I could create a function returning a set of rows, but then I
would lose most advantages of rewritten views, especially optimization,
right ?
I've contemplated reusing an awful hack from my Access era, namely using
a single-rowed table to store the parameter and joining the view on it.
The parameter would be updated before the view is called; this would
work but would definitely be ugly. Can someone think of a better way to
do that ?
Thanks a lot,
Christian
-----Original Message-----
I'm trying to implement a "versionned" storage for a datawarehouse
system, meaning I have tables which store daily information about
objects and I would like to have a row inserted (i.e. a new version)
only if it differs from the most recent existing row. For instance
instead of storing
version attribute1 attribute2
1 x y
2 x y
3 x y
4 z y
5 z y
6 z t
we would only keep the diffs :
version attribute1 attribute2
1 x y
4 z y
6 z t
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
