Re: Returning multiple Rows from PL/pgSQL-Function

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