Say you have a table cat_dogs:
| no_cats | no_dogs | |
|---|---|---|
| 1 | 10 | 0 |
| 2 | 5 | 6 |
| 3 | 1 | 2 |
SELECT max_of(Array[no_cats,no_dogs], Array["cats","dogs"]) AS dominant_animal, *
FROM cat_dogs
the function will return
| no_cats | no_dogs | dominant_animal | |
|---|---|---|---|
| 1 | 10 | 0 | cats |
| 2 | 5 | 6 | dogs |
| 3 | 1 | 2 | dogs |
| select max_of(Array[1,2,3],Array['one','two','three']) |
| create or replace function max_of(vals numeric[], names text[]) returns text as $$ | |
| DECLARE | |
| max_val numeric; | |
| BEGIN | |
| max_val = (select max(a) from unnest(vals) a); | |
| for i in 0..array_length(names,1) loop | |
| if vals[i] = max_val then | |
| return names[i]; | |
| end if; | |
| end loop; | |
| END | |
| $$ | |
| LANGUAGE plpgsql |