Обсуждение: BUG #5740: contrib/spi/moddatetime.c doesn't work with timezones.
The following bug has been logged online: Bug reference: 5740 Logged by: Dirk Heinrichs Email address: dirk.heinrichs@altum.de PostgreSQL version: 8.4.5 Operating system: Linux Description: contrib/spi/moddatetime.c doesn't work with timezones. Details: The moddatetime function provided by this module only works on columns of type "timestamp without time zone". Would be nice if it could also provide an analogous function moddatetime_tz which provides the same functionality for columns of type "timestamp with time zone". Thanks... Dirk
"Dirk Heinrichs" <dirk.heinrichs@altum.de> writes: > The moddatetime function provided by this module only works on columns of > type "timestamp without time zone". Would be nice if it could also provide > an analogous function moddatetime_tz which provides the same functionality > for columns of type "timestamp with time zone". dim=# select pg_typeof('now'::timestamp), pg_typeof('now'::timestamptz); pg_typeof | pg_typeof -----------------------------+-------------------------- timestamp without time zone | timestamp with time zone (1 row) So I guess that you need to modify very little code to get the trigger to work for both types. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
Dirk Heinrichs <dirk.heinrichs@altum.de> writes: > Please find the patch attached. It's against 8.4.5. > > extern Datum moddatetime(PG_FUNCTION_ARGS); > +extern Datum moddatetimetz(PG_FUNCTION_ARGS); [...] > +CREATE OR REPLACE FUNCTION moddatetimetz() > +RETURNS trigger > +AS 'MODULE_PATHNAME' > +LANGUAGE C; You can also do AS 'MODULE_PATHNAME', 'c_function_name' and maintain a single C function that copes with both timestamp and timestamptz arguments, that's what I had in mind: you still declare two functions at the SQL level, though. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
Am 02.11.2010 23:09, schrieb Dimitri Fontaine: > So I guess that you need to modify very little code to get the trigger > to work for both types. Please find the patch attached. It's against 8.4.5. Bye... Dirk
Вложения
Dimitri Fontaine <dimitri@2ndQuadrant.fr> writes: > Dirk Heinrichs <dirk.heinrichs@altum.de> writes: >> Please find the patch attached. It's against 8.4.5. >> >> extern Datum moddatetime(PG_FUNCTION_ARGS); >> +extern Datum moddatetimetz(PG_FUNCTION_ARGS); > [...] >> +CREATE OR REPLACE FUNCTION moddatetimetz() >> +RETURNS trigger >> +AS 'MODULE_PATHNAME' >> +LANGUAGE C; > You can also do AS 'MODULE_PATHNAME', 'c_function_name' and maintain a > single C function that copes with both timestamp and timestamptz > arguments, that's what I had in mind: you still declare two functions at > the SQL level, though. I don't actually see any point in having two functions at all. Since the trigger is examining the column type internally, it could perfectly well do the right thing at runtime depending on column type. regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > I don't actually see any point in having two functions at all. Since > the trigger is examining the column type internally, it could perfectly > well do the right thing at runtime depending on column type. Sorry, brain fart from me. Didn't realise we're talking about a trigger that looks at the column type here, it was a misplaced general advice. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
Am 04.11.2010 04:55, schrieb Tom Lane: > I don't actually see any point in having two functions at all. Since > the trigger is examining the column type internally, it could perfectly > well do the right thing at runtime depending on column type. Got the point. Here's another patch, hope my limited C skills are sufficient... Works here, at least. BTW: Is there a way to achieve the same in pure PL/pgSQL or PL/perl? Bye... Dirk
Вложения
Dirk Heinrichs <dirk.heinrichs@altum.de> writes: > Am 04.11.2010 04:55, schrieb Tom Lane: >> I don't actually see any point in having two functions at all. Since >> the trigger is examining the column type internally, it could perfectly >> well do the right thing at runtime depending on column type. > Got the point. Here's another patch, hope my limited C skills are > sufficient... Works here, at least. Committed with minor cleanup. > BTW: Is there a way to achieve the same in pure PL/pgSQL or PL/perl? plpgsql doesn't have any facility for working with run-time-determined column names. I think it wouldn't be too hard in plperl though. regards, tom lane