Re: Enhancement to SQL query capabilities

On Thursday, March 17, 2016, Andrew Smith <laconical(at)gmail(dot)com> wrote:

> 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
>

I suggest learning ANSI join syntax.

FROM holiday h
JOIN holidaygroup g on (h.id = g.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?
>

Even if this is something that would be committed (I have my doubts and
generally don't think it should be) you'd still end up writing it because I
am near certain no one else would be so inclined.

Given you are not adding any new capabilities and making significant
changes to parsing code, the barrier for entry is very high. If the syntax
was standard then work on using meta-data to auto-resolve the joins might
be worthy of inclusion.

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?
>
>
Most requests that are not bugs should go to -general unless one of the
other lists seems more appropriate. Discussions about patches occur on
-hackers.

David J.