Re: rules, triggers and views - Mailing list pgsql-hackers

On 2004-12-05, elein <elein@varlena.com> wrote:
> We're working on DBI-link which is an access method of sorts
> using existing tools.
>
> We want an empty local table foo with
>     1. a _RETURN RULE
>     2. an update TRIGGER
>     3. a delete TRIGGER
>     4. an insert TRIGGER

Use two tables. One has rules, the other has the trigger. (Neither store
any data.) Here's a working example (tested on 7.4.5):

-- declare the table which we're going to be manipulating. This never
-- actually stores anything (it becomes a view).
create table realtable (id integer, value text);

-- This is where the data comes from (hardcoded for example purposes)
create or replace function datasource() returns setof realtable as ' declare   v realtable; begin   v.id:=1;
v.value:=''foo'';return next v;   v.id:=2; v.value:=''bar''; return next v;   v.id:=3; v.value:=''baz''; return next v;
 raise notice ''datasource() returned 3 rows'';   return; end;
 
' language plpgsql;

-- This is where the updates etc. go to. It must return NULL, because we
-- want to suppress the actual row insertion.
create or replace function datasink() returns trigger as ' begin   raise notice ''datasink(): % % % % %'',
 NEW.opcode,NEW.o_id,NEW.o_value,NEW.n_id,NEW.n_value;   return NULL; end;
 
' language plpgsql;

-- This is a dummy table that we use for attaching the trigger to.
-- It's a real table, but it never contains data. Note that it stores
-- two copies of the data from "realtable" plus an opcode.
create table shadowtable (opcode "char",                         o_id integer, o_value text,
n_idinteger, n_value text);
 

-- attach the trigger
create trigger shadow_trigger before insert on shadowtable for each row execute procedure datasink();

-- Rules. We convert all modifications on realtable into inserts on
-- shadowtable.
create rule "_RETURN" as  on select to realtable  do instead select * from datasource();
create rule ins_rule as  on insert to realtable  do instead insert into shadowtable values ('i',NEW.*);
create rule upd_rule as  on update to realtable  do instead insert into shadowtable values ('u',OLD.*,NEW.*);
create rule del_rule as  on delete to realtable  do instead insert into shadowtable values ('d',OLD.*);

Now we see it in action:

test=> update realtable set value = 'quux' where id=3;
NOTICE:  datasource() returned 3 rows
NOTICE:  datasink(): u 3 baz 3 quux
UPDATE 0
test=> delete from realtable where value='bar';
NOTICE:  datasource() returned 3 rows
NOTICE:  datasink(): d 2 bar <NULL> <NULL>
DELETE 0
test=> delete from realtable;                  
NOTICE:  datasource() returned 3 rows
NOTICE:  datasink(): d 1 foo <NULL> <NULL>
NOTICE:  datasink(): d 2 bar <NULL> <NULL>
NOTICE:  datasink(): d 3 baz <NULL> <NULL>
DELETE 0
test=> insert into realtable values (4,'zoom');
NOTICE:  datasink(): i 4 zoom <NULL> <NULL>
INSERT 0 0

(Unfortunately, all the update operations return 0 rows affected. Not sure
there's any fix for that.)

-- 
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services