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

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

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.