From: | Joe Abbate <jma(at)freedomcircle(dot)com> |
---|---|
To: | psycopg(at)postgresql(dot)org |
Subject: | Re: Question on Multi-Table Inserts |
Date: | 2013-04-16 11:02:26 |
Message-ID: | 516D2FC2.7020906@freedomcircle.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | psycopg |
Hello Don,
On 16/04/13 05:59, Don Parris wrote:
> Is it as simple as running a series of insert statements in succession,
> passing on the PK of the first table to the next table in the process?
> I.e.,
>
> BEGIN
> INSERT into table payee(values...)
> INSERT into table transrec(values...) # pass in payee_id variable
> SELECT *from category (to get cat_id)
> INSERT into table transdetails(values...) #pass in transrec_id and
> cat_id variables
> COMMIT
>
> Or is it more complicated than that?
From a business standpoint, and without any further implementation
details, I'd say the first INSERT and the SELECT aren't part of the
logical unit of work, so the database transaction would normally
parallel the financial transaction, i.e., just insert into transrec and
trandetails.
As you said, the payee insert depends on checking for existence, so it
would be conditional. Similarly, the cat_id has to exist and be found
first. If this was a typical interactive application, the SELECTs for
payee and category would be done first (before the BEGIN), and the
latter could be done just once with the results cached in the front-end
(shown to the user as a drop-down selection).
Regards,
Joe
From | Date | Subject | |
---|---|---|---|
Next Message | Federico Di Gregorio | 2013-04-16 12:54:29 | Re: Question on Multi-Table Inserts |
Previous Message | Don Parris | 2013-04-16 09:59:08 | Question on Multi-Table Inserts |