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

>> How should I do this?
> 
> Can't at the moment.

ups, OK -- then I misunderstand something ;)

 
>> or, in more detail the exact function:
>> 
>> 
>>    CREATE FUNCTION get_emotions (timestamp) RETURNS SETOF records AS
>>          '
>>          DECLARE
>>             start ALIAS FOR $1;
>>             end_id int4;
>>          BEGIN
>>             SELECT emotion_id FROM emotions
>>                              WHERE date <= start
>>                              LIMIT 1
>>                               INTO end_id;
> 

> Not entirely clear what your function is for, but the above select looks a
> bit odd. Do you not want to "order by" here so you can get the "most
> recent" emotion_id or whatever?

In detail, I want 300 rows older then a specific date (timeslider), but
they are sorted by time AND an additional rating. For this i have to sort
the hole table without index -- but if i presort the 3000 rows before the
specific date and catch the 300 best rated/timed rows, i save lot of time. 

It's not critical if there are some faulty rows selected ...

This is the only reason to select a subpart (3000 Rows) of the table bevore
doing the final selection which rows should be taken.


> I'd rewrite this as just a select, or a view if you want to keep things
> clean in the application, possibly with that first select encapsulated in
> a function (sorry, I'm not entirely clear what your code is doing).
>
> so:
> 
> CREATE VIEW get_emotions_view AS
> SELECT emotion_id, emotion1, ...
> ORDER BY date_epoch + full_rating*3600*12
> LIMIT 300;

hmmm, but with this, the hole ORDER BY goes throug the hole table (might be
a lot of rows), with not using the index.

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

$st_h->bind_columns(...); 
[...]


Thanks and Ciao

  Alvar


-- 
| AGI ............................................................... |
| Magirusstrasse 21B, 70469 Stuttgart . Fon +49 (0)711.228 74-50 .... |
| http://www.agi.com/diary/ (english) . http://www.agi.de/tagebuch/ . |
| >>>>>> NEWS >>> AGI holt Bronze-Loewen in Cannes! <<<<<<<<<<<<<<<<< |





view thread (6+ messages)  latest in thread

Message-ID: <74854424.994690567@[192.168.100.219]>
Permalink:  ../74854424.994690567@%5B192.168.100.219%5D/
Also on:    postgresql.org/message-id/74854424.994690567@[192.168.100.219]

 · 

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: alvar@agi.de, dev@archonet.com
  Subject: Re: Returning multiple Rows from PL/pgSQL-Function
  In-Reply-To: <74854424.994690567@[192.168.100.219]>

* 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