Hello,
We happen to have in our schema the following domain.
CREATE DOMAIN public.name varchar(50) NOT NULL;
This was done before me. We assumed this was used in many tables in our
app. Then I wrote a function with a return clause like the following:
RETURNS (
id INT,
name name,
other_names name ARRAY
)
This worked fine until CI tried to build this on a postgres 9.3 box (we are
mostly 9.1, but are fixing that). Then it failed.
So then I discovered that there is a built in type called pg_catalog.name
as well as my public.name. Followed by the discovery that you can't have
arrays of domains. This query showed two rows
SELECT typnamespace, typname
FROM pg_catalog.pg_type typ
WHERE typname = 'name';
Then there was wailing and gnashing of teeth, and I made everything
explicitly varchar, and everything was all good, except I have to fix unit
tests. Oh and nothing is actually using our domain, as demonstrated by this
query:
SELECT attrelid::regclass AS table_name,
attname, atttypid::REGTYPE
FROM pg_catalog.pg_attribute
WHERE atttypid::REGTYPE IN ('name', 'public.name')
ORDER BY atttypid DESC, attrelid::regclass
Based on this, and some consultations with friends who know more about
postgres than I, I'd like to propose that domains not be allowed to be the
same name as built in types or at the very least give a warning. The fact
that I have to quote keywords, but not even need to quote built in types is
bothersome. Here are examples of queries and behaviors I expect
CREATE DOMAIN "INTO" char(5); -- Does work. Should work without a warning.
The error you get for doing it unquoted is sufficient IMHO
CREATE DOMAIN int CHAR(50); -- Does work. Id prefer it not to work.
Alternatively it could work but emit a warning.
CREATE DOMAIN public.int CHAR(50); -- Does work. I could see the argument
for it working, but would prefer it didn't work. Should still emit a
warning its overriding a base
Since I'm returning to postgres after close to a decade, I figured I'd ask
here for feedback before posting to the hackers list.
Regards,
Justin Dearing
