Skip site navigation (1) Skip section navigation (2)

Updatable view

From: "M(dot)D(dot)G(dot) Lange" <mlange(at)dltmedia(dot)nl>
To: pgsql-sql(at)postgresql(dot)org
Subject: Updatable view
Date: 2005-06-30 13:50:25
Message-ID: 42C3F8A1.5000905@dltmedia.nl (view raw or flat)
Thread:
Lists: pgsql-sql
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

pgsql-sql by date

Next:From: KÖPFERL RobertDate: 2005-07-01 08:52:48
Subject: UNIT-tests and SQL
Previous:From: Nick StoneDate: 2005-06-30 12:22:39
Subject: Re: SQL Query question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group