From: | "Richard Huxton" <dev(at)archonet(dot)com> |
---|---|
To: | "Alvar Freude" <alvar(at)agi(dot)de>, <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Returning multiple Rows from PL/pgSQL-Function |
Date: | 2001-07-09 11:12:12 |
Message-ID: | 005301c10868$029625a0$1001a8c0@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
From: "Alvar Freude" <alvar(at)agi(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Alex Pilosov | 2001-07-09 12:54:53 | Re: Returning multiple Rows from PL/pgSQL-Function |
Previous Message | Alvar Freude | 2001-07-09 09:53:18 | Returning multiple Rows from PL/pgSQL-Function |