Postgresql – Pass a table array as a parameter to an upsert function in postgresql

arrayfunctionspostgresqlpostgresql-9.1upsert

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

Best 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;

Related Solutions

Postgresql – Pass an array or record to a function in PostgreSQL

Postgres has very flexible handling of arrays and composite types. This may be the sort of thing you are trying to do:

create type my_type as (val1 integer, val2 integer);

create function my_function(arr my_type[]) returns text language plpgsql as $$
begin
  return arr::text;
end;$$;

select my_function(array[row(1,2),row(3,4)]::my_type[]);

| my_function       |
| :---------------- |
| {"(1,2)","(3,4)"} |

dbfiddle here

Postgresql – Interactive INSERT / UPDATE function to implement UPSERT

If you want to catch NULL violations as well you have to cover the UPDATE as well, which makes the function a little more expensive.

SET search_path = netcen;

CREATE OR REPLACE FUNCTION fun_test(myobj test, OUT myoutput text)
  RETURNS text AS
$func$
BEGIN
   UPDATE test
   SET    tes = myobj.tes,
          testname = myobj.testname
   WHERE  testkey = myobj.testkey;

   IF FOUND THEN
      myoutput := 'UPDATE successfull. testkey = ' || myobj.testkey;
      RETURN;
   END IF;

   INSERT INTO test
   SELECT (myobj).*;
   myoutput := 'INSERT successfull. testkey = ' || myobj.testkey;

EXCEPTION
WHEN null_value_not_allowed THEN
   RAISE NOTICE 'null_value_not_allowed occurred.';
   myoutput := 'A field was null.';

WHEN not_null_violation THEN
   RAISE NOTICE 'not_null_violation occurred.';
   myoutput:= 'A field was null.';

WHEN unique_violation THEN
   RAISE NOTICE 'unique_violation occurred.';
   myoutput:= 'Duplicate value.';
END
$func$ LANGUAGE plpgsql;

Using an OUT parameter and made some simplifications and clarifications.

I would just let the regular EXCEPTION happen. People are not supposed to enter NULL values for NOT NULL columns. This way, the UPDATE can run outside the EXCEPTION block.

Best Answer

Related Solutions

Postgresql – Pass an array or record to a function in PostgreSQL

Postgresql – Interactive INSERT / UPDATE function to implement UPSERT

Related Question