Search Postgresql Archives

Re: sql function with empty row

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxx




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux