From: Daniel CAUNE <d.caune@free.fr>
To: 'Marc G. Fournier' <scrappy@postgresql.org>
To: pgsql-sql@postgresql.org
Subject: Re: Update counter when row SELECT'd ... ?
Date: Mon, 20 Mar 2006 22:33:22 -0500
Message-ID: <0IWG005D0KJNGPE0@VL-MH-MR002.ip.videotron.ca> (raw)
In-Reply-To: <20060320230344.Y1178@ganymede.hub.org>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: d.caune@free.fr, scrappy@postgresql.org
Subject: Re: Update counter when row SELECT'd ... ?
In-Reply-To: <0IWG005D0KJNGPE0@VL-MH-MR002.ip.videotron.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox