Forum OpenACS Q&A: Response to Creating aggregate functions in Postgres

Collapse
Posted by Dan Wickstrom on
" not_null_integer_p is a function we created that basically does case when user_id is null then 1 else 0 end, but for some reason PG wouldn't take it in a view. "

I haven't worked with aggregates, but I do know that pg's handling of null in pl/pgsql functions is broken. I believe that this should be fixed in pg 7.1, but you should probably check with the pg developers. The problem is that any time a null is detected in the input arguements, the result is set to null, and the function returns. As a result, it wouldn't work to encapsulate a "case when" statement in a function.