Date: Mon, 20 Mar 2006 22:33:22 -0500
From: Daniel CAUNE <d.caune@free.fr>
Subject: Re: Update counter when row SELECT'd ... ?
In-reply-to: <20060320230344.Y1178@ganymede.hub.org>
To: "'Marc G. Fournier'" <scrappy@postgresql.org>, pgsql-sql@postgresql.org
Message-id: <0IWG005D0KJNGPE0@VL-MH-MR002.ip.videotron.ca>
MIME-version: 1.0
Content-type: text/plain; charset=UTF-8
Content-transfer-encoding: 7BIT
Thread-index: AcZMlKfBDpXToq7gQPeS2mOk9bDGWwAAl2Lg

> 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