Message-ID: <3EACF485.D2AA433E@rodos.fzk.de>
Date: Mon, 28 Apr 2003 11:29:41 +0200
From: Christoph Haller <ch@rodos.fzk.de>
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Cc: postgres@jal.org
Subject: Re: Rule definition problem
Content-Type: text/plain; charset=iso-8859-2
Content-Transfer-Encoding: 7bit

>
> I'm trying to define rules for inserts and updates on a view. My
problem
> is that I'm unsure on how to get the value of a field on one table and

> use it in an insert into another.
>
> The tables in question have a one-one relationship (A little warped, I

> know; the 'inventory' table has data common to all items, and the
> 'music' table has data specific to, well, inventory items of type
> 'music'.)
>
> The underlying tables and the view look something like this:
>
> create table inventory (
>         id serial unique not null,
>         manufacturer_num text unique,
>         category_id int not null,
>         [...] );
>
> create table music (
>         id serial unique not null,
>         inventory_id int not null,
>         year int,
>         label text,
>         [...]
>         CONSTRAINT category_exists foreign key (inventory_id)
>         references inventory (id) on delete cascade );
>
>
> create view music_view as
>         select inventory.id,
>         inventory.manufacturer_num,
>         inventory.category_id,
>         [...],
>         music.year,
>         music.label,
>         [...]
>         from inventory, music
>         where inventory.id = music.inventory_id;
>
>
> For an Insert rule, I need to get the value of inventory.id we just
> inserted and stash it in music.inventory_id.
>
> Is this possible in a rule, or do I need to do with in a trigger? Am I

> on crack?
>
If you did not mention you intend to insert/update a view via rules,
I'd say
create rule stash_id_in_music as on insert to inventory
 do insert into music values(new.id, ...);

I personnally would prefer to do it via a trigger, because the trigger
function
allows for more flexibility on the action. But still, did I get you
right at all?
Regards, Christoph