PostgreSQL - Return type and EXECUTE QUERY
It's a mismatch of nesting levels.

The return type of the function is declared as:

returns setof core.search_result


core.search_result being the composite type you created earlier. This is equivalent to the more explicit declaration:

RETURNS TABLE (
  user_id uuid,
  country core.country,
  ethnicity core.ethnicity,
  is_smoker core.smoking_status,
  existing_conditions varchar[]
)


Then you return values with:

select (
       core.user.id,
       core.user.country,
       core.health_wellness.ethnicity,
       core.health_wellness.is_smoker,
       core.health_wellness.existing_conditions
     )::core.search_result
     from ...


... which would seem to make sense on a first glance. But the expression is exactly 1 column of type core.search_result, which Postgres tries to fit into the first column of the return type (user_id uuid). Hence the DETAIL information in your error message:


Returned type core.search_result does not match expected type uuid in column 1



You need to return 5 separate columns.

Just remove parentheses &amp; cast:

select core.user.id,
       core.user.country,
       core.health_wellness.ethnicity,
       core.health_wellness.is_smoker,
       core.health_wellness.existing_conditions
     from ...


You may have to add casts to individual columns if data types don't match. One way to avoid that would be to cast the whole ROW type and decompose to separate columns again:

select ((
       core.user.id,
       core.user.country,
       core.health_wellness.ethnicity,
       core.health_wellness.is_smoker,
       core.health_wellness.existing_conditions
     )::core.search_result).*
     from ...


But I'd rather avoid that complication.



Aside:
Once you have fixed the exception, raise notice at the very end of your function is reached unconditionally and incorrect. RETURN QUERY ... does not end a function. Only RETURN; does. Rearrange to something like:

IF _where &lt;&gt; '' THEN
  _sql := _sql || ' WHERE ' || _where;
  return query EXECUTE _sql
  USING $1, $2, $3;
ELSE                                     -- !!    
  raise notice 'all input params null';  -- !!
END IF;


Details:


PostgreSQL stored procedure to return rows or empty set