From: Joel Burton <jburton@scw.org>
To: pgsql-hackers@postgreSQL.org
To: pgsql-sql@postgreSQL.org
Subject: Rules with Conditions: Bug, or Misunderstanding
Date: Wed, 29 Nov 2000 19:00:22 -0500
Message-ID: <3A255246.21430.688927F3@localhost> (raw)
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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: jburton@scw.org, pgsql-hackers@postgreSQL.org
Subject: Re: Rules with Conditions: Bug, or Misunderstanding
In-Reply-To: <3A255246.21430.688927F3@localhost>
References: <3A255246.21430.688927F3@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox