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

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:

CREATE FUNCTION my_func ... RETURNS Token ...

but this fails:

CREATE FUNCTION my_func ... RETURNS Token[] ...

No amount of fiddling with the syntax seems to help. RETURN rdb.Token[], RETURN "rdb.Token"[], RETURN "rdb.Token[]" all fail.

Is there a solution here?

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

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:

CREATE FUNCTION my_func ... RETURNS Token ...

but this fails:

CREATE FUNCTION my_func ... RETURNS Token[] ...

No amount of fiddling with the syntax seems to help. RETURN rdb.Token[], RETURN "rdb.Token"[], RETURN "rdb.Token[]" all fail.

Is there a solution here?

• postgresql

• 1 Do not write your extension to be installed into a specific schema. Postgres allows schema specification with the create extension command. Whoever installs it should be aware of the requirements and have authority satisfy them. IMHO requiring a specific schema for your extension is bad practice. Consider the following: What happens if that schema already exists and access to it is strictly limited to superusers? What happens if during installation a different schema is specified? – Belayer Commented Feb 6 at 19:30

1 Answer 1

Following up on the comment from @Belayer, quoting 36.17. Packaging Related Objects into an Extension:

Another important point is that schemas can belong to extensions, but not vice versa: an extension as such has an unqualified name and does not exist “within” any schema. The extension's member objects, however, will belong to schemas whenever appropriate for their object types. It may or may not be appropriate for an extension to own the schema(s) its member objects are within.