-- SQL support for fake lobs for ACS/Postgres.-- Don Baccus February 2000-- for each user table my_table in which you want to stuff large-- amounts of data:-- define a column "lob integer references lobs"-- do "create trigger my_table_lob_trig before delete or update or insert-- on my_table for each row execute procedure on_lob_ref()"-- to initialize a row's lob column, use empty_lob():-- insert into my_table (lob) values(empty_lob());-- deletes and updates on my_table use reference count information-- to delete data from lobs and lob_data when appropriate.create sequence lob_sequence;create table lobs ( lob_id integer not null primary key, refcount integer not null default 0);create function on_lobs_delete() returns opaque as 'begin delete from lob_data where lob_id = old.lob_id; return old;end;' language 'plpgsql';create trigger lobs_delete_trig before delete on lobsfor each row execute procedure on_lobs_delete();create table lob_data ( lob_id integer not null references lobs, segment integer not null, byte_len integer not null, data bytea not null, primary key (lob_id, segment));create index lob_data_index on lob_data(lob_id);-- Note - race conditions might cause problems here, but I -- couldn't get locking to work consistently between PG 6.5-- and PG 7.0. The ACS doesn't share LOBs between tables-- or rows within a table anyway, I don't think/hope.create function on_lob_ref() returns opaque as 'begin if TG_OP = ''UPDATE'' then if new.lob = old.lob then return new; end if; end if; if TG_OP = ''INSERT'' or TG_OP = ''UPDATE'' then if new.lob is not null then insert into lobs select new.lob, 0 where 0 = (select count(*) from lobs where lob_id = new.lob); update lobs set refcount = refcount + 1 where lob_id = new.lob; end if; end if; if TG_OP <> ''INSERT'' then if old.lob is not null then update lobs set refcount = refcount - 1 where lob_id = old.lob; delete from lobs where lob_id = old.lob and refcount = 0; end if; end if; if TG_OP = ''INSERT'' or TG_OP = ''UPDATE'' then return new; else return old; end if;end;' language 'plpgsql';create function empty_lob() returns integer as 'begin return nextval(''lob_sequence'');end;' language 'plpgsql';create function lob_get_data(integer) returns text as 'declare lob_id integer; v_rec record; v_data text default '''';begin for v_rec in select data, segment from lob_data order by segment; v_data := v_data || v_rec.data; end if; return v_data;end;' language 'plpgsql';create function lob_copy(integer, integer) returns integer as 'declare from_id alias for $1; to_id alias for $2;begin insert into lobs (lob_id,refcount) values (to_id,0); insert into lob_data select to_id as lob_id, segment, byte_len, data from lob_data where lob_id = from_id; return null;end;' language 'plpgsql';create function lob_length(integer) returns integer as 'declare id alias for $1;begin return sum(byte_len) from lob_data where lob_id = id;end;' language 'plpgsql';