Thread: Returning multiple Rows from PL/pgSQL-Function
Hi, I want to create a function (PL/pgSQL), which return multiple rows. But it fails -- when Creating the function, I get a notice: NOTICE: ProcedureCreate: return type 'records' is only a shell When executing it, this error: ERROR: fmgr_info: function 0: cache lookup failed How should I do this? The function is simple: CREATE FUNCTION foo (timestamp) RETURNS SETOF records AS ' BEGIN RETURN SELECT * FROM table; END; ' LANGUAGE 'plpgsql'; 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; RETURN SELECT emotion_id,emotion1, [...] FROM emotions WHERE emotion_id BETWEEN end_id-3000 AND end_id ORDER BY date_epoch + full_rating*(3600*12) LIMIT 300; END; ' LANGUAGE'plpgsql'; Thanx for any help! 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! <<<<<<<<<<<<<<<<< |
From: "Alvar Freude" <alvar@agi.de> > Hi, > > I want to create a function (PL/pgSQL), which return multiple rows. But it > fails -- when Creating the function, I get a notice: > How should I do this? Can't at the moment. > 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? > RETURN SELECT emotion_id, emotion1, [...] > FROM emotions > WHERE emotion_id BETWEEN end_id-3000 AND end_id > ORDER BY date_epoch + full_rating*(3600*12) > LIMIT 300; > END; > ' > LANGUAGE 'plpgsql'; 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; and then issue a query like: SELECT * FROM get_emotions view WHERE emotion_id BETWEEN last_em_id(<start val here>)-3000 AND last_em_id(<start val here>); If you set the "is_cachable" flag on the last_em_id() function it should only be calculated once. HTH - Richard Huxton
Currently, this is not possible. It will be possible in 7.2, or with a patch I'm working on... On Mon, 9 Jul 2001, Alvar Freude wrote: > Hi, > > I want to create a function (PL/pgSQL), which return multiple rows. But it > fails -- when Creating the function, I get a notice: > > NOTICE: ProcedureCreate: return type 'records' is only a shell > > > When executing it, this error: > > ERROR: fmgr_info: function 0: cache lookup failed > > > How should I do this? > > > The function is simple: > > CREATE FUNCTION foo (timestamp) RETURNS SETOF records AS > ' > BEGIN > RETURN SELECT * FROM table; > END; > ' LANGUAGE 'plpgsql'; > > > > 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; > RETURN SELECT emotion_id, emotion1, [...] > FROM emotions > WHERE emotion_id BETWEEN end_id-3000 AND end_id > ORDER BY date_epoch + full_rating*(3600*12) > LIMIT 300; > END; > ' > LANGUAGE 'plpgsql'; > > > > Thanx for any help! > > > Ciao > Alvar > > > >
>> 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
FROMemotions 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! <<<<<<<<<<<<<<<<< |
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
Hi, > So - basically you want something like: > > SELECT * from emotions > WHERE emotion_date <= [cutoff time] > ORDER BY calculated_score(date_epoch,full_rating) > LIMIT 300 yes, thats it -- nearly :-) In detail the calculated_score is: (cutoff_time - creation_time) + (sum_of_rating_points * rating_factor) > 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 yes, this was also my first idea, but this depends also on the [cutoff time], so it can't work, because the trigger can't update this. > 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: aaah, wow, that's cool, I didn't know this before. but here is the same: also the resulting order can't precalculated, if i'm not completely wrong. > Is that the sort of thing you were after? nearly ;-) In detail, I have the following: Users enter from time to time some values (their "emotions") and place some dots with this. Each dot has somethinglike a lifetime relative to the other dots; this lifetime depends on the creation date and some rating of other users. Additionally it is possible to go back in the timeline and visit the stuff from an earlyer view, but with new Voting. Always 300 dots are shown, but not only the newest ones, there is also a chance to be viewed longer with besser voting. It works now -- with the two statements I posted in the last message. It's not exact because of the first select of 3000 dots -- if because of good voting the 3001th dot should be visible it isn't, but thats not critical. 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! <<<<<<<<<<<<<<<<< |