Skip site navigation (1) Skip section navigation (2)

Declaring timestamp variables in function

From: Laurent <lduperval(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Declaring timestamp variables in function
Date: 2011-03-10 21:35:15
Message-ID: f6284a76-53f6-4301-bd1a-96837accfb35@y36g2000pra.googlegroups.com (view raw or flat)
Thread:
Lists: pgsql-novice
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

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2011-03-10 23:27:26
Subject: Re: Declaring timestamp variables in function
Previous:From: Francisco LeoveyDate: 2011-03-10 18:02:37
Subject: psql in postgres 9.0.3 not accepting password

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group