Has anyone ever tried that? I've got an application I need to use the client side lo_import via a perl script. Trouble is I'm connecting to a database in one network (call it host1) zone but need the blob data in a database in another network zone (call it host2). I *might* be able to do this by first loading the blob into host1 and then doing something like the below ($OrigLOBId is from the script calling lo_import on host1, connection is to host1):
INSERT INTO foreign_table.pg_largeobject_metadata (lomowner, lomacl)
SELECT lomowner, lomacl
FROM pg_largeobject_metadata
WHERE oid = $OrigLOBId
RETURNING oid AS foreign_oid;
INSERT INTO foreign_table.pg_largeobject (loid, pageno, data)
SELECT foreign, pageno, data
FROM pg_largeobject
WHERE loid = $OrigLOBId;
SELECT lo_unlink($OrigLOBId);
I think this would work, but I don't really like it. Creating a r/w fdw for pg_largeobject and pg_largeobject_metadata strikes me as an extremely unwise idea.
Thoughts?
INSERT INTO foreign_table.pg_largeobject_metadata (lomowner, lomacl)
SELECT lomowner, lomacl
FROM pg_largeobject_metadata
WHERE oid = $OrigLOBId
RETURNING oid AS foreign_oid;
INSERT INTO foreign_table.pg_largeobject (loid, pageno, data)
SELECT foreign, pageno, data
FROM pg_largeobject
WHERE loid = $OrigLOBId;
SELECT lo_unlink($OrigLOBId);
I think this would work, but I don't really like it. Creating a r/w fdw for pg_largeobject and pg_largeobject_metadata strikes me as an extremely unwise idea.
Thoughts?