I would like a way to run a function on all values returned from a query where the return value of the function is unimportant (even void in this case). A select statement may be the wrong way to do this so an alternative to using SELECT would also work.

This is what I cam up with for the time being which works but feels hacky:

FROM ( SELECT foo.my_function(row_name) AS ignore 
       FROM foo.my_table) AS ig 
WHERE ig.ignore IS NULL;

This gives me back 0 rows as I hoped for (instead of an empty row for every row in foo.my_table that the function was run on). Is there an easier way to achieve this?

  • SELECT SUM(foo.my_function(row_name)) AS ignore FROM foo.my_table will return 1 row. If function return type is numeric, of course. – Akina Jan 31 '19 at 7:07
  • @Akina: using max() would work on all data types – a_horse_with_no_name Jan 31 '19 at 7:14
  • @a_horse_with_no_name MAX() may have sorting overhead. – Akina Jan 31 '19 at 7:23
  • @Akina is there truly no other way to execute a function and not return the result aside from passing it to an aggregate? – Ivan G. Jan 31 '19 at 17:46
  • Anyway you need to call your function for each table record. What difference does it make what to use? Aggregate function overhead is small enough (I think SP+cursor overhead will be greater). The least overhead within aggregate functions list will give COUNT() function (my previous recommendation to use SUM is not optimal). Additional recommendation - define your function as non-deteministic if it will not interfere with another usages of the function. – Akina Jan 31 '19 at 17:52

Your Answer

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

Browse other questions tagged or ask your own question.