Received: from anchor-post-30.mail.demon.net (anchor-post-30.mail.demon.net [194.217.242.88]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f69EXVa95499 for ; Mon, 9 Jul 2001 10:33:31 -0400 (EDT) (envelope-from dev@archonet.com) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-30.mail.demon.net with esmtp (Exim 2.12 #1) id 15Jc6C-0000HD-0U; Mon, 9 Jul 2001 15:33:20 +0100 Received: from client (client.archonet.com [192.168.1.16]) by mainbox.archonet.com (Postfix) with SMTP id DE37912E05; Mon, 9 Jul 2001 15:03:21 +0100 (BST) Message-ID: <002701c1087f$e93f67c0$1001a8c0@archonet.com> From: "Richard Huxton" To: "Alvar Freude" , References: <005301c10868$029625a0$1001a8c0@archonet.com> <74854424.994690567@[192.168.100.219]> Subject: Re: Returning multiple Rows from PL/pgSQL-Function Date: Mon, 9 Jul 2001 15:03:15 +0100 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Archive-Number: 200107/97 X-Sequence-Number: 3881 From: "Alvar Freude" > For now i do the hole stuff on client side with two selects: > First selecting the end_id, then (2. Statement) sort the stuff within > end_id and end_id-3000 and return the 300 most "best". > > > my $end_id = $self->db_h->selectrow_array( > "SELECT emotion_id > FROM emotions > WHERE date <= ? > ORDER BY date DESC > LIMIT 1", > undef, > $self->date_from_sliderpos($params[0])); > > my $st_h = $self->db_h->prepare( > " > SELECT emotion_id, emotion1, ..., full_rating, date > FROM emotions > WHERE emotion_id BETWEEN ? AND ? > ORDER BY date_epoch + full_rating*(3600*12) > LIMIT 300 > "); > > $st_h->execute($end_id-3000, $end_id) or die "execute kaputt"; So - basically you want something like: SELECT * from emotions WHERE emotion_date <= [cutoff time] ORDER BY calculated_score(date_epoch,full_rating) LIMIT 300 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 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: CREATE FUNCTION calculated_score(integer, integer) RETURNS integer AS ' BEGIN RETURN $1 + ($2 + 3600 + 12) END; ' LANGUAGE 'plpgsql'; CREATE INDEX emot_calc_idx ON emotions ( calculated_score(date_epoch, full_rating) ); If you've never used triggers before, there is a section in the docs and also some examples at techdocs.postgresql.org Is that the sort of thing you were after? - Richard Huxton