Postgres Pro
Downloads
Home   >   mailing lists

Thread: sql function with empty row

sql function with empty row

From
Philipp Kraus
Date:
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.

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

Re: sql function with empty row

From
Adrian Klaver
Date:
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.

Since there can be many trivial names per substance shouldn't you be 
using SETOF?:

https://www.postgresql.org/docs/10/static/xfunc-sql.html#XFUNC-SQL-FUNCTIONS-RETURNING-SET

> 
> 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

Re: sql function with empty row

From
Adrian Klaver
Date:
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.

Ignore my previous post I got turned around on what was being returned.

> 
> 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

Re: sql function with empty row

From
Melvin Davidson
Date:


On Wed, May 16, 2018 at 2:25 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
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.

 Ignore my previous post I got turned around on what was being returned.



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


I would start by changing
 RETURNS substance
to
RETURNS RECORD

Note: you might also conside using RETURNS TABLE(...)
https://www.postgresql.org/docs/10/static/sql-createfunction.html

You might also want to consider adding LIMIT 1 to the end of the query.

--
Melvin Davidson
Maj. Database & Exploration Specialist
Universe Exploration Command – UXC
Employment by invitation only!

Re: sql function with empty row

From
Adrian Klaver
Date:
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

Re: sql function with empty row

From
Philipp Kraus
Date:
> 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

Re: sql function with empty row

From
Adrian Klaver
Date:
On 05/16/2018 11:49 AM, Philipp Kraus wrote:
> 
>> Am 16.05.2018 um 20:40 schrieb Adrian Klaver <adrian.klaver@aklaver.com>:
>>

> 
> 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

I gotta believe the difference is:

RETURNS substance
https://www.postgresql.org/docs/10/static/xfunc-sql.html#XFUNC-SQL-TABLE-FUNCTIONS
"
If the function is defined to return a composite type, the table 
function produces a column for each attribute of the composite type."

RETURNS SETOF substance

https://www.postgresql.org/docs/10/static/xfunc-sql.html#XFUNC-SQL-FUNCTIONS-RETURNING-SET

"When an SQL function is declared as returning SETOF sometype, the 
function's final query is executed to completion, and each row it 
outputs is returned as an element of the result set."


> 
> Thanks for help
> 
> Phil
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: sql function with empty row

From
"David G. Johnston"
Date:
On Wed, May 16, 2018 at 11:49 AM, Philipp Kraus <philipp.kraus@tu-clausthal.de> wrote:
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].

​Without setof it will always return exactly 1 row, never 0.  This is a feature as returning zero rows is problematic in many ways; a 0-row result in a select-list will cause that row to be removed from the result when is most cases people would rather be told whatever they were trying to compute for the row simply didn't compute - via a NULL.

David J.

Re: sql function with empty row

From
Laurenz Albe
Date:
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