PostgreSQL: insert string in a large object from an SQL script without relying on an external file

Question

I know it's possible to insert into a large object from a PostgreSQL script using a lo_import():

INSERT INTO image (name, raster)
VALUES ('beautiful image', lo_import('/etc/motd'));

Te problem is, I'm trying to execute a script on a tightly locked up server, so I can't upload a file to it. Is it possible to insert a constant string into a large object without relying on an external file?

Answer 1

If the value is less than a 1GB, you can do this:

INSERT INTO image (name, raster)
    VALUES ('beautiful image', lo_from_bytea(0,$1));

The bytea type is a bit annoying. You may have to resort to driver-specific shenanigans to make the driver/client library understand that $1 is of type bytea. For example in with Perl's DBD::Pg, you have to prepare the statement and then do something like:

$insert->bind_param(1, $blob, { pg_type => PG_BYTEA });