Multi-Table Insert/Update Strategy - Use Functions/Procedures?

Поиск
Список
Период
Сортировка
От Don Parris
Тема Multi-Table Insert/Update Strategy - Use Functions/Procedures?
Дата
Msg-id CAJ-7yomef0k0RN3ts=nBGfyY46GokFwaCZ9nxmg6RxxSkTb9mw@mail.gmail.com
обсуждение исходный текст
Ответы Re: Multi-Table Insert/Update Strategy - Use Functions/Procedures?
Re: Multi-Table Insert/Update Strategy - Use Functions/Procedures?
Список pgsql-general
Дерево обсуждения
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
GPG Key ID: F5E179BE

В списке pgsql-general по дате отправления: