From: | Csaba Nagy <nagy(at)ecircle-ag(dot)com> |
---|---|
To: | John Skillings <jskillings07(at)gmail(dot)com> |
Cc: | Postgres general mailing list <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Fwd: Copying Blobs between two tables using Insert stmt |
Date: | 2008-10-20 11:32:59 |
Message-ID: | 1224502379.17001.42.camel@PCD12478 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2008-10-20 11:34:41 | Re: IS NULL seems to return false, even when parameter is NULL |
Previous Message | pere roca | 2008-10-20 11:21:31 | removing a portion of text |