Thread: Pre-toast toast to toast

I'm posting this for the benefit of others who want to store large data but can't wait for 7.1.  This doesn't have all the benefits of TOAST, but can store large data without any effort in the front end.  This example is from our newly created document storage tables.  With the advent of unlimited query size in 7.0, we can send a large chunk of data to a TCL function.  The function slices it up into smaller pieces and stores it in a related data table.  An analogous function puts it back together when requested.  I successfully stored and retrieved /usr/dict/words without any problems.  I haven't tried anything bigger yet.

Thanks to Tom, Jan, and Brook who helped me get this far.

create table prd_doc (
-------------------------------------
-- Primary Keys:        base, type, class, annot
-- Foreign Keys:        crt_by - references empl
--                      lockuid - references empl

-- Columns:
   base varchar(18) not null,
   type varchar(6) not null CHECK (type in ('notes', 'fig', 'burn', 'mill', 'lathe', 'punch', 'arch')),
   class char(4) not null CHECK (class in ('assy', 'inst', 'rang', 'ship', 'oper')),
   annot varchar(24) not null,
   format varchar(18) not null,
   crt_date timestamp not null,
   crt_by int4 not null references empl,
   mod_date timestamp not null,
   mod_by int4 not null references empl,
   lockuid int4 null references empl,

   primary key (base, type, class, annot)
);

create table prd_doc_data (
-------------------------------------
-- Primary Keys:        base, type, class, annot, seq
-- Foreign Keys:        base, type, class, annot - references prd_doc

-- Columns:
   base varchar(18) not null,
   type varchar(6) not null,
   class char(4) not null,
   annot varchar(24) not null,
   seq int4 not null CHECK (seq >= 0),
   data text,

   primary key (base, type, class, annot, seq),
   foreign key (base, type, class, annot)
       references prd_doc
       on update cascade
       on delete cascade
);

-- This view has access to the whole doc table but we still use a view
-- so the crt_by/mod_by fields get updated properly.
--------------------------------------------------------------------------
create view prd_doc_v as select base, type, class, annot, format, crt_date, crt_by, mod_date, mod_by, oid as _oid from prd_doc;
create rule prd_doc_r_v_insert as on insert to prd_doc_v
    do instead
        insert into prd_doc (base, type, class, annot, format, mod_by, crt_by, mod_date, crt_date)
        values (new.base, new.type, new.class, new.annot, new.format, getpguid(), getpguid(), current_timestamp, current_timestamp);
create rule prd_doc_r_v_update as on update to prd_doc_v
    do instead
        update prd_doc set base = new.base, type = new.type, class = new.class, annot = new.annot, format = new.format, mod_date = current_timestamp, mod_by = getpguid()
        where base = old.base and type = old.type and class = old.class and annot = old.annot;
create rule prd_doc_r_v_delete as on delete to prd_doc_v
    do instead
        delete from prd_doc
        where base = old.base and type = old.type and class = old.class and annot = old.annot;

-- Store the specified document data, slicing it into pieces small enough
-- to fit into a tuple.  Update prd_doc_v$1 to show the modified date.
-- Calling sequence: prd_doc_store(base,type,class,annot,format,data)
create function prd_doc_store(text,text,text,text,text,text) returns varchar as '
    set odata $6
    set _oid {}
    spi_exec "select _oid from prd_doc_v where base = \'$1\' and type = \'$2\' and class = \'$3\' and annot = \'$4\'"
    if {$_oid == {}} {
        spi_exec "insert into prd_doc_v (base,type,class,annot,format) values (\'$1\',\'$2\',\'$3\',\'$4\',\'$5\')"
    } else {
        spi_exec "update prd_doc_v set format = \'$5\' where base = \'$1\' and type = \'$2\' and class = \'$3\' and annot = \'$4\'"
    }
    spi_exec "delete from prd_doc_data where base = \'$1\' and type = \'$2\' and class = \'$3\' and annot = \'$4\'"
    set i 0; while {$odata != {}} {
        set rec [string range $odata 0 8000]
        set odata [string range $odata [expr 8000 + 1] end]
        spi_exec "insert into prd_doc_data (base,type,class,annot,seq,data) values (\'$1\',\'$2\',\'$3\',\'$4\',$i,\'$rec\')"
        incr i
    }
    return 1
    ' LANGUAGE 'pltcl';

-- Fetch the specified document data, reassembling the bits back together
-- in the right order.
-- Calling sequence: prd_doc_fetch(base,type,class,annot)
create function prd_doc_fetch(text,text,text,text) returns text as '
    set odata {}
    spi_exec -array d "select data from prd_doc_data where base = \'$1\' and type = \'$2\' and class = \'$3\' and annot = \'$4\' order by seq" {
        append odata $d(data)
    }
    return $odata
    ' LANGUAGE 'pltcl';