Postgresql – How to change pg_largeobject data from psql? (posgresql-9.1)

postgresqlpostgresql-9.1

16-byte password hashes of some application are stored in the postgresql-9.1 database as LOB data. (I don't know why)

So I can get hash:

# select * from pg_largeobject where loid=16916;
 loid  | pageno |                data                
-------+--------+------------------------------------
 16916 |      0 | \x098f6bcd4621d373cade4e832627b4f6

How can I replace data of this blob or create new blob with specified data in postgres-9.1?

I see that there is lo_from_bytea function since posgresql-9.4. But there is no something like this in 9.1.

I can use lo_import – load lob from external file, but it is hard-way.
Is there something simplier?

Best Answer

Note that PostgreSQL version 9.1 has been obsoleted in 2016, it should no longer be used.

Anyway, there is a set of large object server-side functions in 9.1 that should cover your needs. They tend to expose a large object as if it was a file, providing file-like primitives.

For instance, here's a plpgsql function that replaces the value inside a large object using these primitives. It should work with 9.1 or newer:

CREATE FUNCTION lo_replace(_loid oid, _str bytea) RETURNS void
AS $$ 
declare 
 fd integer;
begin
 fd = lo_open(_loid, 393216); -- INV_READ|INV_WRITE
 if (fd < 0) then
   raise exception 'Failed to open large object %', _loid;
 end if;

 if (lo_truncate(fd, 0) != 0) then
   raise exception 'Failed to truncate large object %', _loid;
 end if;

 if (lowrite(fd, _str) != octet_length(_str)) then
   raise exception 'Failed to write to large object %', _loid;
 end if;

 if (lo_close(fd) != 0) then
   raise exception 'Failed to close large object %', _loid;
 end if;
end; 
$$ LANGUAGE plpgsql;
Related Question