Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit5ff4f39

Browse files
committed
Rename the recently-added pg_timezonenames view to pg_timezone_abbrevs,
and create a new view pg_timezone_names that provides information aboutthe zones known in the 'zic' database. Magnus Hagander, with someadditional work by Tom Lane.
1 parent7ed5df4 commit5ff4f39

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/1600:30:11 momjian Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.132 2006/09/1620: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 ifthis is adaylight-savings zone</entry>
5988+
<entry>true ifcurrently observingdaylight 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
CREATEVIEWpg_rolesAS
@@ -186,10 +186,11 @@ CREATE RULE pg_settings_n AS
186186

187187
GRANTSELECT,UPDATEON pg_settings TO PUBLIC;
188188

189-
CREATEVIEWpg_timezonenamesAS
190-
SELECT*
191-
FROM pg_timezonenames()AS T
192-
(nametext, utc_offset interval, is_dstboolean);
189+
CREATEVIEWpg_timezone_abbrevsAS
190+
SELECT*FROM pg_timezone_abbrevs();
191+
192+
CREATEVIEWpg_timezone_namesAS
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
Datumresult;
3850-
Interval*resInterval;
38513850
HeapTupletuple;
38523851
Datumvalues[3];
38533852
boolnulls[3];
38543853
charbuffer[TOKMAXLEN+1];
38553854
unsignedchar*p;
38563855
structpg_tmtm;
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 matchthe
3880-
*definition of the pg_timezonenames view in system_views.sql
3879+
* build tupdesc for result tuples. This must matchthis 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+
MemoryContextoldcontext;
3940+
FuncCallContext*funcctx;
3941+
pg_tzenum*tzenum;
3942+
pg_tz*tz;
3943+
Datumresult;
3944+
HeapTupletuple;
3945+
Datumvalues[4];
3946+
boolnulls[4];
3947+
inttzoff;
3948+
structpg_tmtm;
3949+
fsec_tfsec;
3950+
char*tzn;
3951+
Interval*resInterval;
3952+
structpg_tmitm;
3953+
3954+
/* stuff done only on the first call of the function */
3955+
if (SRF_IS_FIRSTCALL())
3956+
{
3957+
TupleDesctupdesc;
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(structpg_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-
#defineCATALOG_VERSION_NO200609141
56+
#defineCATALOG_VERSION_NO200609161
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(insertOID=2511 (pg_cursorPGNSPPGUID12fftts02249""_null__null__null_pg_cursor-_null_ ));
37933793
DESCR("get the open cursors for this session");
3794-
DATA(insertOID=2599 (pg_timezonenamesPGNSPPGUID12fftts02249""_null__null__null_pg_timezonenames-_null_ ));
3794+
DATA(insertOID=2599 (pg_timezone_abbrevsPGNSPPGUID12fftts02249"""{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(insertOID=2856 (pg_timezone_namesPGNSPPGUID12fftts02249"""{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
typedefstructpg_tzpg_tz;
41+
typedefstructpg_tzenumpg_tzenum;
4142

4243
externstructpg_tm*pg_localtime(constpg_time_t*timep,constpg_tz*tz);
4344
externstructpg_tm*pg_gmtime(constpg_time_t*timep);
@@ -56,6 +57,10 @@ extern pg_tz *pg_tzset(const char *tzname);
5657
externbooltz_acceptable(pg_tz*tz);
5758
externconstchar*pg_get_timezone_name(pg_tz*tz);
5859

60+
externpg_tzenum*pg_tzenumerate_start(void);
61+
externpg_tz*pg_tzenumerate_next(pg_tzenum*dir);
62+
externvoidpg_tzenumerate_end(pg_tzenum*dir);
63+
5964
externpg_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 intj2day(int jd);
303303
externboolCheckDateTokenTables(void);
304304
externvoidInstallTimeZoneAbbrevs(tzEntry*abbrevs,intn);
305305

306-
externDatumpg_timezonenames(PG_FUNCTION_ARGS);
306+
externDatumpg_timezone_abbrevs(PG_FUNCTION_ARGS);
307+
externDatumpg_timezone_names(PG_FUNCTION_ARGS);
307308

308309
#endif/* DATETIME_H */

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp