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