From: | "Harald Armin Massa" <haraldarminmassa(at)gmail(dot)com> |
---|---|
To: | "pgAdmin Support" <pgadmin-support(at)postgresql(dot)org> |
Subject: | Treat an updateable View as a Table in Grid editor? |
Date: | 2006-10-05 07:30:49 |
Message-ID: | 7be3f35d0610050030g43cde42av89d0ee03fe353f7@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgadmin-support |
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?
Harald
--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
-
Python: the only language with more web frameworks than keywords.
From | Date | Subject | |
---|---|---|---|
Next Message | Dave Page | 2006-10-05 07:33:36 | Re: Treat an updateable View as a Table in Grid editor? |
Previous Message | Hiroshi Saito | 2006-10-05 07:29:46 | Re: [pgadmin-support] Bug report on insert with pgadmin |