Enhancement to SQL query capabilities

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

Responses

Browse pgsql-sql by date

  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