Re: "slicing" records

On Saturday 11 October 2003 06:00, 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:
>
> 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

Simplest way is probably to write either a plpgsql function within PG or a
perl script outside it to split up the data.

If doing it within PG, you might find Stephan Szabo's article on set-returning
functions useful (http://techdocs.postgresql.org)

--
Richard Huxton
Archonet Ltd