> Am 16.05.2018 um 20:40 schrieb Adrian Klaver <adrian.klaver@xxxxxxxxxxx>: > > 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) I have tested it on my data and it works also, but that is a little bit confusing, because imho setof is >= 0 rows and without setof it is [0,1]. On this I know there exist only one or no record, so I choose the solution without setof Thanks for help Phil
