pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Richard Huxton <dev@archonet.com>
To: Alvar Freude <alvar@agi.de>
To: pgsql-sql@postgresql.org
Subject: Re: Returning multiple Rows from PL/pgSQL-Function
Date: Mon, 9 Jul 2001 15:03:15 +0100
Message-ID: <002701c1087f$e93f67c0$1001a8c0@archonet.com> (raw)
References: <005301c10868$029625a0$1001a8c0@archonet.com>
	<74854424.994690567@[192.168.100.219]>

From: "Alvar Freude" <alvar@agi.de>

> 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




view thread (6+ messages)  latest in thread

Message-ID: <002701c1087f$e93f67c0$1001a8c0@archonet.com>
Permalink:  ../002701c1087f$e93f67c0$1001a8c0@archonet.com/
Also on:    postgresql.org/message-id/002701c1087f$e93f67c0$1001a8c0@archonet.com

 · 

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: dev@archonet.com, alvar@agi.de
  Subject: Re: Returning multiple Rows from PL/pgSQL-Function
  In-Reply-To: <002701c1087f$e93f67c0$1001a8c0@archonet.com>

* 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