CODEWITHSUNDEEP
postgresqlsupabasepostgrest
Evaldas Sedys
Evaldas Sedys

Reputation: 11

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

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!

Upvotes: 0

Views: 417

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246918

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.

Upvotes: 0

Related Questions