![]() PS: it seems fishy that you are inserting a new line in transactions for existing rows in the same table. It will be way quicker (as the db executes one query compared to one plus one per row in t_transactions) and incomparably easier to test/debug (just comment out the insert line and you'll be able to see exactly if what the query is going to insert adds up with what you expect). Select nextval('transactions_id_seq'::regclass) as id, and replace your whole function with a single statement like ( disclaimer: did not test this): insert into transactions(id, user_id, transaction_type, account, amount, target_id, target_type, created_at, updated_at, log_id) IIRC the syntax is: FOR tx IN EXECUTE 'SELECT * WHERE t.target_id::integer = rec.purchase_id' When you execute FOR tx IN EXECUTE 'SELECT * I'm aware that Postgres does support FOR loops and did also attempt to do this myself below as well: CREATE OR REPLACE FUNCTION loop_and_create()įOR rec IN EXECUTE 'SELECT * FROM nok.commission_expenses ce WHERE ce.cost_item_id IS NOT NULL AND ce.purchase_id IS NOT NULL'įOR tx IN EXECUTE 'SELECT * FROM transactions t WHERE t.target_id::integer = rec.purchase_id' Syntactically I know this is wrong, but I just thought to highlight the above to express what I'm trying to achieve at a high level. Nextval('transactions_id_seq'::regclass) as id, Transactions = SELECT * FROM transactions t WHERE t.target_id::integer = result.purchase_id Introduction to the PL/pgSQL Exit statement The exit statement allows you to terminate a loop including an unconditional loop, a while loop, and a for loop. # Loop through those and look up transactions Results = SELECT * FROM nok.commission_expenses ce WHERE ce.cost_item_id IS NOT NULL AND ce.purchase_id IS NOT NULL # New CommissionExpenses have the cost_item_id and purchase_id set Any guidance/advice on how I'd go about approaching this would be great: # Old CommissionExpenses do not have the cost_item_id set I'd like to do a SELECT and based of this result set I'd like to loop through these results and do a nested loop inside this result set and from that do an INSERT. However, you would have to reorganize the RETURN NEXT statement in this case. ![]() or b) add a text column (instead of vanothervar) to the composite type mytype. postgresql jsonb loop arrays example 2 This file. I have a mixture of some psuedo code which includes some PostgresSQL. a) split up the composite type vdata into scalar variables and list them in order, then append vanothervar to the FOR target list. The desired result from the query would be something like SELECT membershipid, clears FROM table.
0 Comments
Leave a Reply. |