Thread: How to update dependent tables AND design considerations

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

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