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
