ECPG cursor examples should include EXEC SQL WHENEVER NOT FOUND CONTINUE; after the while loop
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/13/ecpg-variables.html
Description:
Without this line:
EXEC SQL WHENEVER NOT FOUND CONTINUE;
after the while(1), I find that ECPG CURSOR examples fail.
For example, this example under 36.4.4.3.2. Structures fails:
EXEC SQL BEGIN DECLARE SECTION;
typedef struct
{
int oid;
char datname[65];
long long int size;
} dbinfo_t;
dbinfo_t dbval;
EXEC SQL END DECLARE SECTION;
memset(&dbval, 0, sizeof(dbinfo_t));
EXEC SQL DECLARE cur1 CURSOR FOR SELECT oid, datname,
pg_database_size(oid) AS size FROM pg_database;
EXEC SQL OPEN cur1;
/* when end of result set reached, break out of while loop */
EXEC SQL WHENEVER NOT FOUND DO BREAK;
while (1)
{
/* Fetch multiple columns into one structure. */
EXEC SQL FETCH FROM cur1 INTO :dbval;
/* Print members of the structure. */
printf("oid=%d, datname=%s, size=%lld\n", dbval.oid, dbval.datname,
dbval.size);
}
EXEC SQL CLOSE cur1;
Second-granular timezone offset format not documented
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/13/functions-formatting.html Description: I would like to request additional documentation on the timezone format that can be returned. Context: I had a problem with the HDBC-postgresql library, it cannot parse the second-granular timezone offsets returned by PostgreSQL. Here is an example of a format that I don't think the documentation currently covers: janus=> set timezone to 'America/Mexico_City'; SET janus=> select '1920-12-25' :: timestamptz; timestamptz -- 1920-12-25 00:00:00-06:36:36 (1 row) Note how the response has a very weird timezone offset. I guess it is valid, but the documentation should note this as an edge case, since timezone offsets are typically only in minutes. Thank you for your patience.
Re: Second-granular timezone offset format not documented
PG Doc comments form writes: > Here is an example of a format that I don't think the documentation > currently covers: > janus=> set timezone to 'America/Mexico_City'; > SET > janus=> select '1920-12-25' :: timestamptz; > timestamptz > -- > 1920-12-25 00:00:00-06:36:36 > (1 row) Yeah, fair point. There's a passing mention of fractional-minute offsets in Appendix B, but the main docs don't cover it at all. I propose the attached draft patch. regards, tom lane diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index c473d6a746..bd3bf5768c 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -2020,19 +2020,27 @@ MINUTE TO SECOND 04:05:06.789-8 - ISO 8601 + ISO 8601, with time zone as UTC offset 04:05:06-08:00 - ISO 8601 + ISO 8601, with time zone as UTC offset 04:05-08:00 - ISO 8601 + ISO 8601, with time zone as UTC offset 040506-08 - ISO 8601 + ISO 8601, with time zone as UTC offset + + + 040506+0730 + ISO 8601, with fractional-hour time zone as UTC offset + + + 040506+07:30:00 + UTC offset specified to seconds (not allowed in ISO 8601) 04:05:06 PST @@ -2068,17 +2076,21 @@ MINUTE TO SECOND PST8PDT POSIX-style time zone specification + + -8:00:00 + UTC offset for PST + -8:00 - ISO-8601 offset for PST + UTC offset for PST (ISO 8601 extended format) -800 - ISO-8601 offset for PST + UTC offset for PST (ISO 8601 basic format) -8 - ISO-8601 offset for PST + UTC offset for PST (ISO 8601 basic format) zulu @@ -2086,7 +2098,7 @@ MINUTE TO SECOND z - Short form of zulu + Short form of zulu (also in ISO 8601) @@ -2395,6 +2407,24 @@ TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02' + + In the ISO style, the time zone is always shown as + a signed numeric offset from UTC, with positive sign used for zones + east of Greenwich. The offset will be shown + as hh (hours only) if it is an integral + number of hours, else + as hh:mm if it + is an integral number of minutes, else + hh:mm:ss. + (The last case is not possible with any modern time zone standard, + but it can appear when working with timestamps that predate the + introduction of standardized time zones.) + In the other date styles, the time zone is shown as an alphabetic + abbreviation if one is in common use in the current zone; otherwise + it appears as a signed numeric offset in ISO 8601 basic format + (hh or hhmm). + + In the SQL and POSTGRES styles, day appears before month if DMY field ordering has been specified, otherwise month appears
Re: Second-granular timezone offset format not documented
On Mon, Jul 5, 2021 at 9:56 AM PG Doc comments form wrote: > Note how the response has a very weird timezone offset. I guess it is valid, As for whether it's valid, that's coming from the IANA tz dataset. It has a moment that it believes standard time to have begun at each location, in this case: Z America/Mexico_City -6:36:36 - LMT 1922 Ja 1 0:23:24 https://en.wikipedia.org/wiki/Time_in_Mexico#History seems to agree on the year at least. That "local mean time" offset is computed from the location's longitude, for lack of anything better. The tzinfo "Theory" file has a bunch of disclaimers about pre-1970 data though, including "the tz database's LMT offsets should not be considered meaningful".
