Re: Generate a dynamic sequence within a query

On 21 Oct 2010, at 24:28, Raymond O'Donnell wrote:

>> and i'd like to be able to wite a query that would result in
>>
>> 1,A,B
>> 2,A,C
>> 3,A,D
>> [...]
>>
>> PG version is 8.3.
>>
>> Any ideas?
>
> You probably want generate_series():
>
> http://www.postgresql.org/docs/8.3/static/functions-srf.html

I'm currently using WebFOCUS at work and they have a LAST operator, referring to the value a column had in the last returned row. That's pretty good for stuff like this, so I wonder if it wouldn't be beneficial to have something like that in Postgres?

SQL isn't FOCUS, but in SQL it would work something like this:

SELECT COALESCE(LAST foo +1, 1) AS foo, bar FROM table;
foo | bar
----+-----
1 | Apple
2 | Banana
3 | Orange
4 | Lemon

Or for fun,
SELECT COALESCE(LAST foo *2, 1) AS foo, bar || COALESCE(LAST bar, '') AS bar FROM table;
foo | bar
----+------------------------
1 | Apple
2 | BananaApple
4 | OrangeBananaApple
8 | LemonOrangeBananaApple

Of course being able to use LAST requires that there's still a copy of the last returned row lingering in a buffer somewhere. If we have that, great! If we don't, well, it depends on how much the devs desire such a feature :)

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.

!DSPAM:737,4cc0277010283330040792!