I have come across a similar problem with creating views. To solve I used the COALESCE function which is a pain to find in the documentation. You may also wish to look up CASE in the documentation as coalesce gets converted into a case when creating a view.
Basic overview of COALESCE is that it returns the first element which is not null in the list so
will return field1 if it is not null, field2 if field1 is null and 0 if both fields are null. (Number of field columns variable).
Using your example we get
Code: Select all
CREATE FUNCTION max_cell_seq_nbr(integer)
RETURNS integer AS 'SELECT COALESCE(max(nbr),0) FROM user_list WHERE user= $1;' LANGUAGE SQL;
Easy if you know it but as mentioned a bugger to find.