Re: How to update dependent tables AND design considerations - Mailing list pgsql-sql

Have you looked at the documentation on triggers?

-tfo

--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005

On Jan 23, 2005, at 11:23 AM, Klaus W. wrote:

> Hi!
>
> Because i got no answer in pgsql.novice, i'll try it here. But maybe it
> was even too easy for .novice?
>
> Lets say there are three tables:
>
> CREATE TABLE c (id SERIAL, data VARCHAR(20));
> CREATE TABLE b (id SERIAL, c_id INTEGER REFERENCES c, data 
> VARCHAR(20));
> CREATE TABLE a (id SERIAL, b_id INTEGER REFERENCES b, data 
> VARCHAR(20));
>
> Now i have to insert some data into this table structure.
> In my old mysql days i would have inserted into c, look after
> the id, insert it into b, look after the id, insert into a.......
>
> Of course this could be done here too, but i think it's the worst
> case.
>
> Another idea was to create a VIEW which is updatable and
> insertable by RULES. But this solution simply shifts the Problem
> to the rule definition.
> Next idea was using a pl/pgsql function. But still the same
> problem: how to do?
>
> Idea:
> INSERT INTO TABLE c VALUES (DEFAULT, 'asdfasdfasfd');
> INSERT INTO TABLE b VALUES (DEFAULT, currval('c_id_seq'), 
> 'asdfasfasf');
> ........
>
> Good Idea? Is this the normal way?
>
> But what about this case:
> There is the following table:
>
> CREATE TABLE old (data_a VARCHAR(20), data_b VARCHAR(20), data_c 
> VARCHAR(20);
>
> containig Data that should be incorporated to the above mentioned
> table structure. With my solution i have to read the data
> with an application and split it into subsequent INSERT
> statements. This could not be a good Idea.
>
> Of course i could define the already mentioned VIEW, write
> some rules for updating and inserting and INSERT the data
> from old table into the VIEW. But is this the
> usual way?
>
> Isn't there something available like an INSERT to multiple
> tables?
>
> With real updates this should be easier, because the
> datasets are already existing and can be joined
> within FROM of the UPDATE Statement. But what about
> this case:
> I get a dataset: ('data a', 'data b', 'data c'). But
> the corresponding subset in table a and b already exists.
> Do i have to check in my application wheather the
> Dataset in table c exists or not and do an seperate
> INSERT myself? What about if the Data is coming from
> another table and not from an application?
> Should i make my UPDATE rule of a possible VIEW
> doing this JOB?
>
> Unfortunately the available tutorials cover only quite
> simple cases. But my projekt may have up to five or more
> dependency layers. It would be nice to have a
> readable, manageable and extensible solution.
> But i'm afraid my ideas so far are not.
>
> Thank you in advance!
> Klaus
>
>
> ---------------------------(end of 
> broadcast)---------------------------
> TIP 8: explain analyze is your friend