Hi all,
Looking to migrate a software product to postgres coming from a home-grown DBMS. Whilst the majority of features in postgres are a gazillion times better than what our own DBMS has, one feature that I find I am missing is the ability to dereference foreign key fields in queries without having to join a bunch of tables together. Let me give you a (quickly contrived) example:
CREATE TABLE "HolidayRegion"
(
"Id" serial NOT NULL PRIMARY KEY,
"Name" text NOT NULL,
UNIQUE ("Name")
);
CREATE TABLE "HolidayGroup"
(
"Id" serial NOT NULL PRIMARY KEY,
"Name" text NOT NULL,
"HolidayRegion" integer NOT NULL references "HolidayRegion",
UNIQUE ("Name")
);
CREATE TABLE "Holidays"
(
"Id" serial NOT NULL PRIMARY KEY,
"Day" smallint NOT NULL,
"Description" text,
"HolidayGroup" integer NOT NULL references "HolidayGroup",
"Month" smallint NOT NULL,
"Year" integer NOT NULL,
UNIQUE ("HolidayGroup", "Year", "Month", "Day")
);
In order to get the data I want in postgres, I need to do this:
select h.Day, h.Description, h.Month, h.Year, g.Name, r.Name from Holidays h, HolidayGroup g, HolidayRegion r where h.HolidayGroup = g.Id and g.HolidayRegion = r.Id
In our DBMS, we can do this:
select Day, Description, Month, Year, HolidayGroup:Name, HolidayGroup:HolidayRegion:Name from Holidays;
I'm guessing this syntax does not conform to any SQL standard and was simply implemented by our devs as a bit of a shortcut, but it is incredibly useful and can make queries much shorter and easier to understand when getting data from multiple tables that have foreign key relations. Is the sort of thing that would even be entertained as a possibility to be added to postgres, or would I need to change the source/roll my own version to provide this? I can't find any information on the postgres website about a way to submit feature requests/enhancements, only to report bugs. Is there a formal mechanism to request new functionality?
Cheers,
Andrew