PostgreSQL: Create a Large Object (lo) from binary data (bytea) on server side

Again, I found something that NexJ Express uses that PostgreSQL does not have support for, that is creating large objects when given bytea data.
Like with Comparing bytea data to a Large Object on server side, I created a CAST (bytea AS oid) and a procedure to solve this problem.
The algorithm is pretty simple, get the binary data, if it is null, return null. Else create a large object and in the lowrite function, pass it the binary value, instead of a path to a file.

The code for the procedure is below. Note that the lo_manage package should be installed for this to work.

create or replace function blob_write(lbytea bytea)
   returns oid
   volatile
   language plpgsql as
$f$
   declare
      loid oid;
      lfd integer;
      lsize integer;
begin
   if(lbytea is null) then
      return null;
   end if;

   loid := lo_create(0);
   lfd := lo_open(loid,131072);
   lsize := lowrite(lfd,lbytea);
   perform lo_close(lfd);
   return loid;
end;
$f$;
CREATE CAST (bytea AS oid) WITH FUNCTION blob_write(bytea) AS ASSIGNMENT;

So now the following code works:

CREATE TABLE bytea_to_lo (
   lo largeObj
);

INSERT INTO bytea_to_lo VALUES ( DECODE('00AB','hex'));
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: