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)