Re: Copying Blobs between two tables using Insert stmt - [SOLUTION]

Hi all,

Here is the solution I ended up in using to solve the problem.

a. Created a copy_blob function.

CREATE OR REPLACE FUNCTION copy_blob(p_blobId OID)
RETURNS OID AS $$
DECLARE
v_NewOID OID;
BEGIN
SELECT lo_create(-1) INTO v_NewOID;

delete from pg_largeobject where loid = v_NewOID;

INSERT INTO pg_largeobject (loid, pageno, data)
SELECT v_NewOID, pageno, data
FROM pg_largeobject
WHERE loid = p_blobId;

RETURN v_NewOID;
END;
$$ LANGUAGE plpgsql;

2. selected the OID from table_a;

3. select copy_blob(OID from table_a);

4. insert into table_b
(
id,
filename)
values
(
nextval('table_b_id_seq'),
v_NewOID
);

Works well so far.

John

On Fri, Oct 17, 2008 at 1:37 PM, John Skillings <jskillings07(at)gmail(dot)com>wrote:

> Hi Diogo,
>
> Thanks for your response. However, my requirement is to have two separate
> copies. Think about table_a being the original copy, and table_b holding
> the backup copy.
>
> Inserting the OID from table_a to table_b will not make the backup copy.
>
> Best Regards,
>
> John
>
>
>
> On Fri, Oct 17, 2008 at 12:32 PM, Diogo Biazus <diogob(at)gmail(dot)com> wrote:
>
>>
>> Em 17/10/2008, às 18:18, John Skillings escreveu:
>>
>>
>> Hi all,
>>>
>>> I am trying to copy blobs between two tables and need help on the best
>>> way to get this done. My requirement is that the both the tables maintain
>>> their own copy of the large object, instead of sharing the OID.
>>>
>>> I created two tables:
>>>
>>> create table table_a
>>> (id bigserial not null,
>>> filename oid);
>>>
>>> create table table_b
>>> (id bigserial not null,
>>> filename oid);
>>>
>>> In one of the tables, I uploaded a file from the filesystem.
>>>
>>> INSERT INTO table_a (id, filename)
>>> VALUES ( nextval('table_a_id_seq'),
>>> lo_import('C:/applications/largeobj.zip'));
>>>
>>> The record is inserted, and I verified the record's integrity by:
>>>
>>> SELECT lo_export(filename, 'C:/applications/largeobj.zip_copy.zip') FROM
>>> table_a;
>>>
>>> Question
>>> ----------------
>>> Now to make a copy of the object from table to table_a to table_a.
>>> Currently I am exporting the file from table_a to the file system, and again
>>> doing an import into table_b. However, in a large application, I find this
>>> workaround not practical because of the volume of the records, and also the
>>> size of the file (binary object). My ideal solution to do an insert of the
>>> values from table_a into table_b directly.
>>>
>>> So, what is best way to create a copy of this LOB from table_a to
>>> table_b?
>>>
>>
>> You can copy only the oid, You don't need to have another copy of the same
>> file in the database, if you copy only the oid you'll have another reference
>> to the same file.
>>
>> So a simple
>> INSERT INTO table_a SELECT * FROM table_b;
>> will do the trick in your example.
>>
>> --
>> Diogo Biazus
>> diogob(at)gmail(dot)com
>> http://www.softa.com.br
>> http://www.postgresql.org.br
>>
>>
>