Re: sql function with empty row

Philipp Kraus wrote:
> 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.
>
> 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?

The difference is that the function has to return exactly one value,
while the query it at liberty to return 0, 1 or more rows.

Since there is no result, it returns a NULL value.
What you are seeing is a valid composite NULL value:

SELECT ROW(NULL, NULL) IS NULL;

?column?
----------
t
(1 row)

It looks weird, but the SQL standard wants it that way.
NULLs and composite types is a topic that can really twist your brain.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com