I have a function in PostgreSQL 9.1 that I want client applications to be passing a record object and the function will detect if it's an insert or an update and return the operation done, i.e "1 record successfully added" or " Record with PK [wherever] was successfully updated" or "An error occurred, field X cannot be null" or "any error that it gets".
I want to avoid an expensive
CURSOR and an even more expensive
EXCEPTION clause. I also want it to be optimized for speed. The function should consider concurrency because there will be more than 50 clients connected to the database at the same time, each updating/inserting (calling the same function). This is what I have currently:
CREATE OR REPLACE FUNCTION netcen.fun_test(myobj netcen.test) RETURNS text AS $BODY$ DECLARE myoutput text :='Nothing has occured'; BEGIN update netcen.test set tes=myobj.tes, testname=myobj.testname WHERE testkey=myobj.testkey; IF FOUND THEN myoutput:= 'Record with PK[' || myobj.testkey || '] successfully updated'; RETURN myoutput; END IF; BEGIN INSERT INTO netcen.test values(myobj.testkey, myobj.tes, myobj.testname); myoutput:= 'Record successfully added'; EXCEPTION WHEN null_value_not_allowed THEN RAISE NOTICE 'something has just occured'; myoutput:= 'A field was null'; WHEN not_null_violation THEN RAISE NOTICE 'something has just occured'; myoutput:= 'A field was null'; WHEN unique_violation THEN RAISE NOTICE 'something has just occured'; myoutput:= 'A field was null'; END; RETURN myoutput; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100;
I know this function can be simplified to a simple and shorter writable CTE function of SQL language. But those have a race condition. And I don't know how to capture errors and report the operation done as output from it.
Can this function be simplified or complicated but handle all my concerns?