4 ms·

The postgres functions should be marked IMMUTABLE. http://www.postgresql.org/docs/9.2/static/xfunc-volatility.html http://www.postgresql.org/docs/9.2/static/xf

The postgres functions should be marked IMMUTABLE. http://www.postgresql.org/docs/9.2/static/xfunc-volatility.html http://www.postgresql.org/docs/9.2/static/xfunc-volatility.h... This allows the planner to optimize queries that use the functions correctly, instead of treating the functions like a black box. Edit: STABLE would indeed be better, forgot about timezones.

wulczer 13y ago
Not really, the ones calling down to date_trunc should be marked as STABLE, since the underlying function is STABLE. psql=$ \df+ date_trunc Schema | Name | Result data type | Argument data types | Type | Volatility | Owner | Language | Source code | Description ------------+------------+-----------------------------+-----------------------------------+--------+------------+----------+----------+-------------------+------------------------------------------------------ pg_catalog | date_trunc | interval | text, interval | normal | immutable | postgres | internal | interval_trunc | truncate interval to specified units pg_catalog | date_trunc | timestamp without time zone | text, timestamp without time zone | normal | immutable | postgres | internal | timestamp_trunc | truncate timestamp to specified units pg_catalog | date_trunc | timestamp with time zone | text, timestamp with time zone | normal | stable | postgres | internal | timestamptz_trunc | truncate timestamp with time zone to specified units
- jeltz 13y ago
  No, the code casts to "timestamp without time zone" before running date_trunc so it is immutable. For example: CREATE OR REPLACE FUNCTION gd_day(timestamptz, text) RETURNS timestamptz AS $$ SELECT DATE_TRUNC('day', $1 AT TIME ZONE $2) AT TIME ZONE $2; $$ LANGUAGE SQL; EDIT: The reason that some time functions in PostgreSQL are not immutable is that they are affected by the current time zone setting of the session.
akane 13y ago
Awesome, added this to the todo list.

wulczer 13y ago

jeltz 13y ago
No, the code casts to "timestamp without time zone" before running date_trunc so it is immutable. For example: CREATE OR REPLACE FUNCTION gd_day(timestamptz, text) RETURNS timestamptz AS $$ SELECT DATE_TRUNC('day', $1 AT TIME ZONE $2) AT TIME ZONE $2; $$ LANGUAGE SQL; EDIT: The reason that some time functions in PostgreSQL are not immutable is that they are affected by the current time zone setting of the session.

akane 13y ago

Awesome, added this to the todo list.