Custom domain alias not recognized in context of plpgsql function invocation

[chrisgoddard]

• asyncpg version:
• PostgreSQL version:
• Do you use a PostgreSQL SaaS? If so, which? Can you reproduce the issue with a local PostgreSQL install?:
• Python version:
• Platform:
• Do you use pgbouncer?:
• Did you install asyncpg with pip?:
• If you built asyncpg locally, which version of Cython did you use?:
• Can the issue be reproduced under both asyncio and uvloop?:

version: 0.27.0 postgres: 15.1

Hi there - I have a database that has a domain of ulid which is defined as:

create domain ulid as text
check (value ~ '^[0-9A-Z]{26}$');

From the standpoint of the client this should just be interpreted as text - which is what happens when I perform a select query against a table that has that type.

Where I'm getting an error is I have a custom function called upsert_record with a return type of ulid - the function returns the ulid of the upserted record.

await conn.fetchval("select core.upsert_record(a=>'A', b=>'B')")

I get the error: UndefinedObjectError: type "ulid" does not exist

I've tried setting a type codec (which doesn't appear the correct way for domains based on other issues), and i've tried set_builtin_type_codec( 'ulid', schema='core', codec_name='text') - but that doesn't do anything.

Any suggestions?

[chrisgoddard]

Ok, so I changed the postgres function itself - the first type I just changed the return type to text but I still got the same error.

In the second, I changed the data type of the internal variables (removing all us of the custom domain ulid) and this time it worked - so, at least it's working but I'm curious why different internal types would be causing the query to fail?

[elprans]

The error is coming from Postgres. It's likely you're not careful with qualifying the type with the schema name if you happen to define it outside of public.