0

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.

| improve this question | | | | |
4

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.

| improve this answer | | | | |
  • 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 Nov 7 '19 at 7:13
  • I will play around with the parameters, I thought they had to be referenced as such. Thank you again! – Theo Nov 7 '19 at 7:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.