Tag Archives: PostgreSQL

PostgreSQL Windows x64: Enabling UUID-OSSP.sql

The background to this is that for that for over 6 months now, I have been working on developing a PostgreSQL Adapter for NexJ Express, a part of Open Health Tools. Though most of the development is done in Java, I have also learnt a lot about PostgreSQL.

The Problem:
Although PostgreSQL has a UUID type, the UUID generation is dependent on the UUID-OSSP.sql module which in turn depends on the OSSP-UUID library.
The PostgreSQL installer for Windows includes the UUID-OSSP.sql module for x86 builds but not for x64 builds (lastest version that I checked was PostgreSQL 9.0.4).
When I asked on #postgresql irc channel, one of the things I was told was that there was not OSSP-UUID library build for windows x64.

The Solution: (tested with PG 9.0.4 x64 build)
Turns out that there was a build of OSSP-UUID for x64 windows in the wild. Thanks to Hiroshi Saito, who replied to my message on the pg_general mailing list.

  1. Download pg90_uuid_ossp_x64.zip,
  2. Unzip the contents of the zip to path/to/PostgreSQL/9.0/
  3. Open psql and install uuid-ossp.sql using the command:
    \i 'path/to/PostgeSQL/9.0/share/contrib/uuid-ossp.sql'

Also, Hiroshi provides the source if you interested on compiling OSSP-UUID on x64 windows for yourself.

Tagged , , , ,

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 , , , , ,