From: Alvar Freude <alvar@agi.de>
To: pgsql-sql@postgresql.org
Subject: Returning multiple Rows from PL/pgSQL-Function
Date: Mon, 09 Jul 2001 11:53:18 +0200
Message-ID: <63885762.994679598@[192.168.100.219]> (raw)
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! <<<<<<<<<<<<<<<<< |
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: alvar@agi.de
Subject: Re: Returning multiple Rows from PL/pgSQL-Function
In-Reply-To: <63885762.994679598@[192.168.100.219]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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