pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Richard Huxton <dev@archonet.com>
To: Alvar Freude <alvar@agi.de>
To: pgsql-sql@postgresql.org
Subject: Re: Returning multiple Rows from PL/pgSQL-Function
Date: Mon, 9 Jul 2001 12:12:12 +0100
Message-ID: <005301c10868$029625a0$1001a8c0@archonet.com> (raw)
References: <63885762.994679598@[192.168.100.219]>

From: "Alvar Freude" <alvar@agi.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




view thread (6+ messages)  latest in thread

Message-ID: <005301c10868$029625a0$1001a8c0@archonet.com>
Permalink:  ../005301c10868$029625a0$1001a8c0@archonet.com/
Also on:    postgresql.org/message-id/005301c10868$029625a0$1001a8c0@archonet.com

 · 

reply

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: dev@archonet.com, alvar@agi.de
  Subject: Re: Returning multiple Rows from PL/pgSQL-Function
  In-Reply-To: <005301c10868$029625a0$1001a8c0@archonet.com>

* 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