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