From: M.D.G. Lange <mlange@dltmedia.nl>
To: pgsql-sql@postgresql.org
Subject: Updatable view
Date: Thu, 30 Jun 2005 15:50:25 +0200
Message-ID: <42C3F8A1.5000905@dltmedia.nl> (raw)
I have been looking around to get an idea how to make RULES on a VIEW
for INSERT , UPDATE and DELETE of a VIEW with an INNER JOIN.
First the idea:
There are 3 tables, those are related one way or another, combined in
this view:
pg_user (ok, it's a view really)
tblcontacts (with contact information like first name and lastname and such)
tbldepartments (a user works at a certain department)
tblusersettings (some extra information about the user which is not in
pg_shadow, eg locked status, birthdate and such)
tblusersettings has the links to tbldepartments, tblcontacts and pg_user
(via a trigger to check if the user exists)
---
CREATE OR REPLACE VIEW usersview AS
SELECT tblusersettings.userid, tblusersettings.birthdate,
tblusersettings.islocked, pg_user.usename, pg_user.usesuper,
pg_user.valuntil, tbldepartments.departmentname, tbldepartments."ID" AS
departmentid, tbldepartments.dateformatid, tbldepartments.currencyid,
tblcontacts."ID" AS contactid, tblcontacts.firstname,
tblcontacts.lastname, tblcontacts.gender, tblcontacts."function",
tblcontacts.phone, tblcontacts.email, tblcontacts.languageid
FROM tblusersettings
JOIN pg_user ON tblusersettings.userid = pg_user.usesysid
JOIN tbldepartments ON tblusersettings.departmentid = tbldepartments."ID"
JOIN tblcontacts ON tblusersettings.contactid = tblcontacts."ID"
ORDER BY pg_user.usename;
the view is OK, so I want something like this to INSERT a user:
CREATE OR REPLACE RULE insertuser AS
ON INSERT TO usersview
DO INSTEAD
(
(CREATE USER NEW.usename);
(INSERT INTO tblcontacts(firstname, lastname, gender, titleid,
function, phone, email, languageid, attentionid) VALUES (NEW.firstname,
NEW.lastname, NEW.gender, 0, NEW.function, NEW.phone, NEW.email,
NEW.mobile, NEW.languageid, 0));
);
The idea is:
- Create a user
- Create a contact
- Create usersettings for the user with in it the newly created
contactid and userid.
How impossible is this? How do I get the newly created contact and user
id's in the usersettings table?
TIA,
Michiel
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: mlange@dltmedia.nl
Subject: Re: Updatable view
In-Reply-To: <42C3F8A1.5000905@dltmedia.nl>
* 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