pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRule definition problem
3+ messages / 3 participants
[nested] [flat]
* Rule definition problem
@ 2003-04-24 19:05 Jamie Lawrence <jal@jal.org>
0 siblings, 0 replies; 3+ messages in thread
From: Jamie Lawrence @ 2003-04-24 19:05 UTC (permalink / raw)
To: pgsql-sql
Hi all -
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?
TIA.
-j
--
Jamie Lawrence jal@jal.org
"Perl is Internet Yiddish."
- Yoz Graehme
^ permalink raw reply [nested|flat] 3+ messages in thread
* Rule definition problem
@ 2003-04-24 19:11 Jamie Lawrence <postgres@jal.org>
0 siblings, 0 replies; 3+ messages in thread
From: Jamie Lawrence @ 2003-04-24 19:11 UTC (permalink / raw)
To: pgsql-sql
Hi all -
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?
TIA.
-j
--
Jamie Lawrence jal@jal.org
Non-linear physics is like non-elephant biology.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Rule definition problem
@ 2003-04-28 09:29 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 3+ messages in thread
From: Christoph Haller @ 2003-04-28 09:29 UTC (permalink / raw)
To: pgsql-sql; +Cc: postgres@jal.org
>
> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-04-28 09:29 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-04-24 19:05 Rule definition problem Jamie Lawrence <jal@jal.org>
2003-04-24 19:11 Rule definition problem Jamie Lawrence <postgres@jal.org>
2003-04-28 09:29 Re: Rule definition problem Christoph Haller <ch@rodos.fzk.de>
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