Re: rules, triggers and views

On 2004-12-05, elein <elein(at)varlena(dot)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_id integer, 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