Postgres 9 alpha 5 revised, stored proc
I have this stored proc that works in pg 8.4.
create or replace function roundts(ts timestamp) returns timestamp as $$
declare
tmp integer;
result timestamp;
offset interval;
begin
tmp := extract(second from ts);
if tmp > 30 then
tmp := 60 - tmp;
offset := tmp || ' seconds';
result := ts + offset;
else
offset := tmp || ' seconds';
result := ts - offset;
end if;
tmp := extract(minute from result);
if tmp > 30 then
tmp := 60 - tmp;
offset := tmp || ' minutes';
result := result + offset;
else
offset := tmp || ' minutes';
result := result - offset;
end if;
return result;
end; $$ language plpgsql;
In 9 I get an error when trying to create it:
$ psql < roundts.sql
Timing is on.
CREATE FUNCTION
Time: 26.716 ms
ERROR: syntax error at or near "offset"
LINE 11: result := ts + offset;
^
Just wanted to report it.
-Andy
Andy Colson <andy@squeakycode.net> writes:
I have this stored proc that works in pg 8.4.
create or replace function roundts(ts timestamp) returns timestamp as $$
declare
tmp integer;
result timestamp;
offset interval;
OFFSET is a reserved word:
http://developer.postgresql.org/pgdocs/postgres/sql-keywords-appendix.html
Older versions of plpgsql didn't have quite the same rules about
reserved words as the main SQL parser. In 9.0 the rules are much
more nearly the same; in particular, unquoted use of reserved words
won't work.
regards, tom lane
On 04/07/2010 08:34 PM, Tom Lane wrote:
Andy Colson<andy@squeakycode.net> writes:
I have this stored proc that works in pg 8.4.
create or replace function roundts(ts timestamp) returns timestamp as $$
declare
tmp integer;
result timestamp;
offset interval;OFFSET is a reserved word:
http://developer.postgresql.org/pgdocs/postgres/sql-keywords-appendix.htmlOlder versions of plpgsql didn't have quite the same rules about
reserved words as the main SQL parser. In 9.0 the rules are much
more nearly the same; in particular, unquoted use of reserved words
won't work.regards, tom lane
Ah, thank you. Variable-name-changing-time again.
-Andy