[Solved] Help create function in postgresql

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
myleow
Forum Contributor
Posts: 194
Joined: Mon Jun 21, 2004 7:05 pm
Location: California

[Solved] Help create function in postgresql

Post 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
Last edited by myleow on Wed Sep 08, 2004 6:18 pm, edited 1 time in total.
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post 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.
myleow
Forum Contributor
Posts: 194
Joined: Mon Jun 21, 2004 7:05 pm
Location: California

Thanks

Post 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
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post 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.
Post Reply