Philipp Kraus wrote: > I have defined a SQL function > > 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. > > 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? The difference is that the function has to return exactly one value, while the query it at liberty to return 0, 1 or more rows. Since there is no result, it returns a NULL value. What you are seeing is a valid composite NULL value: SELECT ROW(NULL, NULL) IS NULL; ?column? ---------- t (1 row) It looks weird, but the SQL standard wants it that way. NULLs and composite types is a topic that can really twist your brain. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
