Return default rows from a function when first SELECT does not return rows

Question

I have this function http://rextester.com/VIHMIG61446

CREATE OR REPLACE FUNCTION myTestProcedure(namevalue character varying)
 RETURNS TABLE(id integer, name character varying, isdefault boolean)
 LANGUAGE plpgsql
AS $function$
  BEGIN
    IF EXISTS(SELECT
        Domain.id,
        Domain.name,
        Domain.isdefault
      FROM Domain
      where lower(Domain.name) like namevalue)
    THEN
      RETURN QUERY SELECT
        Domain.id,
        Domain.name,
        Domain.isdefault
      FROM Domain
      where lower(Domain.name) like namevalue;
    ELSE
     RETURN QUERY SELECT
        Domain.id,
        Domain.name,
        Domain.isdefault
      FROM Domain
      where Domain.isdefault = true;
    END IF;   
  END
$function$;

and I'm looking a way to not repeat the whole query on the if, so I decided to use with as to store the result but it does not work for me http://rextester.com/MVMVA73088

How should I use with as?

CREATE OR REPLACE FUNCTION myTestProcedure(namevalue character varying)
 RETURNS TABLE(id integer, name character varying, isdefault boolean)
 LANGUAGE plpgsql
AS $function$
  BEGIN
    with temporal_result as (
      SELECT
        Domain.id,
        Domain.name,
        Domain.isdefault
      FROM Domain
      where lower(Domain.name) like namevalue
    )
    IF EXISTS(temporal_result)
    THEN
      RETURN QUERY SELECT * from temporal_result;
    ELSE
     RETURN QUERY SELECT
        Domain.id,
        Domain.name,
        Domain.isdefault
      FROM Domain
      where Domain.isdefault = true;
    END IF;   
  END
$function$;

Answer 1

The if-else logic can be avoided completely. The equivalent result can be written as a single query. The function BOOL_AND is an aggregate function that returns false if any of the values are false, otherwise it returns true.

The following query will work correctly even if multiple rows are matched with the lower(name) like '<namevalue>' condition, or if you have multiple default values.

SELECT subquery.id, subquery.name, subquery.isdefault
    FROM (SELECT d.id,
       d.name,
       d.isdefault,
       BOOL_AND(d.isdefault) OVER () default_and
    FROM domain d
    WHERE lower(d.name) like 'robert' or isdefault) subquery
WHERE isdefault = default_and

As to why you get an error with IF EXISTS(temporal_result), that is not valid sql. It's illegal to do such branching from within an sql statement. What you can do instead is to save the result of the first query into a temporary table, and do the if-else branching referring to the temporary table. A correct version of your stored procedure is below:

CREATE OR REPLACE FUNCTION mytestprocedure(namevalue character varying)
 RETURNS TABLE(id integer, name character varying, isdefault boolean)
 LANGUAGE plpgsql
AS $function$
  BEGIN
    CREATE TEMPORARY TABLE temporal_result as 
      SELECT
        d.id,
        d.name,
        d.isdefault
      FROM domain d
      where lower(d.name) like namevalue
    ;
    IF EXISTS(SELECT TRUE FROM temporal_result) THEN
      RETURN QUERY SELECT * from temporal_result;
    ELSE
      RETURN QUERY SELECT
        d.id,
        d.name,
        d.isdefault
      FROM domain d
      where d.isdefault = true;
      END IF;  
  DROP TABLE temporal_result;
  RETURN;
  END;
$function$;

Note that it is necessary to drop the table at the end of the procedure.

Also note that postgresql ignores upper / lower cases in entity names unless quoted, so it is generally considered poor style to use camel case when naming tables / fields / functions.

Answer 2

I suggest to check the special PL/pgSQL variable FOUND instead:

CREATE OR REPLACE FUNCTION my_test_func(namevalue varchar)
  RETURNS TABLE(id integer, name varchar, isdefault boolean)
  LANGUAGE plpgsql STABLE AS
$func$
BEGIN
   RETURN QUERY 
   SELECT d.id, d.name, d.isdefault
   FROM   domain d
   WHERE  lower(d.name) LIKE namevalue;

   IF NOT FOUND THEN
      RETURN QUERY 
      SELECT d.id, d.name, d.isdefault
      FROM   domain d
      WHERE  d.isdefault;
   END IF;   
END
$func$;

Clean and fast. The first query is simple and as fast as possible. The second query is never executed when the first returns any rows. Related (chapter "Other cases"):

• How to return a value from a function if no value is found

I would probably use d.name ILIKE namevalue and support it with a trigram index. See:

• LOWER LIKE vs iLIKE

A partial index for default rows in the 2nd query might pay, too. If you can get index-only scans out of it, include the (small!) columns you need to retrieve as index columns:

CREATE INDEX domain_defaults_idx ON domain (id, name, isdefault) WHERE isdefault;

Yes, we have to include isdefault, even though logically redundant. Postgres is not currently (pg 14) smart enough to derive the value from the WHERE condition.

If you can't get index-only scans, an index with a constant expression is cheaper:

CREATE INDEX domain_defaults_idx ON domain ((TRUE)) WHERE isdefault;

Related:

• How to index a query with WHERE field IS NULL?