Re: "Type does not exist" error when returning array of type in non-public schema

On Thu, Feb 6, 2025 at 9:47 AM Chris Cleveland <ccleveland(at)dieselpoint(dot)com>
wrote:

> I'm developing a Postgres extension that gets installed in its own schema,
> "rdb". It creates its own type, "token", and has a function that returns
> an array of that type. When I SELECT the function in psql, I get an ERROR:
> type "token" does not exist
>
I'd suggest you create a self-contained example of the behavior without
using an extension.

> It's a search_path problem. If I call SET search_path TO "$user", public,
> rdb; everything works fine.
>
> The trouble is that I can't expect those who install the extension to type
> that command, nor do I wish to ALTER DATABASE SET search_path=... to
> change the global search path, because that might cause problems in an
> unknown environment. (It's already done strange things to my environment).
>
> Oddly, when the function returns just the type itself, not an array of
> them, it works fine. In other words, this works:
>
Yeah, this doesn't make sense, but it points to there probably being an
issue with the function body itself or how you are doing your testing.
This just reinforces the need for a self-contained test case one can
execute in a clean database.

> CREATE FUNCTION my_func ... RETURNS Token ...
>
> but this fails:
>
> CREATE FUNCTION my_func ... RETURNS Token[] ...
>
I'm doubting that the returns clause is the issue since it references
token[] while the error is complaining about token. When the array looks
for its base type it does so via OID, not name, IIRC. Your claim about
"returns token" working notwithstanding.

> No amount of fiddling with the syntax seems to help. RETURN rdb.Token[],
>
If you are using a known fixed schema this is what you should be using as
the returns specification, though you should lowercase the T in Token since
you aren't quoting it.

> RETURN "rdb.Token"[], RETURN "rdb.Token[]" all fail.
>
Expecting these two to work suggests you need to re-read the section on how
quoting identifiers works.

>
> Is there a solution here?
>
Probably add the schema name qualifier to the reference to "token" in your
function body that is actually producing the error.

David J.