X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id C90579DCD23 for ; Tue, 21 Mar 2006 04:55:49 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 04396-10 for ; Tue, 21 Mar 2006 04:55:50 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from peufeu.com (boutiquenumerique.com [82.67.9.10]) by postgresql.org (Postfix) with ESMTP id C1C7D9DCC57 for ; Tue, 21 Mar 2006 04:55:46 -0400 (AST) Received: (qmail 27246 invoked from network); 21 Mar 2006 09:56:15 +0100 Received: from boutiquenumerique.com (HELO apollo13) (82.67.9.10) by boutiquenumerique.com with SMTP; 21 Mar 2006 09:56:15 +0100 Date: Tue, 21 Mar 2006 09:56:14 +0100 To: pgsql-sql@postgresql.org Subject: Re: Update counter when row SELECT'd ... ? References: <0IWG005D0KJNGPE0@VL-MH-MR002.ip.videotron.ca> From: PFC Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8 MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Message-ID: In-Reply-To: <0IWG005D0KJNGPE0@VL-MH-MR002.ip.videotron.ca> User-Agent: Opera M2/8.51 (Linux, build 1462) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.111 required=5 tests=[AWL=0.111] X-Spam-Score: 0.111 X-Spam-Level: X-Archive-Number: 200603/323 X-Sequence-Number: 24669 On Tue, 21 Mar 2006 04:33:22 +0100, Daniel CAUNE 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 .