Updatable view
От | M.D.G. Lange |
---|---|
Тема | Updatable view |
Дата | |
Msg-id | 42C3F8A1.5000905@dltmedia.nl обсуждение исходный текст |
Список | 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" JOINtblcontacts 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 по дате отправления: