drop view t1_sliced;
drop table t1;
drop sequence t1_id_seq;
drop table slice_config;
drop function slice_length (integer, integer, integer);
drop function slice_type (integer, integer, integer);

create table t1 (
	id		serial primary key,
	data	text
);

insert into t1 (data) values ('123456');
insert into t1 (data) values ('1234567890');
insert into t1 (data) values ('123456789012345');
insert into t1 (data) values ('12345678901234567890');
insert into t1 (data) values ('123456789012345678901234567');
insert into t1 (data) values ('12345678901234567890123456789012345678901234567');

create table slice_config (
	s_off		integer primary key,
	s_len		integer
);
insert into slice_config (s_off, s_len) values (0, 10);
insert into slice_config (s_off, s_len) values (10, 10);
insert into slice_config (s_off, s_len) values (20, 10);
insert into slice_config (s_off, s_len) values (30, 10);
insert into slice_config (s_off, s_len) values (40, 10);
insert into slice_config (s_off, s_len) values (50, 10);
insert into slice_config (s_off, s_len) values (60, 10);
insert into slice_config (s_off, s_len) values (70, 10);
insert into slice_config (s_off, s_len) values (80, 10);
insert into slice_config (s_off, s_len) values (90, 10);

create function slice_length (integer, integer, integer) returns integer
as '
declare
	data_size	alias for $1;
	slice_off	alias for $2;
	slice_len	alias for $3;
	frag_len	integer;
begin
	frag_len = data_size - slice_off;
	if frag_len > slice_len then
		return slice_len;
	end if;
	return frag_len;
end;
' language plpgsql;

create function slice_type (integer, integer, integer) returns text
as '
declare
	data_size	alias for $1;
	slice_off	alias for $2;
	slice_len	alias for $3;
begin
	if slice_off = 0 then
		if data_size <= slice_len then
			return ''whole'';
		end if;
		return ''start'';
	end if;
	if data_size <= slice_off + slice_len then
		return ''end'';
	end if;
	return ''middle'';
end;
' language plpgsql;

create view t1_sliced as
	select T.id, C.s_off as fragoffset, 
		slice_length (length(T.data), C.s_off, C.s_len) as fraglength,
		slice_type (length(T.data), C.s_off, C.s_len) as fragtype,
		substr (T.data, C.s_off + 1, C.s_len) as fragdata
		from t1 T, slice_config C
		where C.s_off = 0 or length(T.data) > C.s_off;

select id, length(data), data from t1;

select * from t1_sliced order by id, fragoffset;