Received: from spider.pilosoft.com (p55-222.acedsl.com [160.79.55.222]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f69CiFa44646 for ; Mon, 9 Jul 2001 08:44:15 -0400 (EDT) (envelope-from alex@pilosoft.com) Received: from localhost (alexmail@localhost) by spider.pilosoft.com (8.9.3/8.9.3) with ESMTP id IAA31984; Mon, 9 Jul 2001 08:54:54 -0400 (EDT) Date: Mon, 9 Jul 2001 08:54:53 -0400 (EDT) From: Alex Pilosov To: Alvar Freude cc: pgsql-sql@postgresql.org Subject: Re: Returning multiple Rows from PL/pgSQL-Function In-Reply-To: <63885762.994679598@[192.168.100.219]> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200107/95 X-Sequence-Number: 3879 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 > > > >