Tag Archives: lo

PostgreSQL Compare bytea data to a Large Object on server side

While working on the PostgreSQL Adapter for NexJ Express, I found out that I needed to compare large objects to bytea data on the server side.
for example:
select * from Table_With_LOs where lo_value = bytea_value;

A little background:
PostgreSQL stores large object data in a separate table (pg_largeobject), and in the data column for the table it uses an oid (an integer value) as a “pointer” to this data.
So, doing "where lo_value = bytea_value", actually translates to
"where integer_value = bytea_value"

Solution:
Based on this blog post, Reading XML Files into Database ,by rhodiumtoad (he is very active in the postgresql irc channel), I created a cast function to go from oid to bytea data.

create or replace function blob_read(loid oid)
  returns bytea
  volatile
  language plpgsql as
$f$
  declare
    content bytea;
    lfd integer;
    lsize integer;
  begin
  if(loid is null) then
   return null;
  end if;
    lfd := lo_open(loid,262144);
    lsize := lo_lseek(lfd,0,2);
    perform lo_lseek(lfd,0,0);
    content := loread(lfd,lsize);
    perform lo_close(lfd);
    return content;
  end;
$f$;
CREATE CAST (oid AS bytea) WITH FUNCTION blob_read(oid) AS IMPLICIT;

So now I can use large_objects (lo/oid) and bytea in the “WHERE” clause.

Tagged , , , , ,