CREATE OR REPLACE FUNCTION substancetrivialname(text) RETURNS substance LANGUAGE 'sql' COST 100 VOLATILE AS $BODY$ select s.* from substancetrivialname n join substance s on s.id = n.idsubstance where lower(btrim(n.name)) = lower(btrim($1)); $BODY$;
substance and substancetrivialname have got a 1-to-N relationship (for each substance can exist multiple trivial names). If I call the function with a non-existing trivial name it returns a single row with all fields are set to NULL.
Ignore my previous post I got turned around on what was being returned.
If I run the join query directly it returns an empty record set on a non-existing trivial name. I expected equal behavior on my function, so my question is, how can I fix this?
