Index: src/backend/catalog/system_views.sql
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/catalog/system_views.sql,v
retrieving revision 1.30
diff -c -r1.30 system_views.sql
*** src/backend/catalog/system_views.sql	19 Aug 2006 01:36:24 -0000	1.30
--- src/backend/catalog/system_views.sql	7 Sep 2006 12:27:50 -0000
***************
*** 186,196 ****
  
  GRANT SELECT, UPDATE ON pg_settings TO PUBLIC;
  
! CREATE VIEW pg_timezonenames AS
      SELECT *
!     FROM pg_timezonenames() AS T
      (name text, utc_offset interval, is_dst boolean);
  
  -- Statistics views
  
  CREATE VIEW pg_stat_all_tables AS 
--- 186,201 ----
  
  GRANT SELECT, UPDATE ON pg_settings TO PUBLIC;
  
! CREATE VIEW pg_timezone_abbrevs AS
      SELECT *
!     FROM pg_timezone_abbrevs() AS T
      (name text, utc_offset interval, is_dst boolean);
  
+ CREATE VIEW pg_timezone_names AS
+     SELECT *
+ 	FROM pg_timezone_names() AS T
+ 	(name text);
+ 
  -- Statistics views
  
  CREATE VIEW pg_stat_all_tables AS 
Index: src/backend/utils/adt/datetime.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/datetime.c,v
retrieving revision 1.170
diff -c -r1.170 datetime.c
*** src/backend/utils/adt/datetime.c	4 Sep 2006 01:26:27 -0000	1.170
--- src/backend/utils/adt/datetime.c	7 Sep 2006 12:27:53 -0000
***************
*** 3842,3848 ****
   * and returns a set of (name, utc_offset, is_dst).
   */
  Datum
! pg_timezonenames(PG_FUNCTION_ARGS)
  {
  	FuncCallContext	   *funcctx;
  	int				   *pindex;
--- 3842,3848 ----
   * and returns a set of (name, utc_offset, is_dst).
   */
  Datum
! pg_timezone_abbrevs(PG_FUNCTION_ARGS)
  {
  	FuncCallContext	   *funcctx;
  	int				   *pindex;
***************
*** 3877,3883 ****
  
  		/*
  		 * build tupdesc for result tuples. This must match the
! 		 * definition of the pg_timezonenames view in system_views.sql
  		 */
  		tupdesc = CreateTemplateTupleDesc(3, false);
  		TupleDescInitEntry(tupdesc, (AttrNumber) 1, "name",
--- 3877,3883 ----
  
  		/*
  		 * build tupdesc for result tuples. This must match the
! 		 * definition of the pg_timezone_names view in system_views.sql
  		 */
  		tupdesc = CreateTemplateTupleDesc(3, false);
  		TupleDescInitEntry(tupdesc, (AttrNumber) 1, "name",
***************
*** 3928,3930 ****
--- 3928,3995 ----
  
  	SRF_RETURN_NEXT(funcctx, result);
  }
+ 
+ /*
+  * This set-returning function reads all the available time zone names 
+  */
+ Datum
+ pg_timezone_names(PG_FUNCTION_ARGS)
+ {
+ 	MemoryContext	oldcontext;
+ 	FuncCallContext	   *funcctx;
+ 	pg_tzenum          *tzenum;
+ 	pg_tz              *tz;
+ 	Datum				result;
+ 	HeapTuple			tuple;
+ 	Datum				values[1];
+ 	bool				nulls[1];
+ 
+ 	/* stuff done only on the first call of the function */
+ 	if (SRF_IS_FIRSTCALL())
+ 	{
+ 		TupleDesc		tupdesc;
+ 
+ 		/* create a function context for cross-call persistence */
+ 		funcctx = SRF_FIRSTCALL_INIT();
+ 
+ 		/*
+ 		 * switch to memory context appropriate for multiple function
+ 		 * calls
+ 		 */
+ 		oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+ 
+ 		/* allocate memory for user context */
+ 		tzenum = pg_tzenumerate_start();
+ 		funcctx->user_fctx = (void *) tzenum;
+ 
+ 		/*
+ 		 * build tupdesc for result tuples. This must match the
+ 		 * definition of the pg_timezone_abbrevs view in system_views.sql
+ 		 */
+ 		tupdesc = CreateTemplateTupleDesc(1, false);
+ 		TupleDescInitEntry(tupdesc, (AttrNumber) 1, "name",
+ 						   TEXTOID, -1, 0);
+ 
+ 		funcctx->tuple_desc = BlessTupleDesc(tupdesc);
+ 		MemoryContextSwitchTo(oldcontext);
+ 	}
+ 
+ 	/* stuff done on every call of the function */
+ 	funcctx = SRF_PERCALL_SETUP();
+ 	tzenum = (pg_tzenum *) funcctx->user_fctx;
+ 
+ 	oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+ 	tz = pg_tzenumerate_next(tzenum);
+ 	MemoryContextSwitchTo(oldcontext);
+ 	if (!tz)
+ 		SRF_RETURN_DONE(funcctx);
+ 
+ 	MemSet(nulls, 0, sizeof(nulls));
+ 
+ 	values[0] = DirectFunctionCall1(textin, CStringGetDatum(pg_get_timezone_name(tz)));
+ 
+ 	tuple = heap_form_tuple(funcctx->tuple_desc, values, nulls);
+ 	result = HeapTupleGetDatum(tuple);
+ 
+ 	SRF_RETURN_NEXT(funcctx, result);
+ }
Index: src/include/pgtime.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/pgtime.h,v
retrieving revision 1.12
diff -c -r1.12 pgtime.h
*** src/include/pgtime.h	5 Mar 2006 15:58:53 -0000	1.12
--- src/include/pgtime.h	7 Sep 2006 12:27:56 -0000
***************
*** 38,43 ****
--- 38,44 ----
  };
  
  typedef struct pg_tz pg_tz;
+ typedef struct pg_tzenum pg_tzenum;
  
  extern struct pg_tm *pg_localtime(const pg_time_t *timep, const pg_tz *tz);
  extern struct pg_tm *pg_gmtime(const pg_time_t *timep);
***************
*** 56,61 ****
--- 57,66 ----
  extern bool tz_acceptable(pg_tz *tz);
  extern const char *pg_get_timezone_name(pg_tz *tz);
  
+ extern pg_tzenum *pg_tzenumerate_start(void);
+ extern pg_tz *pg_tzenumerate_next(pg_tzenum *dir);
+ extern void pg_tzenumerate_end(pg_tzenum *dir);
+ 
  extern pg_tz *global_timezone;
  
  /* Maximum length of a timezone name (not including trailing null) */
Index: src/include/catalog/pg_proc.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/catalog/pg_proc.h,v
retrieving revision 1.422
diff -c -r1.422 pg_proc.h
*** src/include/catalog/pg_proc.h	19 Aug 2006 01:36:33 -0000	1.422
--- src/include/catalog/pg_proc.h	7 Sep 2006 12:27:58 -0000
***************
*** 3787,3793 ****
  DESCR("get the prepared statements for this session");
  DATA(insert OID = 2511 (  pg_cursor PGNSP PGUID 12 f f t t s 0 2249 "" _null_ _null_ _null_ pg_cursor - _null_ ));
  DESCR("get the open cursors for this session");
! DATA(insert OID = 2599 (  pg_timezonenames PGNSP PGUID 12 f f t t s 0 2249 "" _null_ _null_ _null_ pg_timezonenames - _null_ ));
  DESCR("get the available time zone names");
  
  /* non-persistent series generator */
--- 3787,3795 ----
  DESCR("get the prepared statements for this session");
  DATA(insert OID = 2511 (  pg_cursor PGNSP PGUID 12 f f t t s 0 2249 "" _null_ _null_ _null_ pg_cursor - _null_ ));
  DESCR("get the open cursors for this session");
! DATA(insert OID = 2599 (  pg_timezone_abbrevs PGNSP PGUID 12 f f t t s 0 2249 "" _null_ _null_ _null_ pg_timezone_abbrevs - _null_ ));
! DESCR("get the available time zone abbreviations");
! DATA(insert OID = 2600 (  pg_timezone_names PGNSP PGUID 12 f f t t s 0 2249 "" _null_ _null_ _null_ pg_timezone_names - _null_ ));
  DESCR("get the available time zone names");
  
  /* non-persistent series generator */
Index: src/include/utils/datetime.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/utils/datetime.h,v
retrieving revision 1.60
diff -c -r1.60 datetime.h
*** src/include/utils/datetime.h	25 Jul 2006 03:51:22 -0000	1.60
--- src/include/utils/datetime.h	7 Sep 2006 12:27:59 -0000
***************
*** 303,308 ****
  extern bool CheckDateTokenTables(void);
  extern void InstallTimeZoneAbbrevs(tzEntry *abbrevs, int n);
  
! extern Datum pg_timezonenames(PG_FUNCTION_ARGS);
  
  #endif   /* DATETIME_H */
--- 303,309 ----
  extern bool CheckDateTokenTables(void);
  extern void InstallTimeZoneAbbrevs(tzEntry *abbrevs, int n);
  
! extern Datum pg_timezone_abbrevs(PG_FUNCTION_ARGS);
! extern Datum pg_timezone_names(PG_FUNCTION_ARGS);
  
  #endif   /* DATETIME_H */
Index: src/timezone/pgtz.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/timezone/pgtz.c,v
retrieving revision 1.44
diff -c -r1.44 pgtz.c
*** src/timezone/pgtz.c	14 Jul 2006 14:52:27 -0000	1.44
--- src/timezone/pgtz.c	7 Sep 2006 12:28:04 -0000
***************
*** 1136,1138 ****
--- 1136,1246 ----
  		SetConfigOption("timezone", def_tz, PGC_POSTMASTER, PGC_S_ARGV);
  	}
  }
+ 
+ 
+ /*
+  * Functions to enumerate available timezones
+  *
+  * Note that pg_tzenumerate_next() will return a pointer into the pg_tzenum structure,
+  * so the data is only valid up to the next call.
+  *
+  * All data is allocated using palloc in the current context.
+  */
+ #define MAX_TZDIR_DEPTH 10
+ struct pg_tzenum {
+    int baselen;
+    int depth;
+    DIR *dirdesc[MAX_TZDIR_DEPTH];
+    char *dirname[MAX_TZDIR_DEPTH];
+    struct pg_tz tz;
+ } ;
+ 
+ pg_tzenum *
+ pg_tzenumerate_start(void) 
+ {
+    struct pg_tzenum *ret = palloc(sizeof(struct pg_tzenum));
+ 
+    ret->baselen = strlen(pg_TZDIR())+1;
+    ret->depth = 0;
+    ret->dirname[0] = pstrdup(pg_TZDIR());
+    ret->dirdesc[0] = AllocateDir(pg_TZDIR());
+    if (!ret->dirdesc[0]) 
+       ereport(ERROR,
+               (errcode_for_file_access(),
+                errmsg("could not open directory \"%s\": %m", pg_TZDIR())));
+ 
+    return ret;
+ }
+ 
+ void
+ pg_tzenumerate_end(pg_tzenum *dir)
+ {
+    while (dir->depth >= 0)
+    {
+       FreeDir(dir->dirdesc[dir->depth]);
+       pfree(dir->dirdesc[dir->depth]);
+       dir->depth--;
+    }
+    pfree(dir);
+ }
+ 
+ pg_tz *
+ pg_tzenumerate_next(pg_tzenum *dir)
+ {
+    while (dir->depth >= 0)
+    {
+       struct dirent *direntry;
+       char fullname[MAXPGPATH];
+       struct stat statbuf;
+ 
+       direntry = ReadDir(dir->dirdesc[dir->depth], dir->dirname[dir->depth]);
+ 
+       if (!direntry)
+       {
+          /* End of this directory */
+          FreeDir(dir->dirdesc[dir->depth]);
+          dir->depth--;
+          continue;
+       }
+ 
+       if (direntry->d_name[0] == '.')
+          continue;
+ 
+       snprintf(fullname, MAXPGPATH, "%s/%s", dir->dirname[dir->depth], direntry->d_name);
+       if (stat(fullname, &statbuf) != 0)
+          ereport(ERROR,
+                  (errcode_for_file_access(),
+                   errmsg("could not stat \"%s\": %m", fullname)));
+ 
+       if (S_ISDIR(statbuf.st_mode))
+       {
+          /* Step into the subdirectory */
+          if (dir->depth == MAX_TZDIR_DEPTH-1)
+             ereport(ERROR,
+                     (errmsg("timezone directory stack overflow")));
+          dir->depth++;
+          dir->dirname[dir->depth] = pstrdup(fullname);
+          dir->dirdesc[dir->depth] = AllocateDir(fullname);
+          if (!dir->dirdesc[dir->depth]) 
+             ereport(ERROR,
+                     (errcode_for_file_access(),
+                      errmsg("could not open directory \"%s\": %m", fullname)));
+ 
+          /* Start over reading in the new directory */
+          continue;
+       }
+ 
+       /* Load this timezone using tzload() not pg_tzset(), so we don't fill the cache */
+       if (tzload(fullname + dir->baselen, &dir->tz.state) != 0)
+          /* Zone could not be loaded, and we don't do parsing. Try next. */
+          continue;
+ 
+       /* Timezone loaded OK. */
+       strcpy(dir->tz.TZname, fullname + dir->baselen);
+       return &dir->tz;
+    }
+ 
+    /* Nothing more found */
+    return NULL;
+ }
+