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

Code: Select all

SELECT COALESCE(field1,field2,0);
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
:lol: I know what you mean, Never really used MySQL though, so all my responses to topics tend to have POSTGRES database connections in.