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.

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

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

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
<https://www.postgresql.org/docs/10/static/sql-createfunction.html&gt;*

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

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

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

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

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.

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