-- -- content_revision__content_copy/2 -- create or replace function content_revision__content_copy( content_copy__revision_id integer, content_copy__revision_id_dest integer ) returns int4 as $$ DECLARE v_item_id cr_items.item_id%TYPE; v_content_length cr_revisions.content_length%TYPE; v_revision_id_dest cr_revisions.revision_id%TYPE; v_content cr_revisions.content%TYPE; v_lob cr_revisions.lob%TYPE; v_new_lob cr_revisions.lob%TYPE; v_storage_type cr_items.storage_type%TYPE; BEGIN if content_copy__revision_id is null then raise exception 'content_revision__content_copy attempt to copy a null revision_id'; end if; select content_length, item_id into v_content_length, v_item_id from cr_revisions where revision_id = content_copy__revision_id; -- get the destination revision if content_copy__revision_id_dest is null then select latest_revision into v_revision_id_dest from cr_items where item_id = v_item_id; else v_revision_id_dest := content_copy__revision_id_dest; end if; -- only copy the content if the source content is not null if v_content_length is not null and v_content_length > 0 then /* The internal LOB types - BLOB, CLOB, and NCLOB - use copy semantics, as opposed to the reference semantics which apply to BFILEs. When a BLOB, CLOB, or NCLOB is copied from one row to another row in the same table or in a different table, the actual LOB value is copied, not just the LOB locator. */ select r.content, r.content_length, r.lob, i.storage_type into v_content, v_content_length, v_lob, v_storage_type from cr_revisions r, cr_items i where r.item_id = i.item_id and r.revision_id = content_copy__revision_id; if v_storage_type = 'lob' then v_new_lob := empty_lob(); PERFORM lob_copy(v_lob, v_new_lob); update cr_revisions set content = null, content_length = v_content_length, lob = v_new_lob where revision_id = v_revision_id_dest; -- this call has to be before the above instruction, -- because lob references the v_new_lob -- PERFORM lob_copy(v_lob, v_new_lob); else -- this will work for both file and text types... well sort of. -- this really just creates a reference to the first file which is -- wrong since, the item_id, revision_id uniquely describes the -- location of the file in the content repository file system. -- after copy is called, the content attribute needs to be updated -- with the new relative file path: -- update cr_revisions -- set content = '[cr_create_content_file $item_id $revision_id [cr_fs_path]$old_rel_path]' -- where revision_id = :revision_id -- old_rel_path is the content attribute value of the content revision -- that is being copied. update cr_revisions set content = v_content, content_length = v_content_length, lob = null where revision_id = v_revision_id_dest; end if; end if; return 0; END; $$ language plpgsql;