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
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