i have an UPSERT function which works well but i update and insert records in batches, can this function be modified so that i just pass all the records to this function as an array then it will insert/update ALL the records at once as ONE TRANSACTION? if record number x fails,it should roll back undo any records that had been inserted/updated before x? here is my upsert function:
CREATE OR REPLACE FUNCTION save_weights(rec tbl_weightment) RETURNS text AS $BODY$ DECLARE myoutput text :='Nothing has occured'; BEGIN update tbl_weightment set vname=rec.vname, iquality=rec.iquality, vhooks=rec.vhooks, tstamp=rec.tstamp vtare=rec.vtare, vgross=rec.vgross WHERE id=rec.id; IF FOUND THEN myoutput:= 'Record successfully updated'; RETURN myoutput; END IF; BEGIN INSERT INTO tbl_weightment SELECT(rec).*; myoutput:= 'Record successfully added'; END; RETURN myoutput; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100;
can this function be modified to be like
save_weights(rec tbl_weightment) ??
any help will be highly appreciated