> I've looked at it again and I think the code is correct,
Good news! I will look into your explanation.
> but I
> miswrote that the array needs to be sorted. The above query returns:
> x | y | nth_value
> ---+---+-----------
> 1 | 1 | 2
> 2 | 2 | 1
> 3 | | 2
> 4 | 4 |
> 5 | | 4
> 6 | 6 | 7
> 7 | 7 | 6
> (7 rows)
>
> This is correct, for values of x:
>
> 1: The first non-null value of y is at position 0, however we have
> EXCLUDE CURRENT ROW so it picks the next non-null value at position 1
> and stores it in the array, returning 2.
> 2: We can now take the first non-null value of y at position 0 and
> store it in the array, returning 1.
> 3. We take 1 preceding, using the position stored in the array, returning 2.
> 4. 1 preceding and 1 following are both null, and we exclude the
> current row, so returning null.
> 5. 1 preceding is at position 3, store it in the array, returning 4.
> 6. 1 preceding is null and we exclude the current row, so store
> position 6 in the array, returning 7.
> 7. 1 preceding is at position 5, store it in the array and return 6.
>
> It will be unordered when the EXCLUDE clause is used but the code
> should handle this correctly.
Best reagards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
