From: | Alex Pilosov <alex(at)pilosoft(dot)com> |
---|---|
To: | Alvar Freude <alvar(at)agi(dot)de> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Returning multiple Rows from PL/pgSQL-Function |
Date: | 2001-07-09 12:54:53 |
Message-ID: | Pine.BSO.4.10.10107090853330.7004-100000@spider.pilosoft.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
>
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Alvar Freude | 2001-07-09 12:56:07 | Re: Returning multiple Rows from PL/pgSQL-Function |
Previous Message | Richard Huxton | 2001-07-09 11:12:12 | Re: Returning multiple Rows from PL/pgSQL-Function |