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
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.