I'm using postgREST to generate a http accessible api (great stuff). There is a little bug that I'm trying to work around.
For whatever reason, when calling a function, and only in the function parameters, I cannot use the types the api can normally reference using qualified notation e.g., core.my_type. It can however access api.my_type where of course I don't need to qualify the type.
So, to be clear, it all works in postgres. It's just a quirk with the postgREST.
One work-around that is prone to error over time, is to copy/paste the definition of each type in each of the schemas.
The other is the topic of this post: is there a way to automatically create a copy of the type in the core schema to one in the api? Or, is there a way to reference the core.my_type using an alias? (not sure if the latter would solve the problem, but perhaps worth a try).
I realize it would require casting where required. However, it does solve the problem of tracking the entries in each of the enums (in this case).
For whatever reason, when calling a function, and only in the function parameters, I cannot use the types the api can normally reference using qualified notation
That is because PostgREST uses a CTE when building the query to call the function and casts the data to the types of the parameters as seen in these lines of code.
There is a closed issue in the GitHub repository mentioning this problem that is labeled as won't fix.
The other is the topic of this post: is there a way to automatically create a copy of the type in the core schema to one in the api? Or, is there a way to reference the core.my_type using an alias?
You could create a DOMAIN as a workaround. That way, any modification you do using ALTER on the underlying private data type will be reflected on the domain. For instance:
create schema api;
create schema private;
create role web_anon nologin;
-- web_anon doesn't have usage on the private schema
grant usage on schema api to web_anon;
-- create type and domain
create type private.grade as enum('a','b','c','d','e');
create domain api.grade as private.grade;
-- The function uses the domain api.grade instead of private.grade
create or replace function
api.get_grade(g api.grade) returns text as $$
begin
return (select g);
end;
$$ language plpgsql;
-- This change will reflect on the domain
alter type private.grade add value 'f';