pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUpdate counter when row SELECT'd ... ?
3+ messages / 3 participants
[nested] [flat]
* Update counter when row SELECT'd ... ?
@ 2006-03-21 03:07 Marc G. Fournier <scrappy@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: Marc G. Fournier @ 2006-03-21 03:07 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Update counter when row SELECT'd ... ?
@ 2006-03-21 03:33 Daniel CAUNE <d.caune@free.fr>
parent: Marc G. Fournier <scrappy@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: Daniel CAUNE @ 2006-03-21 03:33 UTC (permalink / raw)
To: 'Marc G. Fournier' <scrappy@postgresql.org>; pgsql-sql
> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Update counter when row SELECT'd ... ?
@ 2006-03-21 08:56 PFC <lists@peufeu.com>
parent: Daniel CAUNE <d.caune@free.fr>
0 siblings, 0 replies; 3+ messages in thread
From: PFC @ 2006-03-21 08:56 UTC (permalink / raw)
To: pgsql-sql
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 .
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-03-21 08:56 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-21 03:07 Update counter when row SELECT'd ... ? Marc G. Fournier <scrappy@postgresql.org>
2006-03-21 03:33 ` Daniel CAUNE <d.caune@free.fr>
2006-03-21 08:56 ` PFC <lists@peufeu.com>
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