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
returnedrow. That's pretty good for stuff like this, so I wonder if it wouldn't be beneficial to have something like
thatin 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!