Re: ERROR: functions in index expression must be marked IMMUTABLE

"Sven R. Kunze" <srkunze@xxxxxxx> · Wed, 1 Mar 2017 15:23:25 +0100



    On 01.03.2017 14:40, Geoff Winkless
      wrote:

    
        On 1 March 2017 at
            13:36, Sven R. Kunze <srkunze@xxxxxxx> wrote:

        
                  On
                    28.02.2017 17:50, David G. Johnston wrote:
                  
                    
                        Supposedly
                          one could provide a version of to_date that
                          accepts a locale in which to interpret names
                          in the input data - or extend the format
                          string with some kind of "{locale=en_US}"
                          syntax to avoid changing the function
                          signature.
                      
                    
                if this is acceptable, I would be willing to
                contribute a patch to enable exactly this.

              
          I
            don't see how that would help. You can't set a function to
            be immutable for "some" inputs (ie where locale is specified
            in the format string).
          

          The
            only way to do it would be to add to_date(string, string,
            string) where the third string specifies the locale, at
            which point I don't really see why you would gain anything
            over creating your own UDF.
        
      
    I don't consider rolling an UDF the best alternative especially
    after having looked through many solution proposals on the Web which
    just take an mutable _expression_ and wrap them up in an immutable
    function.

    
    An additional variant of to_date/to_timestamp would have the
    following advantages (point 2 is most important):

    
    1) a single, recommended and easy way to parse date(times)

    2) make people aware of the locale/timezone issue but give them a
    standard tool to solve it

    3) make all those and related Google entries
(https://www.google.de/webhp?sourceid=chrome-instant&ion=1&espv=2&ie=UTF-8#q=postgresql+to_date+immutable&*)
    point to the same and safe solution eventually

    
    Sven