From: | Jamie Lawrence <postgres(at)jal(dot)org> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | More plsql questions: updates on views |
Date: | 2003-07-23 23:31:05 |
Message-ID: | 20030723233105.GO1073@jal.clueinc.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hi all -
I'm trying to work through using views in order to access multiple
tables while allowing normal operations on them.
To keep things simple, this is a stripped down version of what the
structure for one of the views is like:
create table base (
id serial primary key,
owner int ,
attribute text );
create table specific_1 (
id serial primary key,
base_id int references base,
otherattr text );
create or replace view my_view as (
select base.id, base.owner, base.attribute,
specific_1.other
from base as b, specific_1 as s1
where (b.id = s1.base_id) );
create or replace rule my_view_insert_rule as
on insert to my_view do instead (
insert into base (owner, attribute) values
(new.owner, new.attr);
insert into specific_1 (base_id, otherattribute) values
( ( select currval('base_id_seq') ), new.otherattr ); );
create or replace rule my_view_delete_rule as
on delete to my_view do instead (
delete from base where id = old.id;
delete from base specific_1 where base_id = old.id; );
(BTW, I know there's general concensus that the use of currval in that
insert rule is a bad idea, and I understand why; This application uses
libraries that will not cause grief there.)
Now I need to be able to update it. My initial thought was to create a
function to handle this tripped by an AFTER trigger. I've been reading
up on dynamic execution from functions, but can't see how to access the
SET clause or the WHERE clause. Is this possible?
Is there any other way to get this effect?
Thanks in advance,
-j
--
Jamie Lawrence jal(at)jal(dot)org
The strength of our liberty depends upon the chaos and
cacophony of the unfettered speech the First Amendment
protects.
- Judge Stewart Dalzell
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Treat | 2003-07-24 02:00:03 | Re: TODO item for plpgsql Was Re: obtuse plpgsql function needs |
Previous Message | Bruce Momjian | 2003-07-23 23:06:06 | Re: TODO item for plpgsql Was Re: obtuse plpgsql function needs |