Insert ID from previous insert POSTGRES

Question

I am trying to daisy chain ids through 3 tables. I need to insert a line, take the inserted id and insert that into the next insert, which then takes that new id and inserts it into the final table. My current code looks like:

CREATE OR REPLACE PROCEDURE record_tenant_transaction(UUID,VARCHAR,NUMERIC,UUID,UUID)
LANGUAGE plpgsql
AS '
BEGIN
 INSERT INTO transaction_info(tenant_id)
 VALUES (1ドル);
 INSERT INTO entry(transaction_info_id)
 VALUES(NEW.id);
 INSERT INTO entry_detail(description,amount,credit,debit,entry_id)
 VALUES (2,ドル3,ドル4,ドル5,ドルNEW.id);
 COMMIT;
END;
'

but I've tried a bunch of other configs.

This code returns ERROR: missing FROM-clause entry for table "new".

I've tried using the RETURNING clause but then I get ERROR: query has no destination for result data. It seems to me that if I use RETURNING then the sequence stop there? Something like that.

I've also tried using the WITH clause but I REALLY don't understand how that works yet. I can't figure out from the documentation and I'm having trouble understanding a lot of the questions/answers about the WITH clause.

Everything I can find seems to be aimed at gathering SERIALS but my ids are UUID so I can't use the lastval() type clauses I believe.

I'm very new at this so I apologize if this has already been answered and I just don't understand it but I've been searching and trying to figure this out for 2 days now and I'm really hitting a brick wall. I only have a couple of hours each night to learn and code so it would mean a lot if someone could point me in the right direction.

Answer 1

You can use a data modifying CTE to chain the inserts and pass the generated ID on to the next statement:

CREATE OR REPLACE PROCEDURE record_tenant_transaction(UUID,VARCHAR,NUMERIC,UUID,UUID)
LANGUAGE plpgsql
AS $$
BEGIN
 with new_info as (
 INSERT INTO transaction_info(tenant_id)
 VALUES (1ドル)
 returning id --<< adjust here for the correct column name
 ), new_entry as (
 INSERT INTO entry(transaction_info_id)
 select id --<< adjust here for the correct column name
 from new_info
 returning id --<< adjust here for the correct column name
 )
 INSERT INTO entry_detail(description,amount,credit,debit,entry_id)
 VALUES (2,ドル3,ドル4,ドル5,ドル (select id from new_entry) );
 COMMIT;
END;
$$

You have to adjust the column name in the line I marked, as I don't know what the generated column in the table transaction_info is.

---

A more procedural way (which I wouldn't recommend) is to store the generated IDs in variables:

CREATE OR REPLACE PROCEDURE record_tenant_transaction(UUID,VARCHAR,NUMERIC,UUID,UUID)
LANGUAGE plpgsql
AS $$
DECLARE
 l_info_id uuid;
 l_entry_id uuid;
BEGIN
 INSERT INTO transaction_info(tenant_id)
 VALUES (1ドル)
 returning id --<< adjust here for the correct column name
 into l_info_id;
 INSERT INTO entry(transaction_info_id)
 values (l_info_id)
 returning id --<< adjust here for the correct column name
 into l_entry_id;
 INSERT INTO entry_detail(description,amount,credit,debit,entry_id)
 VALUES (2,ドル3,ドル4,ドル5,ドル l_entry_id);
 COMMIT;
END;
$$

---

You should also give your parameters proper names, rather then referencing them through 1,ドル 2ドル and so on.

• Theo: Thank you so much for taking the time! This worked like a charm. I thought it might have been the WITH clause. What we seek is always where we least want to look for it...
• Theo: I will play around with the parameters, I thought they had to be referenced as such. Thank you again!