[SQL] 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! < | ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Returning multiple Rows from PL/pgSQL-Function
From: "Alvar Freude" <[EMAIL PROTECTED]> > 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()-3000 AND last_em_id(); If you set the "is_cachable" flag on the last_em_id() function it should only be calculated once. HTH - Richard Huxton ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Returning multiple Rows from PL/pgSQL-Function
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 > > > > ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Returning multiple Rows from PL/pgSQL-Function
>> 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! < | ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Returning multiple Rows from PL/pgSQL-Function
From: "Alvar Freude" <[EMAIL PROTECTED]> > 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Returning multiple Rows from PL/pgSQL-Function
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! < | ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] PGAccess/pgplsql Blues
FYI, I could not reproduce this problem in the current CVS sources. I created a function 'select 1;' and saved it, then opened the function and added a comment line, saved that, and it worked fine. > Roberto, > > > IIRC, pgaccess does quote-escaping for you, so if you try to write > > "standard" PL/pgSQL (escaping single quotes), it'll barf this error. > > > > Just something to check. > > Thanks. This doesn't seem to be the case; it seems to be a translation > problem: > > 1. Test fn_save_order: it's working. > 2. Open fn_save_order in PGAccess. > 3. Add '--test comment' on its own line. > 4. Save fn_save_order. > 5. test it: "Parse Error at or near "" " > > Unfortunately, I can't afford to pay Constatin for debugging, so that's > where things stand ... > > -Josh > > > __AGLIO DATABASE SOLUTIONS___ >Josh Berkus > Complete information technology [EMAIL PROTECTED] >and data management solutions (415) 565-7293 > for law firms, small businessesfax 621-2533 > and non-profit organizations. San Francisco > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] PGAccess/pgplsql Blues
Bruce, Thanks for checking that. You actually missed the beginning of the issue; it only crops up with long, complex functions > 100 lines including nested text quoting. I'm getting out of the habit of using PGAccess for anything but table lookups, anyway, so it's not worth it to me to hunt down the issue. -Josh > FYI, I could not reproduce this problem in the current CVS sources. > > I created a function 'select 1;' and saved it, then opened the > function > and added a comment line, saved that, and it worked fine. > > > Roberto, > > > > > IIRC, pgaccess does quote-escaping for you, so if you try to > write > > > "standard" PL/pgSQL (escaping single quotes), it'll barf this > error. > > > > > > Just something to check. > > > > Thanks. This doesn't seem to be the case; it seems to be a > translation > > problem: > > > > 1. Test fn_save_order: it's working. > > 2. Open fn_save_order in PGAccess. > > 3. Add '--test comment' on its own line. > > 4. Save fn_save_order. > > 5. test it: "Parse Error at or near "" " > > > > Unfortunately, I can't afford to pay Constatin for debugging, so > that's > > where things stand ... > > > > -Josh > > > > > > __AGLIO DATABASE SOLUTIONS___ > >Josh Berkus > > Complete information technology [EMAIL PROTECTED] > >and data management solutions (415) 565-7293 > > for law firms, small businessesfax 621-2533 > > and non-profit organizations. San Francisco > > > > ---(end of > broadcast)--- > > TIP 2: you can get off all lists at once with the unregister > command > > (send "unregister YourEmailAddressHere" to > [EMAIL PROTECTED]) > > > > -- > Bruce Momjian| http://candle.pha.pa.us > [EMAIL PROTECTED] | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup.| Drexel Hill, Pennsylvania > 19026 __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Problem with function & trigger
Tom Lane wrote: > Carlo Vitolo <[EMAIL PROTECTED]> writes: > > This does not work. The error is ERROR: pg_atoi: error in "12.00": can't > > parse ".00" > > What PG version are you running? It seems to work fine for me in > current sources: > > Ver. 7.1.2 > > BTW, the way you are writing the functions seems bizarrely inefficient. > Why not just: > > CREATE FUNCTION "togliscar" () RETURNS opaque AS 'BEGIN > UPDATE magazzino > SET quantita = quantita - NEW.quantita > WHERE descrizione = NEW.descrizione; > RETURN NEW; > END; > ' LANGUAGE 'plpgsql'; > > regards, tom lane Yes is better thanks! You are right it works perfectly. It was my fault. I had another trigger on insert and this function used an incorrect type of variable. By the way. Is there a way to have an error message like "ERROR xxx in Function xxx "?. Bye! ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
