Subject: Re: Fwd: Enhancement to SQL query capabilities
To: Andrew Smith <laconical@gmail.com>,
 "David G. Johnston" <david.g.johnston@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>
Cc: pgsql-sql@postgresql.org
From: Martin Edlman <martin.edlman@gmail.com>
Message-ID: <56EBAC3E.7060401@gmail.com>
Date: Fri, 18 Mar 2016 08:20:30 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:38.0) Gecko/20100101
 Thunderbird/38.6.0
MIME-Version: 1.0
In-Reply-To: 
 <CALr=acEdA4uuDz1h9b_kCaw38yqzFwGon3axuWRV-WkoATttvg@mail.gmail.com>
Content-Type: multipart/signed; protocol="application/pkcs7-signature";
 micalg=sha-256; boundary="------------ms080206090100030701090802"
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

This is a cryptographically signed message in MIME format.

--------------ms080206090100030701090802
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable

Hi Andrew,

what about NATURAL JOIN? I think that's the shortest possible way to writ=
e
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:
> =20
>=20
>         In order to get the data I want in postgres, I need to do this:=

>=20
>         select h.Day, h.Description, h.Month, h.Year, g.Name, r.Name fr=
om
>         Holidays h, HolidayGroup g, HolidayRegion r where h.HolidayGrou=
p =3D
>         g.Id and g.HolidayRegion =3D r.Id=20
>=20
>=20
>     I suggest learning ANSI join syntax.
>=20
>     FROM holiday h
>     JOIN holidaygroup g on (h.id <http://h.id> =3D g.id <http://g.id>) =

>     =20
>=20
>=20
> Any particular reason why? This creates a longer query string than the =
one
> I listed and they both give the same result:
>=20
> select h.Day, h.Description, h.Month, h.Year, g.Name, r.Name from Holid=
ays
> h join HolidayGroup g on h.HolidayGroup =3D g.id <http://g.id> join
> HolidayRegion r on g.HolidayRegion =3D r.id <http://r.id>
>=20
> 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.
> =20
>=20
>=20
>      Even if this is something that would be committed (I have my doubt=
s
>     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.
>=20
>     Given you are not adding any new capabilities and making significan=
t
>     changes to parsing code, the barrier for entry is very high.  If th=
e
>     syntax was standard then work on using meta-data to auto-resolve th=
e
>     joins might be worthy of inclusion.
>=20
>=20
> I would have thought that being able to reduce the complexity of long S=
QL
> 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 leas=
t
> I'll probably have to provide a homebrew alternative to psql as we've g=
ot
> heaps of scripts written in this syntax which will take a very long tim=
e to
> port across to standard SQL.
> =20
>=20
>=20
>          I can't find any information on the postgres website about a w=
ay
>         to submit feature requests/enhancements, only to report bugs. I=
s
>         there a formal mechanism to request new functionality?
>=20
>=20
>     Most requests that are not bugs should go to -general unless one of=
 the
>     other lists seems more appropriate.  Discussions about patches occu=
r on
>     -hackers.
>=20
> =20
> Thanks, will keep that in mind for my next dumb idea :-)=20
>=20
>=20


--------------ms080206090100030701090802
Content-Type: application/pkcs7-signature; name="smime.p7s"
Content-Transfer-Encoding: base64
Content-Disposition: attachment; filename="smime.p7s"
Content-Description: Elektronicky podpis S/MIME

MIAGCSqGSIb3DQEHAqCAMIACAQExDzANBglghkgBZQMEAgEFADCABgkqhkiG9w0BBwEAAKCC
BbwwggW4MIIDoKADAgECAgMRx5EwDQYJKoZIhvcNAQELBQAweTEQMA4GA1UEChMHUm9vdCBD
QTEeMBwGA1UECxMVaHR0cDovL3d3dy5jYWNlcnQub3JnMSIwIAYDVQQDExlDQSBDZXJ0IFNp
Z25pbmcgQXV0aG9yaXR5MSEwHwYJKoZIhvcNAQkBFhJzdXBwb3J0QGNhY2VydC5vcmcwHhcN
MTYwMjI1MDcyMTU2WhcNMTYwODIzMDcyMTU2WjBlMRgwFgYDVQQDEw9DQWNlcnQgV29UIFVz
ZXIxITAfBgkqhkiG9w0BCQEWEmVkbG1hbkBiZXpkcmF0Lm5ldDEmMCQGCSqGSIb3DQEJARYX
bWFydGluLmVkbG1hbkBnbWFpbC5jb20wggEiMA0GCSqGSIb3DQEBAQUAA4IBDwAwggEKAoIB
AQDRIlJZsNhV0QDbVWUUATA0XzkzpQAg0EQGuphfGP8POfaofxw9lRbz40RIVp6WIC3STkDs
uvr3wOzomWD1mBgISYctNDdqKhm0pZx+sB5yzdW4zIemREyynsMfv2zrQpOI4YTYq5MH3sX+
KEA/jVyj2BOaH1UYpjTKhQk0PUykDcyLQwOKyrcWj9Z2bOhDVf8dtaPqFylh5su1PK4eag0o
FmJ+d5i0PkmZ4/Df0mWMNVTyu3yY3Jwz52aoW5ZD+I395Xi1W5R+Tps5B/cL0teqUWtUtKdr
HWYdwl8Q0u+Qs7jTUlw95t8jFr9NVcK5cbV61lR2uycKLdBj0SKOAsl3AgMBAAGjggFbMIIB
VzAMBgNVHRMBAf8EAjAAMFYGCWCGSAGG+EIBDQRJFkdUbyBnZXQgeW91ciBvd24gY2VydGlm
aWNhdGUgZm9yIEZSRUUgaGVhZCBvdmVyIHRvIGh0dHA6Ly93d3cuQ0FjZXJ0Lm9yZzAOBgNV
HQ8BAf8EBAMCA6gwQAYDVR0lBDkwNwYIKwYBBQUHAwQGCCsGAQUFBwMCBgorBgEEAYI3CgME
BgorBgEEAYI3CgMDBglghkgBhvhCBAEwMgYIKwYBBQUHAQEEJjAkMCIGCCsGAQUFBzABhhZo
dHRwOi8vb2NzcC5jYWNlcnQub3JnMDEGA1UdHwQqMCgwJqAkoCKGIGh0dHA6Ly9jcmwuY2Fj
ZXJ0Lm9yZy9yZXZva2UuY3JsMDYGA1UdEQQvMC2BEmVkbG1hbkBiZXpkcmF0Lm5ldIEXbWFy
dGluLmVkbG1hbkBnbWFpbC5jb20wDQYJKoZIhvcNAQELBQADggIBAGoLh5BGZMSRGN5x49s6
DCGMqAdbaZQLD9QZh0Mrj8yVboWcTicAmYY90ZDq7RlkLoPSks1++xQexZFJPBJDCR8o5kEd
diKkpHnZOYSE+8sva7nsEtHJ7q9X3MTA3NHHrJiCKGH5AUyUk6G2V6yb0MSdY8QHzOF2DpFs
7YKqFSGlNjpe9cCOxIzKWOzn4rbsSPFou9Fk/siv3IPkSB4EyBAoYNhhso9bsJ8D4V7B7Y69
/9g5W5c3WU2npWVHECuVA1YCPZiuP9BNKKLrSAHfzXAgDsCANZRKTB4aZ53dI7Opfs5BeeHN
r4aD0wfSXFuRNvkX28qrzjwfFIC5lHPHHF5IxA/d+1ihEGFxbLY1C9xHBe64QcyiWz8R/AgU
af7r+F0vhLGDAMIbqIv2pgyNzKe+NRQdgvrlA1Tq5oZ6XhNZdtnoxV3+lTtk9ZRi/YR0Ajce
NZdxVVnQHxjt6itmCAQWk3dsGsH3UT/RJZPE5QhmYTUaPC033QvrqHMQh/hBPnizgTLcUUBc
LR6y2kHRJIwZqeopZcpBKGZdZbu6vsdEmMTvq+tRCskYlb4f2ZOgNpEDjw7W9t47VX/eWpfo
EKjUY/unWqb9+GEeUOuqVbAEItzwEiwjr9NRNY//tu3YGTQ5cixcTw6jASo8dKO0Rmz3tkGY
snMGIiL5TTicH3ZrMYIChjCCAoICAQEwgYAweTEQMA4GA1UEChMHUm9vdCBDQTEeMBwGA1UE
CxMVaHR0cDovL3d3dy5jYWNlcnQub3JnMSIwIAYDVQQDExlDQSBDZXJ0IFNpZ25pbmcgQXV0
aG9yaXR5MSEwHwYJKoZIhvcNAQkBFhJzdXBwb3J0QGNhY2VydC5vcmcCAxHHkTANBglghkgB
ZQMEAgEFAKCB1zAYBgkqhkiG9w0BCQMxCwYJKoZIhvcNAQcBMBwGCSqGSIb3DQEJBTEPFw0x
NjAzMTgwNzIwMzBaMC8GCSqGSIb3DQEJBDEiBCD0ODAUE4FtwAg0zjtwsF9YjwJ+OBx2etXh
3YSapOQI7DBsBgkqhkiG9w0BCQ8xXzBdMAsGCWCGSAFlAwQBKjALBglghkgBZQMEAQIwCgYI
KoZIhvcNAwcwDgYIKoZIhvcNAwICAgCAMA0GCCqGSIb3DQMCAgFAMAcGBSsOAwIHMA0GCCqG
SIb3DQMCAgEoMA0GCSqGSIb3DQEBAQUABIIBAH95DvM9XNMpPxX6Q9QtNKk8BxkES9KwEzb/
NjGjsA9xsOFvzLeGbLhszYDfu6izPSe2CrmC9JJVW32IIztDwfX+UDVdgmCRFKqtfegthIaD
SvzA8QVMBeBPWJWXtyGb+yD5IE5QrcLd8SVnof4pNjw9frdnFJQJAMgzowK8b7Qn9war54wh
fKi5PcvGnLx3NQDBB1h1NMyewsusyx81zGh6Up6cyWEcu3e5kWYPO5yIrVkKjKwwlyYKZdpU
byoaefdVeKsYjvJhQUs5jlDS13ytdlnbkEvVTCjyqUHSnJRTmzhL6uQsp86GB4sow4+jhLaf
yiMwBUWasi8N5EfyLRwAAAAAAAA=
--------------ms080206090100030701090802--