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

You mean that you want to write a SELECT statement that returns the 5 first rows that have the smallest counter, and just after 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