From: | Din Adrian <adrian(dot)din(at)easynet(dot)ro> |
---|---|
To: | "Jaime Casanova" <systemguards(at)gmail(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: UPDATEABLE VIEWS ... Examples? |
Date: | 2005-06-20 20:49:36 |
Message-ID: | opssor4yu4awcxfg@adi |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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/
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Fuhr | 2005-06-20 23:36:44 | Re: Array in plpgsql with composite type |
Previous Message | grupos | 2005-06-20 16:31:20 | Array in plpgsql with composite type |