Postgresql creating function that dynamically updates foreign key's value based on matching string value

Question

I'm looking for help with my postgresql trigger function that dynamically updates foreign key field based on matching conditions. I believe it's an easy function, but I can't seem to execute it successfully without exceeding stack depth limit (even though the function is triggered on a row rather than statement basis)

Example:

1st table staff with cols. id(PK),name,surname,reference_number(string) 2nd table orders with cols. id(PK),itemName,price,reference_number(string),sellerId(FK) for the same of this example reference numbers are identically matching(can't be used as FK as it's hard imported value)

My current function code:

CREATE OR REPLACE FUNCTION test() RETURNS TRIGGER AS $$ BEGIN UPDATE orders SET sellerId = (SELECT reference_number FROM staff WHERE staff.reference_number = NEW.sellerId); RETURN NEW; END; $$ LANGUAGE plpgsql;

Thank you for your help & time!

It is normal that it gets a stack overflow. You are continously setting selleId to itself. Instead of code maybe you should explain what you are trying to do with sample data. — Cetin Basoz
– Cetin Basoz, Commented Oct 3, 2022 at 0:49
Going by the same example I've provided, I probably wasn't as clear enough... The reason why I'm looking to link the orders, is so I could use their name from the staff table and filter all the orders that only they have sold, ignoring any other orders that aren't matching their reference numbers. In addition being able to use seller specific fields next to their matching orders. — Evaldas Sedys
– Evaldas Sedys, Commented Oct 3, 2022 at 1:21
Maybe there would be some genius who understand what you mean. — Cetin Basoz
– Cetin Basoz, Commented Oct 3, 2022 at 10:03

Laurenz Albe · Accepted Answer · 2022-10-03 06:08:11Z

0

Do not update the same row in an AFTER trigger. Rather, use a BEFORE trigger than modifies the new tow NEW before it hits the table.

answered Oct 3, 2022 at 6:08

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql creating function that dynamically updates foreign key's value based on matching string value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related