Thread: Copying large object in a stored procedure

Hi all,

Is there an easy way to copy a large object to a new one, having a new
OID and it's content independent from the original ?

The large object API functions would only allow to stream the original
to the client and stream it back to the server for the copy, which is
not really optimal... I would like to do it all on the server side. The
server side functions would work to export the original to a file and
then import it again, but that needs superuser privileges which rules
that out.

I could simply copy the contents of the pg_largeobject table, but then I
need  a new OID for that. I can use the server side large object API to
create an empty large object, delete it, then use the obtained OID for
the copy... but that also sounds somewhat suboptimal.

Is there any simple way to copy a large object ?

Cheers,
Csaba.

> Is there an easy way to copy a large object to a new one, having a new
> OID and it's content independent from the original ?

So my current solution would be:

CREATE OR REPLACE FUNCTION copy_blob(p_blobId OID)
RETURNS OID AS '
DECLARE
    v_NewOID BIGINT;
BEGIN
    SELECT lo_create(0) 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;


I would still be grateful if anybody knows a better solution using plain
SQL/plpgsql...

Cheers,
Csaba.

[snip]
> DECLARE
>     v_NewOID BIGINT;
                           ^^^^^^
small correction: v_NewOID should be of type OID:

DECLARE
    v_NewOID OID;

BIGINT would of course work too as long as there is implicit cast from
it to/from OID, which seems to be the case on 8.2.

Cheers,
Csaba.