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'';
          return new;
        end if;
      end if;
      select into lo_oid loid from pg_largeobject where lo_oid = old.data;
      if found then
        raise notice ''Cleaning up the large object table'';
        perform lo_unlink(lo_oid);
        return new;
      else
        raise notice ''Did not find the right oid'';
      end if;
    end'
  language 'plpgsql';


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