pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Alvar Freude <alvar@agi.de>
To: Richard Huxton <dev@archonet.com>
To: pgsql-sql@postgresql.org
Subject: Re: Returning multiple Rows from PL/pgSQL-Function
Date: Mon, 09 Jul 2001 17:45:13 +0200
Message-ID: <84999973.994700712@[192.168.100.219]> (raw)
In-Reply-To: <002701c1087f$e93f67c0$1001a8c0@archonet.com>
References: <002701c1087f$e93f67c0$1001a8c0@archonet.com>

Hi,

> So - basically you want something like:
> 
> SELECT * from emotions
> WHERE emotion_date <= [cutoff time]
> ORDER BY calculated_score(date_epoch,full_rating)
> LIMIT 300

yes, thats it -- nearly :-)

In detail the calculated_score is:


 (cutoff_time - creation_time) + (sum_of_rating_points * rating_factor)


> Where you'd have an index on "calculated_score". Well - you can either
> have a "score" field and use triggers to keep it up to date 

yes, this was also my first idea, but this depends also on the [cutoff
time], so it can't work, because the trigger can't update this.



> or build an
> index on the "calculated_score()" function. Depends on your pattern of
> usage which is going to be better for you.
> 
> You can create a functional index as easily as a normal one:

aaah, wow, that's cool, I didn't know this before.


but here is the same: also the resulting order can't precalculated, if i'm
not completely wrong.


> Is that the sort of thing you were after?

nearly ;-)

In detail, I have the following:

Users enter from time to time some values (their "emotions") and place some
dots with this. Each dot has somethinglike a lifetime relative to the other
dots; this lifetime depends on the creation date and some rating of other
users.
Additionally it is possible to go back in the timeline and visit the stuff
from an earlyer view, but with new Voting. Always 300 dots are shown, but
not only the newest ones, there is also a chance to be viewed longer with
besser voting. 


It works now -- with the two statements I posted in the last message. It's
not exact because of the first select of 3000 dots -- if because of good
voting the 3001th dot should be visible it isn't, but thats not critical. 


Ciao
  Alvar


-- 
| AGI ............................................................... |
| Magirusstrasse 21B, 70469 Stuttgart . Fon +49 (0)711.228 74-50 .... |
| http://www.agi.com/diary/ (english) . http://www.agi.de/tagebuch/ . |
| >>>>>> NEWS >>> AGI holt Bronze-Loewen in Cannes! <<<<<<<<<<<<<<<<< |





view thread (6+ messages)

Message-ID: <84999973.994700712@[192.168.100.219]>
Permalink:  ../84999973.994700712@%5B192.168.100.219%5D/
Also on:    postgresql.org/message-id/84999973.994700712@[192.168.100.219]

 · 

reply

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: alvar@agi.de, dev@archonet.com
  Subject: Re: Returning multiple Rows from PL/pgSQL-Function
  In-Reply-To: <84999973.994700712@[192.168.100.219]>

* 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