Postgres Pro
Downloads
Home   >   mailing lists

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

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

From
Chris Cleveland
Date:

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.

This problem is happening in pg17. Haven't tried other versions.

Is there a solution here?


--
Chris Cleveland
312-339-2677 mobile

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

From
"David G. Johnston"
Date:
On Thu, Feb 6, 2025 at 9:47 AM Chris Cleveland <ccleveland@dieselpoint.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.

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

From
Chapman Flack
Date:
Hi,

On 02/06/25 11:46, Chris Cleveland 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
> ...
> this fails:
> 
> CREATE FUNCTION my_func ... RETURNS Token[] ...

If I'm reading your description right, are you saying rather that the
CREATE FUNCTION (within the extension script) does not fail, but that
the failure is seen later when the function is used in a query?

Is the function implemented in LANGUAGE sql or in some other procedural
language?

If it is in LANGUAGE sql, is it written in the SQL standard form with
BEGIN ATOMIC, or in the PostgreSQL form where the function body is all
in a string literal?

In the SQL standard form, it would have the token type resolved at
function creation time, and not need to rely on the search path at
time of use.

In the string literal form, there could be a use of the type within
the body of your function that is not qualified by its schema name.
That's what would need to be fixed, not the RETURNS declaration of
CREATE FUNCTION.

You could change Token[] to rdb.Token[] wherever it appears in the
string literal, or add a SET search_path clause to the CREATE FUNCTION
itself, ensuring that the needed schema is on the path.

Regards,
-Chap

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

From
Chris Cleveland
Date:
I solved the problem with:

CREATE FUNCTION myfunc ... RETURNS Token[] ... SET search_path to rdb;

I still don't know why it happens in the first place, though.

On Thu, Feb 6, 2025 at 12:14 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Thu, Feb 6, 2025 at 9:47 AM Chris Cleveland <ccleveland@dieselpoint.com>
> wrote:
>> 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.

I'm wondering if it needs to be "Token".  Mixed-case SQL identifiers
are usually more of a pain than they're worth, frankly.

                        regards, tom lane


--
Chris Cleveland
312-339-2677 mobile

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

From
Chapman Flack
Date:
On 02/06/25 16:06, Chris Cleveland wrote:
> I solved the problem with:
> 
> CREATE FUNCTION myfunc ... RETURNS Token[] ... SET search_path to rdb;
> 
> I still don't know why it happens in the first place, though.

That pretty much nails down that the type name appears somewhere
in the quoted-literal function body. But you haven't spotted where?

You still haven't mentioned which PL the function is written in.

I wonder if, for some PL, there could be a generated preamble/postamble
around your function body that you don't see, that contains the name
of the return type.

If that were to turn out to be the source of the issue, it would suggest
that the generated code missed a place where the fully-qualified name
should be used.

Regards,
-Chap

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

From
"David G. Johnston"
Date:
On Thursday, February 6, 2025, Chris Cleveland <ccleveland@dieselpoint.com> wrote:
I solved the problem with:

CREATE FUNCTION myfunc ... RETURNS Token[] ... SET search_path to rdb;

I still don't know why it happens in the first place, though.

Because you are in the habit of not schema-qualifying object references and thus choose to rely on search_path.  Your choice, the SET clause is here for you.

David J.