Re: Adding CORRESPONDING to Set Operations

I delved into the code without waiting for comments from the list just to
learn something about postgresql internals. And I have finished the
CORRESPONDING, now CORRESPONDING BY is being tested. I will also write
documentation and regression tests.

Yes Robert, you are correct. Having used SQL 20nn standard draft as a guide,
a brief explanation can be provided as such:

Shorter version: column name lists are intersected.
Short version: In the set operation queries, which are queries containing
INTERSECT, EXCEPT or UNION, a CORRESPONDING clause can be used to project
the resulting columns to only columns contained in both sides of the query.
There is also and addition of BY(col1, col2, ...) to the clause which
projects the columns to its own list. An example query would clarifiy.

SELECT 1 a, 2 b UNION CORRESPONDING SELECT 3 a;
a
--
1
3

SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(a, c) SELECT 4 a, 5 c
a c
------
1 3
4 5

On Thu, Sep 22, 2011 at 16:20, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Sun, Sep 18, 2011 at 5:39 AM, Kerem Kat <keremkat(at)gmail(dot)com> wrote:
> > I am new to postgresql code, I would like to start implementing easyish
> TODO
> > items. I have read most of the development guidelines, faqs, articles by
> > Greg Smith (Hacking Postgres with UDFs, Adding WHEN to triggers).
> > The item I would like to implement is adding CORRESPONDING [BY
> > (col1[,col2,...]])] to INTERSECT and EXCEPT operators.
> > Can anyone comment on how much effort this item needs?
>
> This seems reasonably tricky for a first project, but maybe not out of
> reach if you are a skilled C hacker. It's certainly more complicated
> than my first patch:
>
>
> http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=a0b76dc662efde6e02921c2d16e06418483b7534
>
> I guess the first question that needs to be answered here is ... what
> exactly is this syntax supposed to do? A little looking around
> suggests that EXCEPT CORRESPONDING is supposed to make the
> correspondence run by column names rather than by column positions,
> and if you further add BY col1, ... then it restricts the comparison
> to those columns.
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>