Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

1 Answer

I think yes. Haven't tried with actual INSERTs or UPDATEs yet, but this function works:

CREATE TABLE a (id integer);

CREATE OR REPLACE FUNCTION arraytest2(rec a[]) RETURNS SETOF integer AS
$body$
SELECT b.*
FROM unnest($1) b;
$body$
LANGUAGE sql;

This way you can write your INSERT statement as

INSERT INTO tbl_weightment 
SELECT (r).*
FROM unnest(rec) r;

And UPDATE as

UPDATE tbl_weightment 
SET 
    vname = r.vname,
    iquality = r.iquality,
    vhooks = r.vhooks,
    tstamp = r.tstamp
    vtare = r.vtare,
    vgross = r.vgross 
FROM unnest(rec) r
WHERE id=rec.id;
share|improve this answer
    
this answer works but how about if one record in the array is faulty? how do we rollback everything and return "record no 79 missing something "?? –  indago Apr 16 '13 at 13:31
    
@indago actually what you'd like to know is how to add exception handling to the function? –  Marian Apr 16 '13 at 14:29
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.