I created a time-constraint table ... that is, rows have a valid-timespan (validvon - validbis, as in "valid from" - "valid until", with German intermixed)
CREATE TABLE otlabel
(
id_label serial NOT NULL,
id_lg integer,
name_l text,
letztespeicherung timestamp without time zone DEFAULT now(),
id_pkl serial NOT NULL,
validvon timestamp without time zone DEFAULT '-infinity'::timestamp without time zone,
validbis timestamp without time zone DEFAULT 'infinity'::timestamp without time zone,
id_user integer DEFAULT 0,
quarant integer DEFAULT 0,
CONSTRAINT otlabel_pkey PRIMARY KEY (id_pkl),
)
WITHOUT OIDS;
ALTER TABLE otlabel OWNER TO ibox;
to make it userproof, selecting, inserting, updating and deleting has to be done with a view:
CREATE OR REPLACE VIEW label AS
SELECT otlabel.id_label, otlabel.id_lg , otlabel.name_l, otlabel.letztespeicherung
FROM otlabel
WHERE now() >= otlabel.validvon AND now() <= otlabel.validbis AND otlabel.quarant = get_quarant();
CREATE OR REPLACE RULE label_delete AS
ON DELETE TO label DO INSTEAD UPDATE otlabel SET validbis = now(), letztespeicherung = now()
WHERE otlabel.id_label = old.id_label AND otlabel.validbis >= '9999-12-31 00:00:00'::timestamp without time zone AND otlabel.quarant = get_quarant();
CREATE OR REPLACE RULE label_insert AS
ON INSERT TO label DO INSTEAD INSERT INTO otlabel (id_label, id_lg, name_l, letztespeicherung, validvon, validbis, id_user, quarant)
VALUES (new.id_label, new.id_lg, new.name_l, now(), now(), 'infinity'::timestamp without time zone, get_user(), get_quarant());
CREATE OR REPLACE RULE label_update AS
ON UPDATE TO label DO INSTEAD ( UPDATE otlabel SET validbis = now(), letztespeicherung = now()
WHERE otlabel.id_label = old.id_label AND otlabel.validbis >= '9999-12-31 00:00:00'::timestamp without time zone AND otlabel.quarant = get_quarant();
INSERT INTO otlabel (id_label, id_lg, name_l, letztespeicherung, validvon, validbis, id_user, quarant)
VALUES (new.id_label, new.id_lg, new.name_l, now(), now(), 'infinity'::timestamp without time zone, get_user(), get_quarant());
);
Now I would be VERY VERY happy if I could trick pgAdmin into allowing me to edit this view within the Grid.
Any chance now? Or only with wihslist for 1.7? Or not at all?
No chance at all now as we're well past feature freeze. We can certainly accept a patch for 1.7 though - I guess we should look for INSERT/UPDATE/DELETE rules and enable the appropriate functions accordingly.
Regards Dave.
