Обсуждение: 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