Re: Fwd: Copying Blobs between two tables using Insert stmt

[Csaba Nagy <nagy@xxxxxxxxxxxxxx>]

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.



-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general