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
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 |