Get all rows if keyword is null else return matching

Question

I have a plpgsql function in Postgres. It's working fine when keyword is not null and returning the matching results but when keyword is null I want to ignore it and return arbitrary rows.

CREATE OR REPLACE FUNCTION get_all_companies(_keyword varchar(255))
RETURNS TABLE(
id INTEGER,
name VARCHAR,
isactive boolean
) AS $$

BEGIN
RETURN Query 
SELECT c.id, c.name, c.isactive FROM companydetail AS c
WHERE c.name ~* _keyword LIMIT 50 ;
END;$$
LANGUAGE plpgsql;

yes. I meant if keyword is empty then return records without matching with empty keyword. — Sachin, Apr 10 '18 at 12:59

score 2 · Accepted Answer · answered Apr 10 '18 at 12:08

2

Verify if it's NULL or it's empty:

RETURN QUERY
SELECT c.id, c.name, c.isactive 
FROM companydetail AS c 
WHERE _keyword IS NULL
   OR _keyword = ''::varchar(255) 
   OR c.name ~* _keyword 
LIMIT 50 ;

answered Apr 10 '18 at 12:08

4ndt3s

3,238
2
20
30

Erwin Brandstetter · Answer 2 · 2018-04-11T12:28:13.183

@jahuuar provided a simple and elegant solution with a single SELECT to solve this with a single query (also skipping empty strings if you need that). You don't need plpgsql or even a function for this.

While working with plpgsql, you can optimize performance:

CREATE OR REPLACE FUNCTION get_all_companies(_keyword varchar(255))
  RETURNS TABLE(id INTEGER, name VARCHAR, isactive boolean) AS
$func$
BEGIN
   IF _keyword <> '' THEN  -- exclude null and empty string
      RETURN QUERY 
      SELECT c.id, c.name, c.isactive
      FROM   companydetail AS c
      WHERE  c.name ~* _keyword
      LIMIT  50;
   ELSE
      RETURN QUERY 
      SELECT c.id, c.name, c.isactive
      FROM   companydetail AS c
      LIMIT  50;
   END IF;
END
$func$  LANGUAGE plpgsql;

Postgres can use separate, optimized plans for the two distinct queries this way. A trigram GIN index scan for the first query (you need the matching index, of course - see links below), a sequential scan for the second. And PL/pgSQL saves query plans when executed repeatedly in the same session.

Get all rows if keyword is null else return matching

2 Answers2