Fwd: Set-valued function in wrong context

[Raymond O'Donnell <rod@xxxxxx>]

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@xxxxxx>
Reply-To: rod@xxxxxx
To: 'PostgreSQL' <pgsql-general@xxxxxxxxxxxxxx>

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@xxxxxx
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------


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