On 05/16/2018 11:07 AM, Philipp Kraus wrote:
> Hello,
>
> 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.
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?
>
> Thanks
>
> Phil
>
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com