🌐 AI搜索 & 代理 主页
Skip to content

Commit 5ff4f39

Browse files
tglsfdctglsfdc
committed
Rename the recently-added pg_timezonenames view to pg_timezone_abbrevs,
and create a new view pg_timezone_names that provides information about the zones known in the 'zic' database. Magnus Hagander, with some additional work by Tom Lane.
1 parent 7ed5df4 commit 5ff4f39

File tree

9 files changed

+331
-31
lines changed

9 files changed

+331
-31
lines changed

‎doc/src/sgml/catalogs.sgml‎

Lines changed: 71 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.131 2006/09/16 00:30:11 momjian Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.132 2006/09/16 20:14:32 tgl Exp $ -->
22
<!--
33
Documentation of the system catalogs, directed toward PostgreSQL developers
44
-->
@@ -4564,10 +4564,15 @@
45644564
</row>
45654565

45664566
<row>
4567-
<entry><link linkend="view-pg-timezonenames"><structname>pg_timezonenames</structname></link></entry>
4567+
<entry><link linkend="view-pg-timezone-abbrevs"><structname>pg_timezone_abbrevs</structname></link></entry>
45684568
<entry>time zone abbreviations</entry>
45694569
</row>
45704570

4571+
<row>
4572+
<entry><link linkend="view-pg-timezone-names"><structname>pg_timezone_names</structname></link></entry>
4573+
<entry>time zone names</entry>
4574+
</row>
4575+
45714576
<row>
45724577
<entry><link linkend="view-pg-user"><structname>pg_user</structname></link></entry>
45734578
<entry>database users</entry>
@@ -5884,22 +5889,74 @@
58845889

58855890
</sect1>
58865891

5887-
<sect1 id="view-pg-timezonenames">
5888-
<title><structname>pg_timezonenames</structname></title>
5892+
<sect1 id="view-pg-timezone-abbrevs">
5893+
<title><structname>pg_timezone_abbrevs</structname></title>
58895894

5890-
<indexterm zone="view-pg-timezonenames">
5891-
<primary>pg_timezonenames</primary>
5895+
<indexterm zone="view-pg-timezone-abbrevs">
5896+
<primary>pg_timezone_abbrevs</primary>
58925897
</indexterm>
58935898

58945899
<para>
5895-
The view <structname>pg_timezonenames</structname> provides a list
5900+
The view <structname>pg_timezone_abbrevs</structname> provides a list
58965901
of time zone abbreviations that are currently recognized by the datetime
58975902
input routines. The contents of this view change when the
58985903
<xref linkend="guc-timezone-abbreviations"> run-time parameter is modified.
58995904
</para>
59005905

59015906
<table>
5902-
<title><structname>pg_timezonenames</> Columns</title>
5907+
<title><structname>pg_timezone_abbrevs</> Columns</title>
5908+
5909+
<tgroup cols=3>
5910+
<thead>
5911+
<row>
5912+
<entry>Name</entry>
5913+
<entry>Type</entry>
5914+
<entry>Description</entry>
5915+
</row>
5916+
</thead>
5917+
<tbody>
5918+
<row>
5919+
<entry><structfield>abbrev</structfield></entry>
5920+
<entry><type>text</type></entry>
5921+
<entry>time zone abbreviation</entry>
5922+
</row>
5923+
<row>
5924+
<entry><structfield>utc_offset</structfield></entry>
5925+
<entry><type>interval</type></entry>
5926+
<entry>offset from UTC (positive means east of Greenwich)</entry>
5927+
</row>
5928+
<row>
5929+
<entry><structfield>is_dst</structfield></entry>
5930+
<entry><type>boolean</type></entry>
5931+
<entry>true if this is a daylight-savings abbreviation</entry>
5932+
</row>
5933+
</tbody>
5934+
</tgroup>
5935+
</table>
5936+
5937+
</sect1>
5938+
5939+
<sect1 id="view-pg-timezone-names">
5940+
<title><structname>pg_timezone_names</structname></title>
5941+
5942+
<indexterm zone="view-pg-timezone-names">
5943+
<primary>pg_timezone_names</primary>
5944+
</indexterm>
5945+
5946+
<para>
5947+
The view <structname>pg_timezone_names</structname> provides a list
5948+
of time zone names that are recognized by <command>SET TIMEZONE</>,
5949+
along with their associated abbreviations, UTC offsets,
5950+
and daylight-savings status.
5951+
Unlike the abbreviations shown in <link
5952+
linkend="view-pg-timezone-abbrevs"><structname>pg_timezone_abbrevs</structname></link>, many of these names imply a set of daylight-savings transition
5953+
date rules. Therefore, the associated information changes across local DST
5954+
boundaries. The displayed information is computed based on the current
5955+
value of <function>CURRENT_TIMESTAMP</>.
5956+
</para>
5957+
5958+
<table>
5959+
<title><structname>pg_timezone_names</> Columns</title>
59035960

59045961
<tgroup cols=3>
59055962
<thead>
@@ -5913,6 +5970,11 @@
59135970
<row>
59145971
<entry><structfield>name</structfield></entry>
59155972
<entry><type>text</type></entry>
5973+
<entry>time zone name</entry>
5974+
</row>
5975+
<row>
5976+
<entry><structfield>abbrev</structfield></entry>
5977+
<entry><type>text</type></entry>
59165978
<entry>time zone abbreviation</entry>
59175979
</row>
59185980
<row>
@@ -5923,7 +5985,7 @@
59235985
<row>
59245986
<entry><structfield>is_dst</structfield></entry>
59255987
<entry><type>boolean</type></entry>
5926-
<entry>true if this is a daylight-savings zone</entry>
5988+
<entry>true if currently observing daylight savings</entry>
59275989
</row>
59285990
</tbody>
59295991
</tgroup>

‎src/backend/catalog/system_views.sql‎

Lines changed: 6 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33
*
44
* Copyright (c) 1996-2006, PostgreSQL Global Development Group
55
*
6-
* $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.30 2006/08/19 01:36:24 tgl Exp $
6+
* $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.31 2006/09/16 20:14:33 tgl Exp $
77
*/
88

99
CREATE VIEW pg_roles AS
@@ -186,10 +186,11 @@ CREATE RULE pg_settings_n AS
186186

187187
GRANT SELECT, UPDATE ON pg_settings TO PUBLIC;
188188

189-
CREATE VIEW pg_timezonenames AS
190-
SELECT *
191-
FROM pg_timezonenames() AS T
192-
(name text, utc_offset interval, is_dst boolean);
189+
CREATE VIEW pg_timezone_abbrevs AS
190+
SELECT * FROM pg_timezone_abbrevs();
191+
192+
CREATE VIEW pg_timezone_names AS
193+
SELECT * FROM pg_timezone_names();
193194

194195
-- Statistics views
195196

‎src/backend/utils/adt/datetime.c‎

Lines changed: 118 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/utils/adt/datetime.c,v 1.170 2006/09/04 01:26:27 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/utils/adt/datetime.c,v 1.171 2006/09/16 20:14:33 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -3839,21 +3839,21 @@ InstallTimeZoneAbbrevs(tzEntry *abbrevs, int n)
38393839

38403840
/*
38413841
* This set-returning function reads all the available time zone abbreviations
3842-
* and returns a set of (name, utc_offset, is_dst).
3842+
* and returns a set of (abbrev, utc_offset, is_dst).
38433843
*/
38443844
Datum
3845-
pg_timezonenames(PG_FUNCTION_ARGS)
3845+
pg_timezone_abbrevs(PG_FUNCTION_ARGS)
38463846
{
38473847
FuncCallContext *funcctx;
38483848
int *pindex;
38493849
Datum result;
3850-
Interval *resInterval;
38513850
HeapTuple tuple;
38523851
Datum values[3];
38533852
bool nulls[3];
38543853
char buffer[TOKMAXLEN + 1];
38553854
unsigned char *p;
38563855
struct pg_tm tm;
3856+
Interval *resInterval;
38573857

38583858
/* stuff done only on the first call of the function */
38593859
if (SRF_IS_FIRSTCALL())
@@ -3876,11 +3876,11 @@ pg_timezonenames(PG_FUNCTION_ARGS)
38763876
funcctx->user_fctx = (void *) pindex;
38773877

38783878
/*
3879-
* build tupdesc for result tuples. This must match the
3880-
* definition of the pg_timezonenames view in system_views.sql
3879+
* build tupdesc for result tuples. This must match this function's
3880+
* pg_proc entry!
38813881
*/
38823882
tupdesc = CreateTemplateTupleDesc(3, false);
3883-
TupleDescInitEntry(tupdesc, (AttrNumber) 1, "name",
3883+
TupleDescInitEntry(tupdesc, (AttrNumber) 1, "abbrev",
38843884
TEXTOID, -1, 0);
38853885
TupleDescInitEntry(tupdesc, (AttrNumber) 2, "utc_offset",
38863886
INTERVALOID, -1, 0);
@@ -3928,3 +3928,114 @@ pg_timezonenames(PG_FUNCTION_ARGS)
39283928

39293929
SRF_RETURN_NEXT(funcctx, result);
39303930
}
3931+
3932+
/*
3933+
* This set-returning function reads all the available full time zones
3934+
* and returns a set of (name, abbrev, utc_offset, is_dst).
3935+
*/
3936+
Datum
3937+
pg_timezone_names(PG_FUNCTION_ARGS)
3938+
{
3939+
MemoryContext oldcontext;
3940+
FuncCallContext *funcctx;
3941+
pg_tzenum *tzenum;
3942+
pg_tz *tz;
3943+
Datum result;
3944+
HeapTuple tuple;
3945+
Datum values[4];
3946+
bool nulls[4];
3947+
int tzoff;
3948+
struct pg_tm tm;
3949+
fsec_t fsec;
3950+
char *tzn;
3951+
Interval *resInterval;
3952+
struct pg_tm itm;
3953+
3954+
/* stuff done only on the first call of the function */
3955+
if (SRF_IS_FIRSTCALL())
3956+
{
3957+
TupleDesc tupdesc;
3958+
3959+
/* create a function context for cross-call persistence */
3960+
funcctx = SRF_FIRSTCALL_INIT();
3961+
3962+
/*
3963+
* switch to memory context appropriate for multiple function
3964+
* calls
3965+
*/
3966+
oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
3967+
3968+
/* initialize timezone scanning code */
3969+
tzenum = pg_tzenumerate_start();
3970+
funcctx->user_fctx = (void *) tzenum;
3971+
3972+
/*
3973+
* build tupdesc for result tuples. This must match this function's
3974+
* pg_proc entry!
3975+
*/
3976+
tupdesc = CreateTemplateTupleDesc(4, false);
3977+
TupleDescInitEntry(tupdesc, (AttrNumber) 1, "name",
3978+
TEXTOID, -1, 0);
3979+
TupleDescInitEntry(tupdesc, (AttrNumber) 2, "abbrev",
3980+
TEXTOID, -1, 0);
3981+
TupleDescInitEntry(tupdesc, (AttrNumber) 3, "utc_offset",
3982+
INTERVALOID, -1, 0);
3983+
TupleDescInitEntry(tupdesc, (AttrNumber) 4, "is_dst",
3984+
BOOLOID, -1, 0);
3985+
3986+
funcctx->tuple_desc = BlessTupleDesc(tupdesc);
3987+
MemoryContextSwitchTo(oldcontext);
3988+
}
3989+
3990+
/* stuff done on every call of the function */
3991+
funcctx = SRF_PERCALL_SETUP();
3992+
tzenum = (pg_tzenum *) funcctx->user_fctx;
3993+
3994+
/* search for another zone to display */
3995+
for (;;)
3996+
{
3997+
oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
3998+
tz = pg_tzenumerate_next(tzenum);
3999+
MemoryContextSwitchTo(oldcontext);
4000+
4001+
if (!tz)
4002+
{
4003+
pg_tzenumerate_end(tzenum);
4004+
funcctx->user_fctx = NULL;
4005+
SRF_RETURN_DONE(funcctx);
4006+
}
4007+
4008+
/* Convert now() to local time in this zone */
4009+
if (timestamp2tm(GetCurrentTransactionStartTimestamp(),
4010+
&tzoff, &tm, &fsec, &tzn, tz) != 0)
4011+
continue; /* ignore if conversion fails */
4012+
4013+
/* Ignore zic's rather silly "Factory" time zone */
4014+
if (tzn && strcmp(tzn, "Local time zone must be set--see zic manual page") == 0)
4015+
continue;
4016+
4017+
/* Found a displayable zone */
4018+
break;
4019+
}
4020+
4021+
MemSet(nulls, 0, sizeof(nulls));
4022+
4023+
values[0] = DirectFunctionCall1(textin,
4024+
CStringGetDatum(pg_get_timezone_name(tz)));
4025+
4026+
values[1] = DirectFunctionCall1(textin,
4027+
CStringGetDatum(tzn ? tzn : ""));
4028+
4029+
MemSet(&itm, 0, sizeof(struct pg_tm));
4030+
itm.tm_sec = -tzoff;
4031+
resInterval = (Interval *) palloc(sizeof(Interval));
4032+
tm2interval(&itm, 0, resInterval);
4033+
values[2] = IntervalPGetDatum(resInterval);
4034+
4035+
values[3] = BoolGetDatum(tm.tm_isdst > 0);
4036+
4037+
tuple = heap_form_tuple(funcctx->tuple_desc, values, nulls);
4038+
result = HeapTupleGetDatum(tuple);
4039+
4040+
SRF_RETURN_NEXT(funcctx, result);
4041+
}

‎src/include/catalog/catversion.h‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -37,7 +37,7 @@
3737
* Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group
3838
* Portions Copyright (c) 1994, Regents of the University of California
3939
*
40-
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.356 2006/09/14 22:05:06 tgl Exp $
40+
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.357 2006/09/16 20:14:33 tgl Exp $
4141
*
4242
*-------------------------------------------------------------------------
4343
*/
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/* yyyymmddN */
56-
#define CATALOG_VERSION_NO 200609141
56+
#define CATALOG_VERSION_NO 200609161
5757

5858
#endif

‎src/include/catalog/pg_proc.h‎

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.424 2006/09/14 22:05:06 tgl Exp $
10+
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.425 2006/09/16 20:14:33 tgl Exp $
1111
*
1212
* NOTES
1313
* The script catalog/genbki.sh reads this file and generates .bki
@@ -3791,7 +3791,9 @@ DATA(insert OID = 2510 ( pg_prepared_statement PGNSP PGUID 12 f f t t s 0 2249
37913791
DESCR("get the prepared statements for this session");
37923792
DATA(insert OID = 2511 ( pg_cursor PGNSP PGUID 12 f f t t s 0 2249 "" _null_ _null_ _null_ pg_cursor - _null_ ));
37933793
DESCR("get the open cursors for this session");
3794-
DATA(insert OID = 2599 ( pg_timezonenames PGNSP PGUID 12 f f t t s 0 2249 "" _null_ _null_ _null_ pg_timezonenames - _null_ ));
3794+
DATA(insert OID = 2599 ( pg_timezone_abbrevs PGNSP PGUID 12 f f t t s 0 2249 "" "{25,1186,16}" "{o,o,o}" "{abbrev,utc_offset,is_dst}" pg_timezone_abbrevs - _null_ ));
3795+
DESCR("get the available time zone abbreviations");
3796+
DATA(insert OID = 2856 ( pg_timezone_names PGNSP PGUID 12 f f t t s 0 2249 "" "{25,25,1186,16}" "{o,o,o,o}" "{name,abbrev,utc_offset,is_dst}" pg_timezone_names - _null_ ));
37953797
DESCR("get the available time zone names");
37963798

37973799
/* non-persistent series generator */

‎src/include/pgtime.h‎

Lines changed: 6 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -6,7 +6,7 @@
66
* Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group
77
*
88
* IDENTIFICATION
9-
* $PostgreSQL: pgsql/src/include/pgtime.h,v 1.12 2006/03/05 15:58:53 momjian Exp $
9+
* $PostgreSQL: pgsql/src/include/pgtime.h,v 1.13 2006/09/16 20:14:33 tgl Exp $
1010
*
1111
*-------------------------------------------------------------------------
1212
*/
@@ -38,6 +38,7 @@ struct pg_tm
3838
};
3939

4040
typedef struct pg_tz pg_tz;
41+
typedef struct pg_tzenum pg_tzenum;
4142

4243
extern struct pg_tm *pg_localtime(const pg_time_t *timep, const pg_tz *tz);
4344
extern struct pg_tm *pg_gmtime(const pg_time_t *timep);
@@ -56,6 +57,10 @@ extern pg_tz *pg_tzset(const char *tzname);
5657
extern bool tz_acceptable(pg_tz *tz);
5758
extern const char *pg_get_timezone_name(pg_tz *tz);
5859

60+
extern pg_tzenum *pg_tzenumerate_start(void);
61+
extern pg_tz *pg_tzenumerate_next(pg_tzenum *dir);
62+
extern void pg_tzenumerate_end(pg_tzenum *dir);
63+
5964
extern pg_tz *global_timezone;
6065

6166
/* Maximum length of a timezone name (not including trailing null) */

‎src/include/utils/datetime.h‎

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -9,7 +9,7 @@
99
* Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group
1010
* Portions Copyright (c) 1994, Regents of the University of California
1111
*
12-
* $PostgreSQL: pgsql/src/include/utils/datetime.h,v 1.60 2006/07/25 03:51:22 tgl Exp $
12+
* $PostgreSQL: pgsql/src/include/utils/datetime.h,v 1.61 2006/09/16 20:14:33 tgl Exp $
1313
*
1414
*-------------------------------------------------------------------------
1515
*/
@@ -303,6 +303,7 @@ extern int j2day(int jd);
303303
extern bool CheckDateTokenTables(void);
304304
extern void InstallTimeZoneAbbrevs(tzEntry *abbrevs, int n);
305305

306-
extern Datum pg_timezonenames(PG_FUNCTION_ARGS);
306+
extern Datum pg_timezone_abbrevs(PG_FUNCTION_ARGS);
307+
extern Datum pg_timezone_names(PG_FUNCTION_ARGS);
307308

308309
#endif /* DATETIME_H */

0 commit comments

Comments
 (0)