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

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 ...

----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664

> 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 mean that you want to write a SELECT statement that returns the 5 first rows that have the smallest counter, and
justafter what, you would like to increment their counter, right?
 

I was thinking of using a table function, something like (I didn't test it):

CREATE OR REPLACE FUNCTION my_first_url(P_Limit IN int) RETURNS SETOF table
AS $$
BEGIN FOR V_Record IN   SELECT *     FROM table     ORDER BY counter     LIMIT P_Limit LOOP   UPDATE table      SET
counter= counter + 1     WHERE name = V_Record.name      /* AND url = V_Record.url */; -- if needed
 
   RETURN V_Record; END LOOP;
END;
$$ LANGUAGE PLPGSQL;


--
Daniel

On Tue, 21 Mar 2006 04:33:22 +0100, Daniel CAUNE <d.caune@free.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 .