Re: Fwd: Enhancement to SQL query capabilities

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