From: Christoph Haller <ch@rodos.fzk.de>
To: pgsql-sql@postgresql.org
Cc: postgres@jal.org
Subject: Re: Rule definition problem
Date: Mon, 28 Apr 2003 11:29:41 +0200
Message-ID: <3EACF485.D2AA433E@rodos.fzk.de> (raw)
>
> 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
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ch@rodos.fzk.de, postgres@jal.org
Subject: Re: Rule definition problem
In-Reply-To: <3EACF485.D2AA433E@rodos.fzk.de>
* 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