Re: Detecting change in event properties

>Von: pgsql-general-owner(at)postgresql(dot)org [pgsql-general-owner(at)postgresql(dot)org]" im Auftrag von "Elliot [yields(dot)falsehood(at)gmail(dot)com]
>Gesendet: Freitag, 25. Oktober 2013 20:33
>
>On 2013-10-25 13:35, Robert James wrote:
>> On 10/25/13, Robert James <srobertjames(at)gmail(dot)com> wrote:
>>> I have a table of (timed) events, and I'm interested in marking events
>>> whose properties have changed from the previous event.
>>>
>>> I believe this can be done with window functions, but I'm not sure
>>> how. What window function can give me a field from the _previous_
>>> row?
>>>
>>> (To elaborate, I'm interested in:
>>> * Finding field x of the _previous_ row
>>> * Finding field x of the _next_ row
>>> * Finding field x of the _previous_ row that meets a certain criteria
>>> (which the current row may or may not meet)
>>> )
>> The first two are actually trivial - lag(field_x) over (order by [same
>> order as query]) and lead(...).
>>
>> But the last one seems ellusive - How can I find the value of field x
>> on the previous row WHERE a criteria is met? Is it possible to do this
>> at all with a window function?
>>
>>
>
>Maybe a custom aggregate that takes the last item in a set?

Hello,

I would misuse GUC variables for this.
(using the functions current_setting and set_config)

define a set get and switch fuction (I use operators for better readability)
something like:

select 'a' ==> 'foo'
'a'
select 'b' <==> 'foo'
'a'
select <== 'foo'
'b'

and in your query:

SELECT
case when test then col <==> 'foo' else <== 'foo' end

regards,

Marc Mamin