Date: Mon, 20 Jun 2005 13:49:36 -0700
To: "Jaime Casanova" <systemguards@gmail.com>
Subject: Re: UPDATEABLE VIEWS ... Examples?
Cc: pgsql-sql@postgresql.org
References: <20050616180356.Y90456@ganymede.hub.org>
	<20050620071604.GB15942@apartia.fr>
	<c2d9e70e050620002430cb0c38@mail.gmail.com>
From: Din Adrian <adrian.din@easynet.ro>
Organization: OMC
Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-ID: <opssor4yu4awcxfg@adi>
In-Reply-To: <c2d9e70e050620002430cb0c38@mail.gmail.com>
User-Agent: Opera M2/7.54 (Win32, build 3929)

An example(found it some time ago somewhere ?! :) ):

/*
drop view a_and_b cascade;
drop table tbla cascade;
drop table tblb cascade;
*/

CREATE TABLE tbla
(
  id int4 NOT NULL,
  a int4,
  b varchar(12),
  CONSTRAINT tbla_pk PRIMARY KEY (id)
)
--WITHOUT OIDS
;

CREATE TABLE tblb
(
  id int4 NOT NULL,
  x bool,
  y timestamp,
  CONSTRAINT tblb_pk PRIMARY KEY (id),
  CONSTRAINT tblb_fk FOREIGN KEY (id) REFERENCES tbla (id) ON UPDATE CASCADE
ON DELETE CASCADE
)
--WITHOUT OIDS
;

INSERT INTO tbla VALUES ( 3, 9034, 'test1' );
INSERT INTO tbla VALUES ( 6, -23, 'test2' );
INSERT INTO tblb VALUES ( 3, false, now() );
INSERT INTO tblb VALUES ( 6, true, now() );

CREATE OR REPLACE VIEW a_and_b AS
  SELECT tbla.id, tbla.a, tbla.b, tblb.x, tblb.y
    FROM tbla
NATURAL LEFT JOIN tblb;


CREATE OR REPLACE RULE a_b_insert AS


     ON INSERT TO a_and_b DO INSTEAD (
     INSERT INTO tbla (id, a, b) VALUES (new.id, new.a, new.b);
     INSERT INTO tblb (id, x, y) VALUES (new.id, new.x, new.y);
);

-- test your insert
INSERT INTO a_and_b VALUES (99, 123, 'text', false, now() );


CREATE OR REPLACE RULE a_and_b_del AS


     ON DELETE TO a_and_b DO INSTEAD
      DELETE FROM tbla WHERE tbla.id = OLD.id;

-- test your delete
DELETE FROM a_and_b WHERE id=99;

CREATE OR REPLACE RULE a_and_b_upd AS


     ON UPDATE TO a_and_b DO INSTEAD
     (
        UPDATE tbla SET a = new.a, b = new.b  WHERE tbla.id = new.id;
        UPDATE tblb SET x = new.x, y = new.y  WHERE tblb.id = new.id ;
     );

-- test your update
UPDATE a_and_b SET a=-333, b='neotext', x=false, y='2005-6-6' WHERE id=1;

... it works ok in pgadmin  ...

PS:
    but for me is a problem - I can't do update from delphi7 :
    Error is: "row cannot be located for updating"  ... this is because I  
do 2 updates in rule of     update view and the odbc driver (psqlodbc ) or  
delphi wants to do update based on every field     ... (also is no key in  
view!!!???)
    ... if anybody have a solution to this problem ....!?

Best Regards,
Adrian Din,
Om Computer & SoftWare
Bucuresti, Romania

-- 
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/