pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedReturning multiple Rows from PL/pgSQL-Function
6+ messages / 3 participants
[nested] [flat]
* Returning multiple Rows from PL/pgSQL-Function
@ 2001-07-09 09:53 Alvar Freude <alvar@agi.de>
0 siblings, 2 replies; 6+ messages in thread
From: Alvar Freude @ 2001-07-09 09:53 UTC (permalink / raw)
To: pgsql-sql
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! <<<<<<<<<<<<<<<<< |
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Returning multiple Rows from PL/pgSQL-Function
@ 2001-07-09 11:12 Richard Huxton <dev@archonet.com>
parent: Alvar Freude <alvar@agi.de>
1 sibling, 1 reply; 6+ messages in thread
From: Richard Huxton @ 2001-07-09 11:12 UTC (permalink / raw)
To: Alvar Freude <alvar@agi.de>; pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Returning multiple Rows from PL/pgSQL-Function
@ 2001-07-09 12:54 Alex Pilosov <alex@pilosoft.com>
parent: Alvar Freude <alvar@agi.de>
1 sibling, 0 replies; 6+ messages in thread
From: Alex Pilosov @ 2001-07-09 12:54 UTC (permalink / raw)
To: Alvar Freude <alvar@agi.de>; +Cc: pgsql-sql
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
>
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Returning multiple Rows from PL/pgSQL-Function
@ 2001-07-09 12:56 Alvar Freude <alvar@agi.de>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 6+ messages in thread
From: Alvar Freude @ 2001-07-09 12:56 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; pgsql-sql
>> 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! <<<<<<<<<<<<<<<<< |
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Returning multiple Rows from PL/pgSQL-Function
@ 2001-07-09 14:03 Richard Huxton <dev@archonet.com>
parent: Alvar Freude <alvar@agi.de>
0 siblings, 1 reply; 6+ messages in thread
From: Richard Huxton @ 2001-07-09 14:03 UTC (permalink / raw)
To: Alvar Freude <alvar@agi.de>; pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Returning multiple Rows from PL/pgSQL-Function
@ 2001-07-09 15:45 Alvar Freude <alvar@agi.de>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 6+ messages in thread
From: Alvar Freude @ 2001-07-09 15:45 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; pgsql-sql
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! <<<<<<<<<<<<<<<<< |
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2001-07-09 15:45 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-07-09 09:53 Returning multiple Rows from PL/pgSQL-Function Alvar Freude <alvar@agi.de>
2001-07-09 11:12 ` Richard Huxton <dev@archonet.com>
2001-07-09 12:56 ` Alvar Freude <alvar@agi.de>
2001-07-09 14:03 ` Richard Huxton <dev@archonet.com>
2001-07-09 15:45 ` Alvar Freude <alvar@agi.de>
2001-07-09 12:54 ` Alex Pilosov <alex@pilosoft.com>
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