Thread: stateful UDF?
Hi Can I implement a stateful UDF in C? i.e. storing the state of the previous run of the function and access them in the succeeding runs. Is it supported? How do I implement it? Thanks. -- andrew
On Tue, Feb 14, 2006 at 11:47:49PM +0100, andrew wrote: > Can I implement a stateful UDF in C? i.e. storing the state of the > previous run of the function and access them in the succeeding runs. > Is it supported? How do I implement it? Thanks. Do you need to maintain state across sessions or just within a single session? What are you trying to do? -- Michael Fuhr
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. On 2/15/06, Michael Fuhr <mike@fuhr.org> wrote: > On Tue, Feb 14, 2006 at 11:47:49PM +0100, andrew wrote: > > Can I implement a stateful UDF in C? i.e. storing the state of the > > previous run of the function and access them in the succeeding runs. > > Is it supported? How do I implement it? Thanks. > > Do you need to maintain state across sessions or just within a > single session? What are you trying to do? > > -- > Michael Fuhr > -- andrew
On Wed, Feb 15, 2006 at 10:49:14AM +0100, andrew wrote: > 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. Sounds like you're referring to an aggregate which takes a number of input values and returns a single output value. There you define a state structure to deal with this. http://www.postgresql.org/docs/8.1/interactive/sql-createaggregate.html Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
Thanks. But it is a little different. What I want is one output value of every input tuple. But the output value is not only based on the current input value but also some value that have been read before. On 2/15/06, Martijn van Oosterhout <kleptog@svana.org> wrote: > On Wed, Feb 15, 2006 at 10:49:14AM +0100, andrew wrote: > > 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. > > Sounds like you're referring to an aggregate which takes a number of > input values and returns a single output value. There you define a > state structure to deal with this. > > http://www.postgresql.org/docs/8.1/interactive/sql-createaggregate.html > > Hope this helps, > -- > Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > > tool for doing 5% of the work and then sitting around waiting for someone > > else to do the other 95% so you can sue them. > > > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.4.1 (GNU/Linux) > > iD8DBQFD8vsEIB7bNG8LQkwRAkqEAJ97v7v9cUMWPXMcrc13ANVmLNfuYACggxty > nRPGHR4KEPm0RUiwqGIdI7k= > =nGrX > -----END PGP SIGNATURE----- > > > -- andrew
andrew <andrew.ylzhou@gmail.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