Re: Treat an updateable View as a Table in Grid editor?

________________________________

From: pgadmin-support-owner(at)postgresql(dot)org
[mailto:pgadmin-support-owner(at)postgresql(dot)org] On Behalf Of Harald Armin
Massa
Sent: 05 October 2006 08:31
To: pgAdmin Support
Subject: [pgadmin-support] Treat an updateable View as a Table
in Grid editor?

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.