Re: sql function with empty row

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;
  line_id | category | cell_per | ts_insert | ts_update | user_insert | 
user_update | plant_type | season | short_category

---------+----------+----------+-----------+-----------+-------------+-------------+------------+--------+----------------
(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);
  line_id | category | cell_per | ts_insert | ts_update | user_insert | 
user_update | plant_type | season | short_category

---------+----------+----------+-----------+-----------+-------------+-------------+------------+--------+----------------
     NULL | NULL     |     NULL | NULL      | NULL      | NULL        | 
NULL        | NULL       | NULL   | NULL
(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);
line_id | category | cell_per | ts_insert | ts_update | user_insert | 
user_update | plant_type | season | short_category

---------+----------+----------+-----------+-----------+-------------+-------------+------------+--------+----------------
(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.klaver@aklaver.com