From: Jamie Lawrence <postgres@jal.org>
To: pgsql-sql@postgresql.org
Subject: More plsql questions: updates on views
Date: Wed, 23 Jul 2003 18:31:05 -0500
Message-ID: <20030723233105.GO1073@jal.clueinc.net> (raw)
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@jal.org
The strength of our liberty depends upon the chaos and
cacophony of the unfettered speech the First Amendment
protects.
- Judge Stewart Dalzell
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: postgres@jal.org
Subject: Re: More plsql questions: updates on views
In-Reply-To: <20030723233105.GO1073@jal.clueinc.net>
* 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