Re: "slicing" records

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(at)Yahoo(dot)com #