Re: Fwd: Enhancement to SQL query capabilities

agora inbox for pgsql-sql@postgresql.org  
help / color / mirror / Atom feed

From: Martin Edlman <martin.edlman@gmail.com>
To: Andrew Smith <laconical@gmail.com>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Fwd: Enhancement to SQL query capabilities
Date: Fri, 18 Mar 2016 08:20:30 +0100
Message-ID: <56EBAC3E.7060401@gmail.com> (raw)
In-Reply-To: <CALr=acEdA4uuDz1h9b_kCaw38yqzFwGon3axuWRV-WkoATttvg@mail.gmail.com>
References: <CALr=acHJbn1kiohs8upmSxN+JK5OWQ5=3LK5wp2PzGyiRK4oSA@mail.gmail.com>
	<CAKFQuwbv1vgovrEAfsY7zUoY8dStLLcEJ0rFHQV+e7CtT4hySg@mail.gmail.com>
	<CALr=acFkr-BGcq24osM34qHLhr11GAyZvD-N0-jkXFn=UpV2Dw@mail.gmail.com>
	<CALr=acEdA4uuDz1h9b_kCaw38yqzFwGon3axuWRV-WkoATttvg@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>

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

Attachments:

  [application/pkcs7-signature] smime.p7s (2.1K, ../56EBAC3E.7060401@gmail.com/2-smime.p7s)
  download

view thread (7+ messages)  latest in thread

Message-ID: <56EBAC3E.7060401@gmail.com>
Permalink:  ../56EBAC3E.7060401@gmail.com/
Also on:    postgresql.org/message-id/56EBAC3E.7060401@gmail.com

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: martin.edlman@gmail.com, laconical@gmail.com, david.g.johnston@gmail.com
  Subject: Re: Fwd: Enhancement to SQL query capabilities
  In-Reply-To: <56EBAC3E.7060401@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox