On Wed, May 16, 2018 at 2:25 PM, Adrian Klaver <adrian.kla...@aklaver.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.kla...@aklaver.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!
