Re: Timezones (in 8.5?)

On Nov 28, 2009, at 5:40 PM, Bruce Momjian wrote:

> I think there is general agreement that we should have a timezone data
> type which validates against pg_timezone_names().name.  It might be
> enough to just document how users can create such a domain data type,
> but I don't know of a way to do that.  Is this a TODO?

From http://justatheory.com/computers/databases/postgresql/citext-patch-submitted.html

CREATE OR REPLACE FUNCTION is_timezone( tz TEXT ) RETURNS BOOLEAN as $$
BEGIN PERFORM now() AT TIME ZONE tz; RETURN TRUE;
EXCEPTION WHEN invalid_parameter_value THEN RETURN FALSE;
END;
$$ language plpgsql STABLE;

CREATE DOMAIN timezone AS CITEXT
CHECK ( is_timezone( value ) );

It could also be TEXT I suppose, but "America/Los_Angeles" and "america/los_angeles" should be considered the same.

Best,

David