The update() function

The update() function allows the database server to handle in-place updates of opaque data type values, improving the performance for an opaque type that has an expensive constructor. For example, an opaque type that contains a smart large object might benefit from an update() function. If no update() function is present, the database server calls the assign() function, which creates an entirely new smart large object, and then calls the destroy() function to delete the old smart large object. If the update only changes a few bytes in a large object, this is clearly not efficient.

The update() function provides for in-place update of an opaque data type. Like the assign() and destroy() functions, the update() function is an SQL function defined on a given UDT. It takes two arguments, both of the same UDT type, and returns the same UDT type. The first argument is the original value of the user-defined type, and the second argument is the new UDT value. The function must handle NULL.

The following statement registers an update() function for the multirepresentational data type MyUDT:

CREATE FUNCTION Update (MyUDT, MyUDT)
   RETURNS MyUDT 
   WITH (HANDLESNULLS, NOT VARIANT)
   EXTERNAL NAME'/usr/lib/extend/blades/MyUDT.so(MyUDT_update)'
   LANGUAGE C;

The update() function must check for updates that cross the threshold for multirepresentational data. For example, if a large quantity of data is updated to a small quantity, the update() routine needs to decrement the smart large object reference count and return the updated value as an in-row object.