Thread: Query Question
I have created 3 tables, the parent table has a primary key(pmt_id) which is generated by a sequence object which is tied to this table. The 2nd table paid_policy is a child to the first table and has a primary key of pmt_id, (which is the FK to the 1st table), plcy_num and rqst_id. The third table which is a child to the 2nd table has a primary key of pmt_id, plcy_num, rqst_id, and pmt_tstmp. When inserting a row into the parent the sequence number is generated and returned by the statement below; however, I have not been able to figure out a way to capture this id to insert it in the 3rd table since it is part of the key. This application will have thousands if not hundreds of thousands of inserts daily, so I was concerned that current value could change. I thought about trying to capture the rowid from the statement below but was wondering if there is a better way to accomplish this. Any assistance would be greatly appreciated. I just joined the mailing list within the last hour so I am very new at this. Thanks in advance.
WITH testtbl AS (
INSERT INTO schema.pmt_wip
(pmt_id, clnt_id, agt_assoc_id, pmt_stts_cd, refr_num, user_id, pmt_rcpt_tstmp)
VALUES
(nextval('schema.pmt_wip_seq'), 'Client ID 5', 'Assoc Id 6', 1, 'reference number', 'PCPA', Now())
RETURNING pmt_id
) INSERT INTO schema.paid_plcy
(pmt_id, plcy_num, rqst_id, plcy_st_cd, co_code, addl_life_plcy_amt, prem_trf_amt)
VALUES
((SELECT pmt_id FROM testtbl), 'policy #2', 'rqst id2', 25, 4, 0, 0);
______________
Henry Ramsey Jr. A
Database Administrator
Database Administrator
State Farm Insurance Companies
(309) 287-1064
Henry Ramsey <henry.ramsey.pcpa@statefarm.com> writes: > I have created 3 tables, the parent table has a primary key(pmt_id) > which is generated by a sequence object which is tied to this table. > The 2nd table paid_policy is a child to the first table and has a > primary key of pmt_id, (which is the FK to the 1st table), plcy_num > and rqst_id. The third table which is a child to the 2nd table has a > primary key of pmt_id, plcy_num, rqst_id, and pmt_tstmp. FWIW, this use of "child" doesn't square with the way the term is typically used in Postgres. I think you just mean that the one table has an FK reference to the other. > When inserting a row into the parent the sequence number is generated > and returned by the statement below; however, I have not been able to > figure out a way to capture this id to insert it in the 3rd table > since it is part of the key. Since you're relying on WITH RETURNING anyway, can't you chain several such WITHs together? Something like this: WITH ins1 AS (INSERT INTO t1 VALUES(...) RETURNING ...), ins2 AS (INSERT INTO t2 SELECT ... FROM ins1 RETURNING ...) INSERT INTO t3 SELECT ... FROM ins2; A quick test says that this works unsurprisingly. You do need to have all the values from t1 that you need in t3 be also inserted into t2 so that you can return them up from ins2. (If you had to, you could probably join the results of ins1 and ins2 to overcome that, but I'd personally avoid a join if I could.) In any case, I'd advise using INSERT ... SELECT rather than the hack of sub-selects in a VALUES list. The sub-select method doesn't scale nicely when you need more than one value from the WITH rows. regards, tom lane
On Jun 15, 2012, at 7:08 PM, Henry Ramsey wrote: > I have created 3 tables, the parent table has a primary key(pmt_id) which is generated by a sequence object which is tiedto this table. The 2nd table paid_policy is a child to the first table and has a primary key of pmt_id, (which is theFK to the 1st table), plcy_num and rqst_id. The third table which is a child to the 2nd table has a primary key of pmt_id,plcy_num, rqst_id, and pmt_tstmp. When inserting a row into the parent the sequence number is generated and returnedby the statement below; however, I have not been able to figure out a way to capture this id to insert it in the3rd table since it is part of the key. This application will have thousands if not hundreds of thousands of inserts daily,so I was concerned that current value could change. I thought about trying to capture the rowid from the statementbelow but was wondering if there is a better way to accomplish this. Any assistance would be greatly appreciated. I just joined the mailing list within the last hour so I am very new at this. Thanks in advance. > > WITH testtbl AS ( > INSERT INTO schema.pmt_wip > (pmt_id, clnt_id, agt_assoc_id, pmt_stts_cd, refr_num, user_id, pmt_rcpt_tstmp) > VALUES > (nextval('schema.pmt_wip_seq'), 'Client ID 5', 'Assoc Id 6', 1, 'reference number', 'PCPA', Now()) > RETURNING pmt_id > ) INSERT INTO schema.paid_plcy > (pmt_id, plcy_num, rqst_id, plcy_st_cd, co_code, addl_life_plcy_amt, prem_trf_amt) > VALUES > ((SELECT pmt_id FROM testtbl), 'policy #2', 'rqst id2', 25, 4, 0, 0); Try something like: WITH testa as(INSERT INTO test1 VALUES(1) RETURNING id), testb as (INSERT INTO test2 (select id from testa) RETURNING id)INSERT INTO test3 (select id from testb); Thanks & Regards, Vibhor Kumar EnterpriseDB Corporation The Enterprise PostgreSQL Company Blog: http://vibhork.blogspot.com