In SQL Function I want to return a table or nothing-CodePudding

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)

CodePudding user response：

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.