Hello,
We happen to have in our schema the following domain.
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