Re: Fwd: Copying Blobs between two tables using Insert stmt - Mailing list pgsql-general

On Fri, 2008-10-17 at 13:40 -0700, John Skillings wrote:
> Hello Csaba,
>
> Back in the month of April, I noticed that you posted a similar
> request on copying blobs between two tables, having separate OID.
>
> Can you let me know your final solution please.

The final version I'm using is this one:

CREATE OR REPLACE FUNCTION copy_blob(p_blobId oid)
RETURNS oid
AS '
DECLARE
    v_NewOID OID;
    v_LODesc INTEGER;
BEGIN
    SELECT lo_create(0) INTO v_NewOID;

    SELECT lo_open(v_NewOID, -1) INTO v_LODesc;

    PERFORM lowrite(v_LODesc, data) FROM pg_largeobject WHERE loid =
p_blobId ORDER BY pageno;

    PERFORM lo_close(v_LODesc);

    RETURN v_NewOID;
END;
' LANGUAGE 'plpgsql';

The only drawback is that you must enable reading of pg_largeobject for
the application user, as by default only the postgres super user can
access it. You would do that with something like (as postgres super
user):

GRANT SELECT ON pg_largeobject TO my_app_user;

Cheers,
Csaba.