Claudio Lapidus wrote:
> Hello
>
> I have a table with objects' descriptions:
>
> id | length
> ---------+--------
> object1 | 40
> object2 | 66
> object3 | 12
> object4 | 107
> object5 | 220
>
> But I need to export data to a legacy system that doesn't handle lengths
> greater than 50 (don't ask me why...). Instead, it expects the data in this
> format:
Oh, it's one of these _don't ask me why_ things ... well, then "what is
the target legacy system?" ... hehe.
>
> id | length | fragment | offst
> ---------+--------+----------+-------
> object1 | 40 | whole | 0
> object2 | 50 | start | 0
> object2 | 16 | end | 50
> object3 | 12 | whole | 0
> object4 | 50 | start | 0
> object4 | 50 | middle | 50
> object4 | 7 | end | 100
> object5 | 50 | start | 0
> object5 | 50 | middle | 50
> object5 | 50 | middle | 100
> object5 | 50 | middle | 150
> object5 | 20 | end | 200
>
If there is a total upper maximum for the object length and it's not way
too obscenely large, then you can create a view that get's you this:
select id, length(data), data from t1;
id | length | data
----+--------+-------------------------------------------------
1 | 6 | 123456
2 | 10 | 1234567890
3 | 15 | 123456789012345
4 | 20 | 12345678901234567890
5 | 27 | 123456789012345678901234567
6 | 47 | 12345678901234567890123456789012345678901234567
(6 rows)
select * from t1_sliced order by id, fragoffset;
id | fragoffset | fraglength | fragtype | fragdata
----+------------+------------+----------+------------
1 | 0 | 6 | whole | 123456
2 | 0 | 10 | whole | 1234567890
3 | 0 | 10 | start | 1234567890
3 | 10 | 5 | end | 12345
4 | 0 | 10 | start | 1234567890
4 | 10 | 10 | end | 1234567890
5 | 0 | 10 | start | 1234567890
5 | 10 | 10 | middle | 1234567890
5 | 20 | 7 | end | 1234567
6 | 0 | 10 | start | 1234567890
6 | 10 | 10 | middle | 1234567890
6 | 20 | 10 | middle | 1234567890
6 | 30 | 10 | middle | 1234567890
6 | 40 | 7 | end | 1234567
(14 rows)
See attached sample script. I didn't know if you really wanted this
fancy "whole|start|middle|end" string or if that was supposed to be the
data of the fragment itself. Please notice that the view in the sample
is "configured" for data sized up to 100 characters.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
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;
