Postgres Pro
Downloads
Home   >   mailing lists

Thread: tubles matching

tubles matching

From
salah jubeh
Date:

Hello,

I have two views both contain  identical column names , order and types except the primary keys. I want to match these two views - return the pk pair  of the rows which match from these views - by comparing all the column values. I want to write a pgplsql function to do this Job by iterating through all the coloumns and compare the values. 

Is there another way to do that ?

Thanks in advance

Re: tubles matching

From
Alban Hertroys
Date:
On 28 September 2011 13:19, salah jubeh <s_jubeh@yahoo.com> wrote:
>
> Hello,
>
> I have two views both contain  identical column names , order and types
> except the primary keys. I want to match these two views - return the pk
> pair  of the rows which match from these views - by comparing all the column
> values. I want to write a pgplsql function to do this Job by iterating
> through all the coloumns and compare the values.
>
> Is there another way to do that ?

SELECT a.pk1, a.pk2 FROM view1 AS b INNER JOIN view2 AS b ON (a.pk1 =
b.pk1 AND a.pk2 = b.pk2 AND a.col1 = b.col1 AND a.col2=b.col2);

--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

Re: tubles matching

From
Chris Travers
Date:
Is something like this what you are trying to do?

somedb=# create table a (a int, b text, c date);
CREATE TABLE
somedb=# create table b (a int, b text, c date);
CREATE TABLE
somedb=# select * from a join b using (a, b, c);
 a | b | c
---+---+---
(0 rows)

somedb=# insert into a values (1, 'test', now());
INSERT 0 1
somedb=# insert into b values (1, 'test', now());
INSERT 0 1
somedb=# insert into b values (2, 'test', now());
INSERT 0 1
somedb=# select * from a join b using (a, b, c);
 a |  b   |     c
---+------+------------
 1 | test | 2011-09-28
(1 row)

Best wishes,
Chris Travers

Re: tubles matching

From
salah jubeh
Date:

Thanks Chris, this solution is one alternative, but it will not work in my app because the join condition in your example is defined using all the fields. in my case the join condition is unknown.  if a row in the first view is a subset of a row in the second view that means there is a match. 

Regards
From: Chris Travers <chris.travers@gmail.com>
To:
Cc: salah jubeh <s_jubeh@yahoo.com>; pgsql <pgsql-general@postgresql.org>
Sent: Wednesday, September 28, 2011 5:09 PM
Subject: Re: [GENERAL] tubles matching

Is something like this what you are trying to do?

somedb=# create table a (a int, b text, c date);
CREATE TABLE
somedb=# create table b (a int, b text, c date);
CREATE TABLE
somedb=# select * from a join b using (a, b, c);
a | b | c
---+---+---
(0 rows)

somedb=# insert into a values (1, 'test', now());
INSERT 0 1
somedb=# insert into b values (1, 'test', now());
INSERT 0 1
somedb=# insert into b values (2, 'test', now());
INSERT 0 1
somedb=# select * from a join b using (a, b, c);
a |  b  |    c
---+------+------------
1 | test | 2011-09-28
(1 row)

Best wishes,
Chris Travers


Re: tubles matching

From
Michael Glaesemann
Date:
On Sep 28, 2011, at 7:19, salah jubeh wrote:

>
>
> Hello,
>
> I have two views both contain  identical column names , order and types except the primary keys. I want to match
thesetwo views - return the pk pair  of the rows which match from these views - by comparing all the column values. I
wantto write a pgplsql function to do this Job by iterating through all the coloumns and compare the values.   
>
> Is there another way to do that ?

SELECT * FROM a NATURAL JOIN b;

Michael Glaesemann
grzm seespotcode net