Re: sql function with empty row

From: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
To: Philipp Kraus <philipp(dot)kraus(at)tu-clausthal(dot)de>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: sql function with empty row
Date: 2018-05-16 18:25:09
Message-ID: 50245dd7-b496-7be1-4ed3-f434fe77fb23@aklaver.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Melvin Davidson 2018-05-16 18:32:05 Re: sql function with empty row
Previous Message Adrian Klaver 2018-05-16 18:21:46 Re: sql function with empty row