From: | Andrew Smith <laconical(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Enhancement to SQL query capabilities |
Date: | 2016-03-18 05:04:55 |
Message-ID: | CALr=acHJbn1kiohs8upmSxN+JK5OWQ5=3LK5wp2PzGyiRK4oSA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2016-03-18 05:54:55 | Re: Enhancement to SQL query capabilities |
Previous Message | Andreas Kretschmer | 2016-03-16 16:08:04 | Re: delete taking long time |