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

From: Don Parris <parrisdc(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Multi-Table Insert/Update Strategy - Use Functions/Procedures?
Date: 2016-01-27 19:48:16
Message-ID: CAJ-7yomef0k0RN3ts=nBGfyY46GokFwaCZ9nxmg6RxxSkTb9mw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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
http://dcparris.net/ <https://www.xing.com/profile/Don_Parris>
<http://www.linkedin.com/in/dcparris>
GPG Key ID: F5E179BE

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bret Stern 2016-01-27 20:28:51 Re: Multi-Table Insert/Update Strategy - Use Functions/Procedures?
Previous Message Matt 2016-01-27 19:41:28 Re: Performance options for CPU bound multi-SUM query