Обсуждение: Fwd: Set-valued function in wrong context

I sent the email below a while ago and haven't seen it appear yet -
apologies for the noise if you've already got it.


-------- Original Message --------
Subject: Set-valued function in wrong context
Date: Thu, 09 Oct 2008 20:04:18 +0100
From: Raymond O'Donnell <rod@iol.ie>
Reply-To: rod@iol.ie
To: 'PostgreSQL' <pgsql-general@postgresql.org>

Hi all,

I've written a function that returns a SETOF TIME WITHOUT TIME ZONE -
code below - which works fine on my development laptop (WinXP, version
8.3.4).

However, when I try it on another machine (8.2.5 on Debian Etch - yes, I
know it's out of date, but it's an installation I only play with from
time to time), I get:

gfc_bookings=# select * from make_time_series('11:00', '14:00', 30);
ERROR:  set-valued function called in context that cannot accept a set
CONTEXT:  PL/pgSQL function "make_time_series" line 10 at for over
select rows

Now, I know what the error means, and I reckon it's because of the
cast(), but for the life of me I can't see what to do about it. Any help
will be appreciated...

Thanks,

Ray.


-----------------------------

create or replace function make_time_series(
  start_time time without time zone,
  end_time time without time zone,
  mins_delta integer
) returns setof time without time zone
as
$$
declare
  TheDiff interval;
  TotalMins integer;
  ATime time without time zone;
begin
  -- Get the total number of minutes covered by the required period.
  select end_time - start_time into TheDiff;
  TotalMins := extract(hour from TheDiff) * 60 + extract(minute from
TheDiff);

  -- Generate the series.
  for ATime in
    select start_time + s.a
    from cast(generate_series(0, TotalMins, mins_delta) || ' minutes' as
interval) as s(a)
  loop
    return next ATime;
  end loop;

  return;
end;
$$
language plpgsql immutable;


------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------


--
------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

On Thu, 9 Oct 2008, Raymond O'Donnell wrote:

> gfc_bookings=# select * from make_time_series('11:00', '14:00', 30);
> ERROR:  set-valued function called in context that cannot accept a set
> CONTEXT:  PL/pgSQL function "make_time_series" line 10 at for over
> select rows
>
> Now, I know what the error means, and I reckon it's because of the
> cast(), but for the life of me I can't see what to do about it. Any help
> will be appreciated...

>   for ATime in
>     select start_time + s.a
>     from cast(generate_series(0, TotalMins, mins_delta) || ' minutes' as
> interval) as s(a)

I think you'd end up wanting something like:
 FROM ( select a * interval '1 minute' from generate_series(0, TotalMins,
mins_delta) as s(a) ) as s(a)

I changed the concatenation and cast into an interval multiply, but you
could easily do things the other way as well.

On 09/10/2008 21:25, Stephan Szabo wrote:
> I think you'd end up wanting something like:
>  FROM ( select a * interval '1 minute' from generate_series(0, TotalMins,
> mins_delta) as s(a) ) as s(a)
>
> I changed the concatenation and cast into an interval multiply, but you
> could easily do things the other way as well.

Great - thanks!

I also came up with the following after posting, when the brain finally
kicked into gear :-)

select start_time + cast(s.a::text || ' minutes' as interval)
from generate_series(0, TotalMins, mins_delta) as s(a)

Thanks for the help.

Ray.


------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------