From: | Avery Fischer <biggerfisch(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Cannot create canonicalization function for user-defined range type |
Date: | 2021-01-20 10:58:43 |
Message-ID: | CAOfPeiGnB7Ef1geTKuP7hTmr=+dZs6nLh310NmvgQt+gE-1dnw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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!
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2021-01-20 14:41:58 | Re: Cannot create canonicalization function for user-defined range type |
Previous Message | Atul Kumar | 2021-01-20 09:53:56 | upgrade using logical replication |