Loop over SELECT and insert records postgres

Question

I have a mixture of some psuedo code which includes some PostgresSQL. 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. Any guidance/advice on how I'd go about approaching this would be great:

# Old CommissionExpenses do not have the cost_item_id set
# New CommissionExpenses have the cost_item_id and purchase_id set

# Find _new_ commissions
results = SELECT * FROM nok.commission_expenses ce WHERE ce.cost_item_id IS NOT NULL AND ce.purchase_id IS NOT NULL

# Loop through those and look up transactions

for result in results
  transactions = SELECT * FROM transactions t WHERE t.target_id::integer = result.purchase_id  

  for t in transactions
    INSERT INTO transactions
      nextval('transactions_id_seq'::regclass) as id,
      t.user_id,
      t.transaction_type,
      t.account,
      result.amount as amount,
      result.id as target_id,
      t.target_type,
      t.created_at,
      t.updated_at,
      t.log_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. 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() 

RETURNS VOID AS $$
DECLARE
  rec RECORD;
  txt RECORD;
BEGIN
  FOR rec IN EXECUTE 'SELECT * FROM nok.commission_expenses ce WHERE ce.cost_item_id IS NOT NULL AND ce.purchase_id IS NOT NULL'
    LOOP
      FOR tx IN EXECUTE 'SELECT * FROM transactions t WHERE t.target_id::integer = rec.purchase_id'
        LOOP
          INSERT INTO transactions
            nextval('transactions_id_seq'::regclass) as id,
            tx.user_id,
            tx.transaction_type,
            tx.account,
            rec.amount as amount,
            rec.id as target_id,
            tx.target_type,
            tx.created_at,
            tx.updated_at,
            tx.log_id
          ; 
        END LOOP; 
   END LOOP;
END;
$$ LANGUAGE plpgsql;

giorgiga · Accepted Answer · 2018-01-20 01:22:06Z

When you execute

FOR tx IN EXECUTE 'SELECT *
                     FROM transactions t
                    WHERE t.target_id::integer = rec.purchase_id'

rec.purchase_id should be a variable.

IIRC the syntax is:

FOR tx IN EXECUTE 'SELECT *
                     FROM transactions t
                    WHERE t.target_id = ?'
          USING rec. purchase_id

but... that is NOT the way to go :)

What you really want is to use insert ... select ... 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)
select nextval('transactions_id_seq'::regclass) as id,
       tx.user_id,
       tx.transaction_type,
       tx.account,
       rec.amount as amount,
       rec.id as target_id,
       tx.target_type,
       tx.created_at,
       tx.updated_at,
       tx.log_id
  from transactions tx
       join nok.commission_expenses ce
            on ce.purchase_id = tx.target_id
 where ce.cost_item_id is not

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

PS: it seems fishy that you are inserting a new line in transactions for existing rows in the same table... are you sure you don't want to update the existing rows instead?

Thaw · Accepted Answer · 2018-01-20 19:06:51Z

0

A RDMS is used to operate on sets. You don't need to do it yourself

INSERT INTO transactions (...)
SELECT t.a, t.b, ...
  FROM transactions t, nok.commission_expenses ce
 WHERE t.target_id::integer = ce.purchase_id
   AND ce.cost_item_id IS NOT NULL
   AND ce.purchase_id IS NOT NULL

edited Jan 20, 2018 at 19:06

answered Jan 20, 2018 at 1:14

Thaw

8619 silver badges19 bronze badges

3 Comments

Deej Over a year ago

FWIW - I had something like this. Apologies for the formatting here ``` SELECT nextval('transactions_id_seq'::regclass) as id, t.user_id, t.transaction_type, t.account, ce.amount as amount, ce.id as target_id, t.target_type, t.created_at, t.updated_at, t.log_id FROM nok.commission_expenses ce JOIN nok.transactions t ON t.target_id::integer = ce.id WHERE ce.cost_item_id IS NOT NULL AND ce.purchase_id IS NOT NULL```

Deej Over a year ago

This doesn't satisfy my needs. The query that I was using in the first comment

Thaw Over a year ago

@David you shouldn't be using loops like this at all unless you are using Oracle, you can do it with SQL, I'd recommend that you try to rewrite it

Collectives™ on Stack Overflow

Loop over SELECT and insert records postgres

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related