Rules with Conditions: Bug, or Misunderstanding - Mailing list pgsql-hackers

From Joel Burton
Subject Rules with Conditions: Bug, or Misunderstanding
Date
Msg-id 3A255246.21430.688927F3@localhost
Whole thread Raw
Responses Re: [SQL] Rules with Conditions: Bug, or Misunderstanding
List pgsql-hackers
Am I misunderstanding how to use rule w/conditionals, or is there a 
bug in this?

--

I love to use Pgsql comments, but find the 'comment on field...' 
language a bit of a pain for documenting a large database at the 
last minute. So, I wrote a query that pulls together all the fields in a 
database, w/descriptions (if any):

create view dev_col_comments as 
select a.oid as att_oid, relname, attname, description 
from pg_class c, pg_attribute a left outer join pg_description d on d.objoid=a.oid
where c.oid=a.attrelid
and (c.relkind='r' or c.relkind='v') and c.relname !~ '^pg_'
and attname not in ('xmax','xmin','cmax','cmin','ctid','oid','tableoid')
order by relname, attname;

[This uses pg7.1 syntax; you could rewrite for 7.0 w/o the 'v' for 
views, and using a union rather than outer join.]

This works great. Feeling clever, I wrote two rules, so I could 
update this and create comments. I need two rules, one if this is an 
existing description (becoming an update to pg_description), one if 
this not (becoming an insert to pg_description).

create rule dev_ins as on update to dev_col_comments where 
old.description isnull do instead insert into pg_description ( objoid, 
description) values (old.att_oid, new.description);

create rule dev_upd as on update to dev_col_comments where 
old.description notnull do instead update pg_description set 
description=new.description where objoid=old.att_oid;

This doesn't work: I get a "cannot update view w/o rule" error 
message, both for fields where description was null, and for fields 
where it wasn't null.

If I take out the "where old.description isnull" clause of dev_ins, it 
works fine--but, only, of course, if I am sure to only pick new 
descriptions. Or, if I take out the clause in dev_upd, it works too, 
with the opposite caveat.

Is this a bug? Am I misunderstanding something about the way that 
rule conditions should work? The docs are long but fuzzy on rules 
(they seem to suggest, for instance, that "create rule foo on 
update to table.column" will work, when this is not implemented yet, 
so perhaps the docs are ahead of the implementation?)

Any help would be great!

I do read the pgsql lists, but always appreciate a cc, so I don't miss 
any comments. TIA.

Thanks,

--
Joel Burton, Director of Information Systems -*- jburton@scw.org
Support Center of Washington (www.scw.org)


pgsql-hackers by date:

Previous
From: "Joel Burton"
Date:
Subject: Re: [GENERAL] Warning: Don't delete those /tmp/.PGSQL.* files
Next
From: Tom Lane
Date:
Subject: Re: [GENERAL] Warning: Don't delete those /tmp/.PGSQL.* files