Re: Update counter when row SELECT'd ... ?

On Tue, 21 Mar 2006 04:33:22 +0100, Daniel CAUNE <d(dot)caune(at)free(dot)fr> wrote:

>> I have a simple table:
>>
>> name, url, counter
>>
>> I want to be able to do:
>>
>> SELECT * FROM table ORDER BY counter limit 5;
>>
>> But, I want counter to be incremented by 1 *if* the row is included in
>> that 5 ... so that those 5 basically move to the bottom of the list, and
>> the next 5 come up ...
>>
>> I've checked CREATE TRIGGER, and that doesn't work 'ON SELECT' ... is
>> there anything that I *can* do, other then fire back an UPDATE based on
>> the records I've received?
>>
>> Thanks ...
>>

You could also have a "new" table (which gets new rows) and an "archive
table", and move the rows from "new" to "archive" while selecting them,
using a plpgsql set-returning function .