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

<br />I created a time-constraint table ... that is, rows have a valid-timespan (validvon - validbis, as in "valid
from"- "valid until", with German intermixed)<br /><br />CREATE TABLE otlabel<br />(<br />  id_label serial NOT NULL,
<br/>  id_lg integer,<br />  name_l text,<br />  letztespeicherung timestamp without time zone DEFAULT now(),<br /> 
id_pklserial NOT NULL,<br />  validvon timestamp without time zone DEFAULT '-infinity'::timestamp without time zone,
<br/>  validbis timestamp without time zone DEFAULT 'infinity'::timestamp without time zone,<br />  id_user integer
DEFAULT0,<br />  quarant integer DEFAULT 0,<br />  CONSTRAINT otlabel_pkey PRIMARY KEY (id_pkl),<br />) <br />WITHOUT
OIDS;<br />ALTER TABLE otlabel OWNER TO ibox;<br /><br />to make it userproof, selecting, inserting, updating and
deletinghas to be done with a view:<br /><br />CREATE OR REPLACE VIEW label AS <br /> SELECT otlabel.id_label,
otlabel.id_lg, otlabel.name_l, otlabel.letztespeicherung<br />   FROM otlabel<br />  WHERE now() >= otlabel.validvon
ANDnow() <= otlabel.validbis AND otlabel.quarant = get_quarant();<br /><br />CREATE OR REPLACE RULE label_delete
AS<br/>     ON DELETE TO label DO INSTEAD  UPDATE otlabel SET validbis = now(), letztespeicherung = now()<br />  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();<br /><br />CREATE OR REPLACE RULE label_insert AS<br />    ON INSERT TO label DO
INSTEAD INSERT INTO otlabel (id_label, id_lg, name_l, letztespeicherung, validvon, validbis, id_user, quarant) <br />  
VALUES(new.id_label, new.id_lg, new.name_l, now(), now(), 'infinity'::timestamp without time zone, get_user(),
get_quarant());<br/><br />CREATE OR REPLACE RULE label_update AS<br />    ON UPDATE TO label DO INSTEAD ( UPDATE
otlabelSET validbis = now(), letztespeicherung = now() <br />  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();<br
/> INSERTINTO otlabel (id_label, id_lg, name_l, letztespeicherung, validvon, validbis, id_user, quarant) <br />  VALUES
(new.id_label,new.id_lg, new.name_l, now(), now(), 'infinity'::timestamp without time zone, get_user(),
get_quarant());<br/>);<br /><br />Now I would be VERY VERY happy if I could trick pgAdmin into allowing me to edit this
viewwithin the Grid. <br /><br />Any chance now? Or only with wihslist for 1.7? Or not at all?<br /><br />Harald<br
/>--<br />GHUM Harald Massa<br />persuadere et programmare<br />Harald Armin Massa<br />Reinsburgstraße 202b<br />70197
Stuttgart<br/>0173/9409607 <br />-<br />Python: the only language with more web frameworks than keywords.  

---

From: pgadmin-support-owner@postgresql.org [mailto:pgadmin-support-owner@postgresql.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?