Tom Lane wrote:
>
> Steve Wampler <swampler@noao.edu> writes:
> > Well, it looks like I got bit by this after all. Can someone show
> > me a rule to attach to a view ("attributes") to invoke a trigger function
> > (insert_or_update)?
>
> > The trigger function maps the update into either an insert or an update
> > on the underlying table ("attributes_table") depending on whether the row
> > already exists or not.
>
> > This was working under 7.0.3 (naturally, since insert/update/delete were
> > allowed on views without explicit rules).
>
> You had a trigger on update attached to a view? It should never have
> fired, unless you were allowing tuples to be inserted into the view's
> hidden table, which seems pretty wasteful.
Never knew views had hidden tables. See below for what I wanted (and
had working [apparently by chance!] in 7.0.x).
I'm (obviously) a novice sqler so perhaps there's a better way to
provide the functionality I want:
I have a table ("attributes_table" with (say) 2 columns: name and value) where I
want to allow an "insert_or_update" action. That is, if an insert comes along
where the name duplicates that of an existing row's name field, then I want to map
the insert into an update into attributes_table. (The table is acting as a
labelled set.)
I asked the newsgroup about this a year or so ago and
was told to create a view ("attributes") and put a trigger function on the view to
do this mapping. Doing so allowed updates, inserts and queries to operate through
the view (I deliberately disallowed deletes through the view) while still providing
the labelled set behavior.
> But if that's what you want to do, you could emulate this pre-7.1
> behavior by using a rule to redirect inserts/updates on the view to some
> dummy table that you put triggers on. The dummy table would take the
> place of the view's hidden table, which no longer exists in 7.1.
Ok, I'll try that unless someone can suggest a better approach to providing
a "labelled set".
I'd prefer the users (all Java programs, in this case) to not have to
know about two database objects - before, all actions were handled through the
single view and the users never knew "attributes_table" was the real
storage for the attributes.
Hmmm, is there any way to put the triggers directly on "attributes_table" instead
of a dummy table? That would be cleaner [from my perspective], though it looks
to me that would cause a recursive plunge in the trigger function (which does
an insert/update internally after figuring out which to use...).
Thanks!
--
Steve Wampler- SOLIS Project, National Solar Observatory
swampler@noao.edu