Received: from newmail.intra.agi.de (host6.agi.de [195.177.62.46]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f699rQa72557 for ; Mon, 9 Jul 2001 05:53:26 -0400 (EDT) (envelope-from alvar@agi.de) Received: from [192.168.100.219] (dyn219.intra.agi.de [192.168.100.219]) by newmail.intra.agi.de (8.11.0/8.11.0) with ESMTP id f699rLY07767 for ; Mon, 9 Jul 2001 11:53:21 +0200 Date: Mon, 09 Jul 2001 11:53:18 +0200 From: Alvar Freude To: pgsql-sql@postgresql.org Subject: Returning multiple Rows from PL/pgSQL-Function Message-ID: <63885762.994679598@[192.168.100.219]> X-Mailer: Mulberry/2.1.0a6 (Win32) X-Important: http://online-demonstration.org/ | http://a-blast.org/ XXL-User: Alvar C.H. Freude, Internet-Forscher MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Archive-Number: 200107/93 X-Sequence-Number: 3877 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! <<<<<<<<<<<<<<<<< |