On Jan 17, 2005, at 0:25, Bo Lorentsen wrote:
Tom Lane wrote:
Ok, thanks I see why there is these three differant function types, but I don't quite understand why the value from a volatile function, can't be used as a index key. Is this because there is no return type garanti, for the voilatile function too ?http://developer.postgresql.org/docs/postgres/xfunc-volatility.html
I don't believe it has necessarily anything to do with the return type, but rather the return value. An index only works if you know what the value is, and the return value for a volatile function is not guaranteed to be the same for given parameters. Here's a contrived (and untestsd) example, but one I think makes it clear:
CREATE FUNCTION plus_random ( INTEGER ) RETURNS INTEGER LANGUAGE SQL AS ' SELECT round( $1 + random() * 100 ); ';
One could conceivably attempt to make a functional index using plus_random(), but the result it gives every time is indeterminant. How would you be able to usefully search for values in an index that is based on this function? Would it make sense do to do so?
Does this help? (And if I'm completely off base, someone please let me know :)
Michael Glaesemann grzm myrealbox com
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
