Re: sql function with empty row - Mailing list pgsql-general

From Philipp Kraus
Subject Re: sql function with empty row
Date
Msg-id 72B01876-DA02-4306-9994-F7F1F12A7C29@tu-clausthal.de
Whole thread Raw
In response to Re: sql function with empty row  (Adrian Klaver <adrian.klaver@aklaver.com>)
Responses Re: sql function with empty row  (Adrian Klaver <adrian.klaver@aklaver.com>)
Re: sql function with empty row  ("David G. Johnston" <david.g.johnston@gmail.com>)
List pgsql-general
> Am 16.05.2018 um 20:40 schrieb Adrian Klaver <adrian.klaver@aklaver.com>:
>
> 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

pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: sql function with empty row
Next
From: Adrian Klaver
Date:
Subject: Re: sql function with empty row