Hi, I'm trying to create a canonicalization function for a user-defined range type and running into some issues. I've filled out the template below, and I'm super grateful for your help!
A description of what you are trying to achieve and what results you expect.: I'm trying to create a user-defined range type (specifically, over UUID, but this question isn't that specific) with a canonicalization function, as described in the docs: https://www.postgresql.org/docs/11/sql-createtype.html .Specifically, the docs say that one should create a shell type, then define a canonicalization function, then finally, redefine the range type over the shell type with the newly created canonicalization function. However, I cannot get to that last step as I get an error on the second step, as I can't seem to define a canonicalization function with a shell type. I've included SQL and output/error logs below. I expect this function declaration to work (or for there to be some method of defining it documented) so that I can create a custom range type with a canonicalization function. PostgreSQL version number you are running: - PostgreSQL 11.10 on x86_64-pc-linux-musl, compiled by gcc (Alpine 9.3.0) 9.3.0, 64-bit - PostgreSQL 13.1 (Debian 13.1-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3 .0-6) 8.3.0, 64-bit How you installed PostgreSQL: - docker Changes made to the settings in the postgresql.conf file: see Server Configuration for a quick way to list them all. - None specifically, this happens with 0 explicit configuration Operating system and version: - Arch, up-to-date (shouldn't be relevent) What program you're using to connect to PostgreSQL: - psql Is there anything relevant or unusual in the PostgreSQL server logs?: - same message as described only For questions about any kind of error: What you were doing when the error happened / how to cause the error: SQL: ``` -- create a shell type CREATE TYPE myrange; -- create a canonicalization function with signature described by docs CREATE FUNCTION myrange_canonical(x myrange) RETURNS myrange AS $$ BEGIN return x; END; $$ LANGUAGE plpgsql; ``` This is what I understood from the docs at https://www.postgresql.org/docs/11/rangetypes.html#RANGETYPES-DEFINING The EXACT TEXT of the error message you're getting, if there is one: (Copy and paste the message to the email, do not send a screenshot) NOTICE: argument type myrange is only a shell NOTICE: return type myrange is only a shell ERROR: PL/pgSQL functions cannot return type myrange The two NOTICE lines make sense - but I don't understand the error. When I dig into it, it seems to be due to "psuedotype" handling (and limitations thereof). Am I misunderstanding the docs, or are they out-of-date, or is something else going on? How can I create a canonicalization function for my range type? Thanks!
