Re: stateful UDF?

andrew <andrew(dot)ylzhou(at)gmail(dot)com> writes:
> Within the same query. The function takes a tuple as its input
> parameter. It will be used in the where clause. So I think it will be
> called one time for each read tuple, right? I want to maintain a
> structure to store the information about the tuples that have been
> read so far. The output value of this function is computed based on
> this information and the current input tuple.

This seems unlikely to be a good idea, considering that there's no
guarantee of the tuples being delivered in the same order by every
query. Aren't you setting yourself up for irreproducible results?

Having said that, though, you can certainly do this, and fairly easily
too: you stick a struct containing your state info into the fn_extra
field of the fcinfo you are called with. Look at some of the
set-returning functions (eg, generate_series) for examples. There are
quite a lot of standard functions that use this technique for caching
expensive lookups so they'll be done only once per query, too.

regards, tom lane