Re: Postgres 9 alpha 5 revised, stored proc

Поиск
Список
Период
Сортировка
Искать

Re: Postgres 9 alpha 5 revised, stored proc

От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Andy Colson  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

Postgres 9 alpha 5 revised, stored proc

От:
Andy Colson <andy@squeakycode.net>
Дата:
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

Re: Postgres 9 alpha 5 revised, stored proc

От:
Andy Colson <andy@squeakycode.net>
Дата:
On 04/07/2010 08:34 PM, Tom Lane wrote:
> Andy Colson  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

Ah, thank you.  Variable-name-changing-time again.

-Andy
FAQ