Page 1 of 1
[Solved] Help create function in postgresql
Posted: Tue Sep 07, 2004 11:54 pm
by myleow
Code: Select all
CREATE FUNCTION max_cell_seq_nbr(integer) RETURNS integer AS 'SELECT max(nbr) FROM user_list WHERE user= $1;' LANGUAGE SQL;
I am getting an error from this when the table is empty, i wanted it to return 0 but it is returning a NULL.
Can anyone help me on this, please.
Regards
Mian
Posted: Wed Sep 08, 2004 3:41 am
by CoderGoblin
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.
Thanks
Posted: Wed Sep 08, 2004 6:18 pm
by myleow
Thank you for the answer, i actually found out how to do it yesterday night. I do agree it is quite difficult to find, took me a while to get it working too.
Really appreciate the help though, normally its quite difficult to get help with PostgreSQL.
Regards
Mian
Posted: Thu Sep 09, 2004 3:05 am
by CoderGoblin

I know what you mean, Never really used MySQL though, so all my responses to topics tend to have POSTGRES database connections in.