> > What I'm not sure about is the syntax for specifying such an update.
>
> Yup, that's the key part of the problem.
>
> > Would we just overload the syntax for an array?
>
> > e.g. UPDATE objects SET obj_data[32:47]='0123456789abcdef';
>
> This makes lots of sense for bytea, which ought to be considered an
> array type anyway, but I'm less certain about doing it that way for
> multibyte text objects. Seems like it could be pretty inefficient
> if the subscriptable entities are of varying length. Ideas anyone?
>
FWIW, here's the SQL SVR 7 spec:
-----------------------------------------
UPDATETEXT {table_name.dest_column_name dest_text_ptr}
{ NULL | insert_offset }
{ NULL | delete_length}
[WITH LOG]
[ inserted_data | {table_name.src_column_name src_text_ptr}]
Oracle 8 define DBMS_LOB.ERASE(ptr, length, offset) and DBMS_LOB.WRITE(ptr,
length, offset, data) functions, but I don't see an UPDATE function for
LOBs.
SQL99 says:
4.2.2.1
<character overlay function> is a function, OVERLAY, that modifies a string
argument by replacing a given substring of the string, which is specified by
a
given numeric starting position and a given numeric length, with another
string
(called the replacement string). When the length of the substring is zero,
nothing is removed from the original string and the string returned by the
function is the result of inserting the replacement string into the original
string at the starting position.
4.3.2.1
<blob overlay function> is a function identical in syntax and semantics to
<character overlay func-tion> except that the first argument, second
argument, and returned value are all binary strings.
So based on all of that, maybe:
UPDATE <table> SET <text_or_bytea_field> = OVERLAY(<text_or_bytea_field>,
'replacement_string', offset, length)
-- Joe
