Postgresql – Insert ID from previous insert POSTGRES

insertpostgresql

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.

Best Answer

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.

Related Question