bug or regression in plpgsql vs word window, your choice

From: Robert Treat <xzilla(at)users(dot)sourceforge(dot)net>
To: pgsql-bugs(at)postgresql(dot)org
Subject: bug or regression in plpgsql vs word window, your choice
Date: 2010-03-17 04:20:23
Message-ID: 201003170020.24329.xzilla@users.sourceforge.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message David E. Wheeler 2010-03-17 04:50:55 Re: bug or regression in plpgsql vs word window, your choice
Previous Message Joshua Tolley 2010-03-16 23:38:32 Re: Facing problem with pg_dump