Re: sql function with empty row

On Wed, May 16, 2018 at 2:25 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
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(dot)klaver(at)aklaver(dot)com
>
>
*I would start by changing*
* RETURNS substance*
*to*
*RETURNS RECORD*

*Note: you might also conside using RETURNS TABLE(...)*

*https://www.postgresql.org/docs/10/static/sql-createfunction.html
<https://www.postgresql.org/docs/10/static/sql-createfunction.html>*

*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!