Обсуждение: Enhancement to SQL query capabilities

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?

Hi Andrew,

what about NATURAL JOIN? I think that's the shortest possible way to write
joined SQL. It needs you to use same column names across joined tables on
which you want to join, but it should not be a problem.

See http://www.postgresql.org/docs/9.5/static/sql-select.html


CREATE TABLE holiday_region
(region_id serial NOT NULL PRIMARY KEY,region_name text NOT NULL UNIQUE
);

CREATE TABLE holiday_group
(group_id serial NOT NULL PRIMARY KEY,group_name text NOT NULL UNIQUE,       region_id integer NOT NULL references
holiday_region(region_id)
);

CREATE TABLE holidays
(holidays_id serial NOT NULL PRIMARY KEY,holidays_description text,group_id integer NOT NULL references
holiday_group(group_id),holidays_daysmallint NOT NULL,holidays_month smallint NOT NULL,holidays_year integer NOT
NULL,UNIQUE(group_id, holidays_year, holidays_month, holidays_day) 
);

Then you write

SELECT *
FROM holidays
NATURAL JOIN holiday_group
NATURAL JOIN holiday_region


Regards, Martin

> Apologies, forgot to reply all:
>
>
>         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 <http://h.id> = g.id <http://g.id>)
>
>
>
> Any particular reason why? This creates a longer query string than the one
> I listed and they both give the same result:
>
> select h.Day, h.Description, h.Month, h.Year, g.Name, r.Name from Holidays
> h join HolidayGroup g on h.HolidayGroup = g.id <http://g.id> join
> HolidayRegion r on g.HolidayRegion = r.id <http://r.id>
>
> Does using a join make the query execute faster, or is it just a 'more
> accepted' query standard? I've been so used to our custom syntax that I
> haven't written a 'real' SQL query in more than a decade.
>
>
>
>      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 would have thought that being able to reduce the complexity of long SQL
> statements would be seen as a new capability, but if it isn't desirable
> then I'll download the code and start looking into it. At the very least
> I'll probably have to provide a homebrew alternative to psql as we've got
> heaps of scripts written in this syntax which will take a very long time to
> port across to standard SQL.
>
>
>
>          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.
>
>
> Thanks, will keep that in mind for my next dumb idea :-)
>
>

Andrew Smith <laconical@gmail.com> writes:
> 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.

Aside from the other solutions that have been mentioned, have you
considered querying a view that contains the desired join?

There's no substitute for fixing that nonstandard syntax, in any case.
Even if you could persuade pgsql-hackers that the concept was good
(which I really doubt), they'd not go for using : as a selector.
        regards, tom lane