From: | Bret Stern <bret_stern(at)machinemanagement(dot)com> |
---|---|
To: | Don Parris <parrisdc(at)gmail(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Multi-Table Insert/Update Strategy - Use Functions/Procedures? |
Date: | 2016-01-27 20:28:51 |
Message-ID: | 1453926531.3100.6.camel@centos65.machinemanagement.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2016-01-27 20:32:34 | Re: Multi-Table Insert/Update Strategy - Use Functions/Procedures? |
Previous Message | Don Parris | 2016-01-27 19:48:16 | Multi-Table Insert/Update Strategy - Use Functions/Procedures? |