jsmith

CREATE FUNCTION loin (cstring) RETURNS lo AS 'oidin' LANGUAGE internal IMMUTABLE STRICT;
CREATE FUNCTION loout (lo) RETURNS cstring AS 'oidout' LANGUAGE internal IMMUTABLE STRICT;
CREATE FUNCTION lorecv (internal) RETURNS lo AS 'oidrecv' LANGUAGE internal IMMUTABLE STRICT;
CREATE FUNCTION losend (lo) RETURNS bytea AS 'oidrecv' LANGUAGE internal IMMUTABLE STRICT;

CREATE TYPE lo ( INPUT = loin, OUTPUT = loout, RECEIVE = lorecv, SEND = losend, INTERNALLENGTH = 4, PASSEDBYVALUE );
CREATE CAST (lo AS oid) WITHOUT FUNCTION AS IMPLICIT;
CREATE CAST (oid AS lo) WITHOUT FUNCTION AS IMPLICIT;

CREATE TABLE lo_test(
 id serial,
 data lo);

create or replace function lo_cleanup() returns trigger as '
    declare
      lo_oid oid;
    begin
      --    raise notice ''Starting lo_cleanup function for large object with oid %'',old.data;
      -- If it is an update action but the BLOB (lo) field was not changed, dont do anything
      if (TG_OP = ''UPDATE'') then
        if ((old.data = new.data) or (old.data is NULL)) then
          raise notice ''Not cleaning up the large object table, as data has not changed'';
          return new;
        end if;
      end if;
      if (old.data IS NOT NULL) then
        perform lo_unlink(old.data);
        if found then
          raise notice ''Cleaning up the large object table'';
          return new;
        else
          raise exception ''Failed to cleanup the large object table'';
          return old;
        end if;
      else
        --    raise notice ''No need to cleanup the large object table, no data on old row'';
        return new;
      end if;
    end'
  language 'plpgsql';

create trigger lo_test_cleanup
after delete or update on lo_test
for each row execute procedure lo_cleanup();