On this track (possibly unrelated)...can a view be used as part of the multi table update Just curious
On Wed, 2016-01-27 at 14:48 -0500, Don Parris wrote: > I have several tables related to people and their contact information, > and want db users to be able to add or update a given person and their > respective contact information in one step, and get all the > information into the correct tables. > > > > I think I am ok with setting the privileges on the tables and columns > as appropriate to allow each group to select, insert and update the > appropriate data, and I can create appropriate views for them to view > data they need to see. However, I am not really sure about the best > way to allow someone to actually insert/update the data. > > > > For instance, given the following tables: > > > core.category > contact.entity > > > contact.person > > contact.entity_category --linking table between entity and category > > > contact.person_entity --linking table between entity & person > > ... --other tables for address and phone contact info > > > > I haven't really given much thought as to how such a procedure might > look, but I'm guessing something along the lines of: > CREATE FUNCTION record_insert() RETURNS integer AS $$ > > BEGIN > INSERT statements... --need PK from entity & category tables to insert > into entity_category table. > > END > > $$ > > language plpgsql; > > > Ideally, the db user just says "I want to enter Joe Public, and Joe is > affiliated with the Widget Corp entity, and has the phone numbers..." > > > > Am I on the right track, or is there some better way to set this up? > My understanding is that views really aren't meant for insert/update > operations, and I have seen on the web that using views to > insert/update is a bit tricky - and still requires a procedure with a > rule on the view. > > > > Thanks, > > Don > > -- > > D.C. Parris, FMP, Linux+, ESL Certificate > Minister, Security/FM Coordinator, Free Software Advocate > > http://dcparris.net/ > GPG Key ID: F5E179BE
