Re: Returning multiple Rows from PL/pgSQL-Function

From: "Alvar Freude" <alvar(at)agi(dot)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