Received: from newmail.intra.agi.de (host6.agi.de [195.177.62.46]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f69Cuka50398 for ; Mon, 9 Jul 2001 08:56:46 -0400 (EDT) (envelope-from alvar@agi.de) Received: from [192.168.100.219] (dyn219.intra.agi.de [192.168.100.219]) by newmail.intra.agi.de (8.11.0/8.11.0) with ESMTP id f69CuAY17485; Mon, 9 Jul 2001 14:56:10 +0200 Date: Mon, 09 Jul 2001 14:56:07 +0200 From: Alvar Freude To: Richard Huxton , pgsql-sql@postgresql.org Subject: Re: Returning multiple Rows from PL/pgSQL-Function Message-ID: <74854424.994690567@[192.168.100.219]> In-Reply-To: <005301c10868$029625a0$1001a8c0@archonet.com> References: <005301c10868$029625a0$1001a8c0@archonet.com> X-Mailer: Mulberry/2.1.0a6 (Win32) X-Important: http://online-demonstration.org/ | http://a-blast.org/ XXL-User: Alvar C.H. Freude, Internet-Forscher MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Archive-Number: 200107/96 X-Sequence-Number: 3880 >> 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! <<<<<<<<<<<<<<<<< |