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.

Improve this question
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
    Commented May 3, 2020 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".
    – ReverseFlowControl
    Commented May 3, 2020 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
    – user1822
    Commented May 3, 2020 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
    Commented May 3, 2020 at 15:01
  • @a_horse_with_no_name could you give an example in code ?
    – ReverseFlowControl
    Commented May 3, 2020 at 19:05

1 Answer 1

Reset to default
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.

Improve this answer
4
  • What value does the state transition function receive after the first call?
    – ReverseFlowControl
    Commented May 4, 2020 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.
    – Laurenz Albe
    Commented May 4, 2020 at 8:59
  • I will try this. Is this behavior documented somewhere in the Postgres Docs?
    – ReverseFlowControl
    Commented May 4, 2020 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.
    – Laurenz Albe
    Commented May 4, 2020 at 11:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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