Ok, that worked. Such a simple change.
i.e. in the function definition, changed the return statement from
return result::customer_tier;
to
return result::public.customer_tier;
However --
When running the restore as user "root", with the default search path (which was the case): "$user", public
When it finds an unqualified database object referenced, shouldn't it search those schemas in that order, and eventually map the unqualified type "customer_tier" to "public.customer_tier" when it finds it in the "public" schema (after looking and not finding it in the "root" schema)?
Is there an underlying issue with the way the search path is searched? Maybe just for types? Or maybe for types used in a type-cast?
Thanks,
Keith
PG Bug reporting form <noreply@postgresql.org> writes:
> A materialized view runs a select query that uses a function on one of its
> projected columns which internally uses an enum TYPE to map the column value
> to an enum value.
> Action and Resulting Error:
> Trying to restore a database setup in the above way will fail with error:
> ERROR: type "<type_name>" does not exist
If this started happening recently, the problem is likely that the type
is not in the restrictive search_path that dump/restore now uses. You
could possibly fix it just by schema-qualifying the type name in the
function body. However, that may only let you get as far as the next
failure of the same kind. A better fix is to add a "SET search_path"
clause to the function definition so that it works independently of
what the caller's search path is.
regards, tom lane