On Wed, May 16, 2018 at 2:25 PM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
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
I would start by changing
RETURNS substance
to
RETURNS RECORD
Note: you might also conside using RETURNS TABLE(...)
You might also want to consider adding LIMIT 1 to the end of the query.
--
Melvin Davidson
Maj. Database & Exploration Specialist
Universe Exploration Command – UXC
Employment by invitation only!
Maj. Database & Exploration Specialist
Universe Exploration Command – UXC
Employment by invitation only!
