Thread: Declaring timestamp variables in function

Hi,

I'm trying to create a function that updates fields according to the
current date. One of the fields needs to be a numeric UTC value.

So, I'm trying something like this:

-- This rounds a timestamp to the the nearest 30 minute mark
CREATE OR REPLACE FUNCTION round_time(TIMESTAMP WITH TIME ZONE)
RETURNS TIMESTAMP WITH TIME ZONE AS $$
  SELECT date_trunc('hour', $1) + INTERVAL '30 min' *
ROUND(date_part('minute', $1) / 30.0)
$$ LANGUAGE SQL;

CREATE or replace FUNCTION updateDates() RETURNS void AS $$
declare
    currentDate timestamp(3);
BEGIN
currentDate =  := round_time(current_date);
 ...
-- here I update my date
END;
$$ LANGUAGE SQL;

The problem is that my currentDate declaration always generates an
error:

********** Error **********

ERROR: syntax error at or near "timestamp"
SQL state: 42601
Character: 301

I thought this was legal syntax (with 8.4) but evidently, I'm wrong.

My questions is: where?

Thanks,

L

Laurent <lduperval@gmail.com> writes:
> CREATE or replace FUNCTION updateDates() RETURNS void AS $$
> declare
>     currentDate timestamp(3);
> BEGIN
> currentDate =  := round_time(current_date);
>  ...
> -- here I update my date
> END;
> $$ LANGUAGE SQL;

> The problem is that my currentDate declaration always generates an
> error:

> ********** Error **********

> ERROR: syntax error at or near "timestamp"

The problem's at the other end of that command :-(.  You need to
say language plpgsql, not language sql.

            regards, tom lane

On Mar 10, 6:27 pm, t...@sss.pgh.pa.us (Tom Lane) wrote:
> Laurent <lduper...@gmail.com> writes:
> > -- here I update my date
> > END;
> > $$ LANGUAGE SQL;
> The problem's at the other end of that command :-(.  You need to
> say language plpgsql, not language sql.
>

Yep, that was it. All is well now, thanks.

L