Hacker Newsnew | past | comments | ask | show | jobs | submitlogin

The postgres functions should be marked IMMUTABLE.

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.



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


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.


Awesome, added this to the todo list.




Guidelines | FAQ | Lists | API | Security | Legal | Apply to YC | Contact

Search: