# [SOLVED] In SQL Function I want to return a table or nothing

## Issue

I want to create an sql function that returns either table record or nothing/void

``````CREATE OR REPLACE function testfunc(value bigint)
RETURNS TABLE(severity int, maxvalue bigint) AS \$\$
SELECT CASE
WHEN (value > 30)      THEN (10, 30)
WHEN (value > 60)      THEN (15, 45)
ELSE -- return void or no rows
END
\$\$
language sql stable strict;

SELECT * FROM testfunc(29)
``````

## Solution

Don’t return a CASE expression, use a SELECT with a WHERE condition:

``````CREATE OR REPLACE function testfunc(value bigint)
RETURNS TABLE(severity int, maxvalue bigint)
AS \$\$
SELECT 10,30
where value > 30
union all
select 15, 25
where value > 60;
\$\$
language sql stable strict;
``````

This will return an empty result set if you pass a value that doesn’t meet any of the conditions.

A more flexible way might be to setup a mapping between "value ranges" and the result to be returned. The input value can then be compared to a range of integers

``````CREATE OR REPLACE function testfunc(value bigint)
RETURNS TABLE(severity int, maxvalue bigint)
AS \$\$
with data (severity, maxvalue, value_range) as (
values
(10,30, int8range(30,60,'()') ),
(15,25, int8range(60,null,'()') )
)
SELECT severity, maxvalue
from data
where value_range @> value;
\$\$
language sql
stable strict;
``````

As you have used `>` for your comparison, I defined the ranges as exclusive for both bounds through `'()'` an inclusive lower bound and exclusive upper bound would be `'[)'`. More details in the manual.

You might even think about putting this definition into a table, so you can change it without re-creating the function each time.