rule to update view that inserts into log

From: "Chad Showalter" <cshowalter(at)bplglobal(dot)net>
To: <pgsql-general(at)postgresql(dot)org>
Subject: rule to update view that inserts into log
Date: 2008-04-12 12:09:44
Message-ID: 000001c89c96$18c0b8d0$4a422a70$@net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I would like to create a rule that, by updating a view, allows me to update
one table and insert into another.

The following example illustrates what I'm trying to do:

--Create Tables

CREATE TABLE my_table

(

my_table_id serial,

a character varying(255),

b character varying(255),

CONSTRAINT my_table_id_pk PRIMARY KEY (my_table_id)

);

CREATE TABLE my_audit_table

(

audit_id serial,

my_table_id int,

c character varying(255),

CONSTRAINT audit_id_pk PRIMARY KEY (audit_id)

);

--Create View

CREATE OR REPLACE VIEW my_view AS

SELECT

t.my_table_id,

t.a,

t.b,

au.audit_id,

au.c

FROM

my_table t, my_audit_table au

WHERE

t.my_table_id = au.my_table_id;

--Create Rules

CREATE OR REPLACE RULE insert_to_my_view AS

ON INSERT TO my_view

DO INSTEAD(

INSERT INTO my_table (a,b)

VALUES(new.a, new.b);

INSERT INTO my_audit_table(my_table_id, c)

VALUES

(currval('my_table_my_table_id_seq'), new.c);

);

CREATE OR REPLACE RULE update_my_view AS

ON UPDATE TO my_view DO INSTEAD

( UPDATE my_table SET

a = new.a,

b = new.b

WHERE

my_table_id = old.my_table_id;

INSERT INTO my_audit_table

(my_table_id,

c)

VALUES

(new.my_table_id,

new.c);

);

--The insert statement below inserts one row into my_table, and one row into
my_audit_table

--(This works the way I would like)

insert into my_view(a,b,c) values('a contents','b contents', 'c contents');

--The update statement below doesn't work the way I want.

--What I would like this to do is to update one row in my_table, and insert

--one row into my_audit table. It does the update fine, but the insert to
my_audit_table

--doesn't work as I had anticipated.

update my_view set a = 'new a contents', b = 'new b contents', c = 'new c
contents' where my_table_id = 1;

-- If I execute the above update statement multiple times, multiple rows
will be

--inserted with each call after the first call.

--

--Specifically,

--after the first call, 1 row is inserted

--after the second call, 2 rows are inserted

--after the third call, 4 rows are inserted

--after the fourth call, 8 rows are inserted... and so on

--

--The problem is due to the INSERT in the update_my_view rule:

--

--INSERT INTO my_audit_table

-- (my_table_id,

-- c)

--VALUES

-- (new.my_table_id,

-- new.c);

--

--Apparently, "new.my_table_id" in this case references more than one row,
if more than one row with

--the given id already exists in my_audit_table.

--

--How do I accomplish what I want to accomplish here? I'd prefer not to use
a sp.

Thanks,

Chad

Browse pgsql-general by date

  From Date Subject
Next Message william wayne 2008-04-12 15:05:41 Post-installation questions
Previous Message Alban Hertroys 2008-04-12 10:28:56 Re: Problem. createdb: could not connect to database postgres: could not connect to server: No such file or directory