0

Is there a way to write a custom aggregate Postgresql C extension function that takes an input from the user and sets it as the initial condition? If so, how?

I’ve written several Postgresql Extensions in the past. The inputs are always columns of the table, but now it is requirement —for theoretical reasons- that I need to make an aggregate extension dependent on one user input that changes a lot. In particular, this values cannot be stored in the database either.

Edit 1: For example, say I want to run the function

aggregate fancy_select( int tmp_accum_value, int column, int *fancy_math_stuff){

return (row *** fancy_math_stuff)    
}

where *** is a fancy math operation that uses the entire fancy_math_stuff array and said array is passed on the first call but it is not stored as column in the database. tmp_accum_value is the intermediary state of the accumulation.

8
  • Of course there is a way (documented here postgresql.org/docs/current/xfunc-c.html) . Please ask a question which cannot be answered with simple yes/no :-)
    – filiprem
    May 3 '20 at 13:31
  • @filiprem: you are not being helpful. For one, every argument of a function is a column of the database, i need an argument that is just data passed in to the function as a normal C-function argument. I do not see anywhere in your link where to achieve that, which is a large document to begin with. And second, an answer with a how-to would be helpful, instead of a "yes". May 3 '20 at 13:36
  • Your answer is pretty unclear. Functions have parameters. How you pass that parameter and where you get the value for that parameter from doesn't matter. You always pass values to it. If that value happens to be a column reference it comes from a column. If that value happens to be a constant, it was supplied by whoever is running the SQL statement calling the function May 3 '20 at 15:00
  • You can pass any expression as a function argument, not necessarily a column. It's not exactly clear what you find difficult to implement. May be you could include a hypothetical example of how you intend to call such a function.
    – mustaccio
    May 3 '20 at 15:01
  • @a_horse_with_no_name could you give an example in code ? May 3 '20 at 19:05
1

If you need to pass an argument to an aggregate function, just do it. That argument does not have to be a table column.

For example:

SELECT myagg(col1, 'initial value') FROM atable;

True, you could also call the function as

SELECT myagg(col1, col2) FROM atable;

but nobody forces you to do that, if you want the second argument to be a constant used for initializing then state.

4
  • What value does the state transition function receive after the first call? May 4 '20 at 8:53
  • It receives whatever you pass. So for a constant, always the same value. But you are free to ignore the argument after the first call. May 4 '20 at 8:59
  • I will try this. Is this behavior documented somewhere in the Postgres Docs? May 4 '20 at 9:00
  • 1
    Yes, in the CREATE AGGREGATE documentation: "PostgreSQL creates a temporary variable of data type stype to hold the current internal state of the aggregate. At each input row, the aggregate argument value(s) are calculated and the state transition function is invoked with the current state value and the new argument value(s) to calculate a new internal state value." So if the new argument value contains your parameter, you can store it in the internal state and use it in the final function. May 4 '20 at 11:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.