Received: from anchor-post-30.mail.demon.net (anchor-post-30.mail.demon.net [194.217.242.88]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f69BXKa13270 for ; Mon, 9 Jul 2001 07:33:20 -0400 (EDT) (envelope-from dev@archonet.com) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-30.mail.demon.net with esmtp (Exim 2.12 #1) id 15JZHv-000NeM-0U; Mon, 9 Jul 2001 12:33:15 +0100 Received: from client (client.archonet.com [192.168.1.16]) by mainbox.archonet.com (Postfix) with SMTP id 392611408E; Mon, 9 Jul 2001 12:12:17 +0100 (BST) Message-ID: <005301c10868$029625a0$1001a8c0@archonet.com> From: "Richard Huxton" To: "Alvar Freude" , References: <63885762.994679598@[192.168.100.219]> Subject: Re: Returning multiple Rows from PL/pgSQL-Function Date: Mon, 9 Jul 2001 12:12:12 +0100 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Archive-Number: 200107/94 X-Sequence-Number: 3878 From: "Alvar Freude" > 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()-3000 AND last_em_id(); If you set the "is_cachable" flag on the last_em_id() function it should only be calculated once. HTH - Richard Huxton