Custom Aggregate Postgresql C extension with input as initial condition

Question

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.

• filiprem: 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 :-)
• ReverseFlowControl: @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".
• user1822: 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
• mustaccio: 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.
• ReverseFlowControl: @a_horse_with_no_name could you give an example in code ?

Answer 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.

• ReverseFlowControl: What value does the state transition function receive after the first call?
• Laurenz Albe: 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.
• ReverseFlowControl: I will try this. Is this behavior documented somewhere in the Postgres Docs?
• Laurenz Albe: 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.