Re: sql function with empty row

Adrian Klaver Wed, 16 May 2018 11:41:14 -0700

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.


Well I was on the right track for the wrong reason. At any rate SETOF works:

select * from cell_per where cell_per = 100;

---------+----------+----------+-----------+-----------+-------------+-------------+------------+--------+----------------
(0 rows)

CREATE OR REPLACE FUNCTION cp(integer)
    RETURNS cell_per
    LANGUAGE 'sql'
AS $BODY$
select cell_per.* from cell_per where cell_per = $1;
$BODY$;


select * from cp(100);

---------+----------+----------+-----------+-----------+-------------+-------------+------------+--------+----------------

(1 row)


CREATE OR REPLACE FUNCTION cp(integer)
    RETURNS SETOF cell_per
    LANGUAGE 'sql'
AS $BODY$
select cell_per.* from cell_per where cell_per = $1;
$BODY$;


select * from cp(100);

---------+----------+----------+-----------+-----------+-------------+-------------+------------+--------+----------------
(0 rows)

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

Re: sql function with empty row

Reply via email to