Thread: Cannot create canonicalization function for user-defined range type

Cannot create canonicalization function for user-defined range type

From
Avery Fischer
Date:
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!

Re: Cannot create canonicalization function for user-defined range type

From
"David G. Johnston"
Date:
On Wed, Jan 20, 2021 at 5:00 AM Avery Fischer <biggerfisch@gmail.com> wrote: 
ERROR:  PL/pgSQL functions cannot return type myrange

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?

In short, I'm pretty sure shell types can only be referenced in C language functions.  The allowed types listing for pl/pgsql doesn't mention shell types and this error reinforces the fact that the omission is indeed a reflection of the fact that shell types are not allowed.

Thus one needs to write a C language function if one wishes to implement a custom range.

David J.
 

Re: Cannot create canonicalization function for user-defined range type

From
Tom Lane
Date:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Wed, Jan 20, 2021 at 5:00 AM Avery Fischer <biggerfisch@gmail.com> wrote:
>> ERROR:  PL/pgSQL functions cannot return type myrange
>> 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?

> Thus one needs to write a C language function if one wishes to implement a
> custom range.

Yeah, that's the only way at the moment.  We've not seen much use-case
for writing canonicalization functions in PLs, because of (a) performance
and (b) circularity concerns --- the latter meaning that the PL's own
infrastructure is likely to expect that it can manipulate scalar values
without incurring recursion.  These are pretty much the same reasons
why a datatype's I/O functions can't be written in a PL.

            regards, tom lane