Howdy folks, I've been testing the reconnoiter code against 9.0:
CREATE OR REPLACE FUNCTION stratcon.choose_window(IN in_start_time timestamp
with time zone, IN in_end_time timestamp with time zone, IN
in_hopeful_nperiods integer, OUT tablename text, OUT period interval, OUT
nperiods integer)
RETURNS SETOF record AS
$BODY$
declare
window record;
begin
-- Figure out which table we should be looking in
for window in
select atablename, aperiod, anperiods
from (select aperiod, round(iv/isec) ::integer as anperiods, atablename,
abs(case when iv/isec - in_hopeful_nperiods < 0
then 10 * (in_hopeful_nperiods - iv/isec)
else iv/isec - in_hopeful_nperiods
end) as badness
from (select extract('epoch' from in_end_time) -
extract('epoch' from in_start_time) as iv
) i,
( select 5*60 as isec, '5 minutes'::interval as aperiod,
'metric_numeric_rollup_5m' as atablename
union all
select 20*60 as isec, '20 minutes'::interval as aperiod,
'metric_numeric_rollup_20m' as atablename
union all
select 30*60 as isec, '30 minutes'::interval as aperiod,
'metric_numeric_rollup_30m' as atablename
union all
select 60*60 as isec, '1 hour'::interval as aperiod,
'metric_numeric_rollup_1hour' as atablename
union all
select 4*60*60 as isec, '4 hours'::interval as aaperiod,
'metric_numeric_rollup_4hour' as atablename
union all
select 24*60*60 as isec, '1 day'::interval as aperiod,
'metric_numeric_rollup_1day' as atablename
) ivs
) b
order by badness asc
limit 1
loop
tablename := window.atablename;
period := window.aperiod;
nperiods := window.anperiods;
return next;
end loop;
return;
end
$BODY$
LANGUAGE 'plpgsql' SECURITY DEFINER;
ERROR: syntax error at or near "window"
LINE 40: tablename := window.atablename;
The problem is with the variable name window; once I changed it things worked
fine. Now, Window is supposed to be a reserved word, so I am not necessarily
expecting it to work any more, but this code works fine in 8.4., so I figured I
ought to get an official word, and make sure it is documented as a regression
for future folks if that's the case.
--
Robert Treat
Conjecture: http://www.xzilla.net
Consulting: http://www.omniti.com
