[GENERAL] Copying Blobs between two tables using Insert stmt
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?
Appreciate any responses.
Thank you.
John
Re: [GENERAL] Copying Blobs between two tables using Insert stmt
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 <[EMAIL PROTECTED]> 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
> [EMAIL PROTECTED]
> http://www.softa.com.br
> http://www.postgresql.org.br
>
>
Re: [GENERAL] 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 <[EMAIL PROTECTED]>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 <[EMAIL PROTECTED]> 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
>> [EMAIL PROTECTED]
>> http://www.softa.com.br
>> http://www.postgresql.org.br
>>
>>
>
