My function returns a setof
composite type, however the RETURN EXECUTE
seems to return some other unknown type:
create type core.search_result as (
user_id uuid,
country core.country,
ethnicity core.ethnicity,
is_smoker core.smoking_status,
existing_conditions varchar[]
);
create or replace function core.search_samples(
country core.country,
ethnicity core.ethnicity,
is_smoker core.smoking_status,
existing_conditions varchar[]
) returns setof core.search_result as $$
declare
_where text;
_sql text := '
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
core.user
join
core.health_wellness
on
(core.user.id = core.health_wellness.user_id)';
results core.search_result[];
begin
_where := concat_ws(
' AND ',
CASE WHEN country IS NOT NULL THEN 'core.user.country = $1' END,
CASE WHEN ethnicity IS NOT NULL THEN 'core.health_wellness.ethnicity = $2' END,
CASE WHEN is_smoker IS NOT NULL THEN 'core.health_wellness.is_smoker = $3' END
);
raise notice '_where: %', _where;
IF _where <> '' THEN
_sql := _sql || ' WHERE ' || _where;
return query EXECUTE _sql
USING $1, $2, $3;
end if;
raise notice 'all input params null';
end;
$$ language plpgsql stable;
When I try to run the function:
select core.search_samples('United Kingdom', null, null, null);
I get the following error:
NOTICE: _where: core.user.country = $1 ERROR: structure of query does not match function result type DETAIL: Returned type core.search_result does not match expected type uuid in column 1. CONTEXT: PL/pgSQL function core.search_samples(core.country,core.ethnicity,core.smoking_status,character varying[]) line 29 at RETURN QUERY
Best Answer
It's a mismatch of nesting levels.
The return type of the function is declared as:
core.search_result
being the composite type you created earlier. This is equivalent to the more explicit declaration:Then you return values with:
... 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:You need to return 5 separate columns.
Just remove parentheses & cast:
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:
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. OnlyRETURN;
does. Rearrange to something like:Details: