From pk_rehan@yahoo.com Mon Mar  19 19:40:20 2012
Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id A99EBC7DB13
	for <pgsql-sql@postgresql.org>; Mon, 19 Mar 2012 16:40:20 -0300 (ADT)
Received: from nm12-vm2.bullet.mail.ne1.yahoo.com ([98.138.91.88])
	by makus.postgresql.org with smtp (Exim 4.72)
	(envelope-from <pk_rehan@yahoo.com>) id 1S9iRH-0006OH-5S
	for pgsql-sql@postgresql.org; Mon, 19 Mar 2012 19:40:19 +0000
Received: from [98.138.90.50] by nm12.bullet.mail.ne1.yahoo.com with NNFMP;
	19 Mar 2012 19:40:06 -0000
Received: from [98.138.89.172] by tm3.bullet.mail.ne1.yahoo.com with NNFMP;
	19 Mar 2012 19:40:06 -0000
Received: from [127.0.0.1] by omp1028.mail.ne1.yahoo.com with NNFMP;
	19 Mar 2012 19:40:06 -0000
X-Yahoo-Newman-Property: ymail-3
X-Yahoo-Newman-Id: 717143.37210.bm@omp1028.mail.ne1.yahoo.com
Received: (qmail 50117 invoked by uid 60001); 19 Mar 2012 19:40:06 -0000
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024;
	t=1332186006; bh=vq86q6v7fbmMTGgUFazNjqwZkuhvZRPdLFwRtq7slho=;
	h=X-YMail-OSG:Received:X-Mailer:Message-ID:Date:From:Reply-To:Subject:To:MIME-Version:Content-Type;
	b=3uR0jegZ3rTBb9Q9Z74re9g6FkIK5bZ6hyOVviyJeQZ58IwrWVwTQMU1PGz0jiv1wk9P9bHKoNvyEy3mPlC1NJ8jiH6hstLPlH2ZNV8bOPM0WHHe93sSoPheIIydfayw6kaEI6V6CKW2tNGz2p2/ykyK40ZxvaI/3qpuZMbrHzM=
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=X-YMail-OSG:Received:X-Mailer:Message-ID:Date:From:Reply-To:Subject:To:MIME-Version:Content-Type;
	b=ioim7V9VaYoRA4dot0MsWRmdAR2dDlb8S1PcAAlVBy2WuW3AqXsKot9pLPXoGSAfQ3hy4+ASX+SBd+s5G1L9AHljaMGSH0cPAClkCG6a+zowdbyAeBs76j6E499Q7LWDV4HiAb7VvsZERyQM5uK2igEMHjReGT4KEhagyXA9+ps=;
X-YMail-OSG: LwzI8D0VM1kGmn.FZXg_eQIeBN5N0rnVJYoQS5PuTiPT_Am
	CjcQbxk7nYAx6zkUvNHZn6vAahufv4geLnfRL1mzp3uBD97_9RNkWMmWVrDN
	sUlrTacbn3xoI5_DK.gGCR8aAsrro9Z3mh2A0Z4I2BJrudeHvPtME.qYIwy3
	e.Sjf4FZA_sxr81NG9xzLUw92f302vrX6moHShdO.YWtZtj4GSDEhmGTTdEg
	FVUIn2R9l8LMq1mCj7DSUmyGEbcnvkQqd39C3niGhVOF9Fd4WleF409vC_DK
	D9ZsHPfC8C2p9u48h5agTS9cS6c9JXp4nm8nxgv1LrAmzMOtVV25lU0YGHin
	.y1n.lhVwmjCYKhCsPhvCSBBlin7J9qguX2vDTuO9xo847LHkUtyBZqdt1Rc
	uuSnxR_g1QwIWIn9p4hAAxFGzUMzPaZJdEzADh2Baj1XAB1f9Vto9ftAGXzV Xo5s-
Received: from [182.177.147.66] by web121605.mail.ne1.yahoo.com via HTTP;
	Mon, 19 Mar 2012 12:40:06 PDT
X-Mailer: YahooMailWebService/0.8.116.338427
Message-ID: <1332186006.44650.YahooMailNeo@web121605.mail.ne1.yahoo.com>
Date: Mon, 19 Mar 2012 12:40:06 -0700 (PDT)
From: Rehan Saleem <pk_rehan@yahoo.com>
Reply-To: Rehan Saleem <pk_rehan@yahoo.com>
Subject: Postgresql function which compares values from both tables
To: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="2053635335-1467089104-1332186006=:44650"
X-Pg-Spam-Score: 1.6 (+)
X-Archive-Number: 201203/48
X-Sequence-Number: 36443

--2053635335-1467089104-1332186006=:44650
Content-Type: text/plain; charset=us-ascii

hi ,
how to write a function which should read data from 2 tables having same number of columns and should show the common values from those tables.
thanks

--2053635335-1467089104-1332186006=:44650
Content-Type: text/html; charset=us-ascii

<html><body><div style="color:#000; background-color:#fff; font-family:times new roman, new york, times, serif;font-size:12pt">hi ,<br>how to write a function which should read data from 2 tables having same number of columns and should show the common values from those tables.<br>thanks<br></div></body></html>
--2053635335-1467089104-1332186006=:44650--



From polobo@yahoo.com Mon Mar  19 20:04:25 2012
Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
	by mail.postgresql.org (Postfix) with ESMTP id 2BFFAC7DB12
	for <pgsql-sql@postgresql.org>; Mon, 19 Mar 2012 17:04:25 -0300 (ADT)
Received: from smtp104.prem.mail.ac4.yahoo.com ([76.13.13.43])
	by magus.postgresql.org with smtp (Exim 4.72)
	(envelope-from <polobo@yahoo.com>) id 1S9ioY-0007KJ-7A
	for pgsql-sql@postgresql.org; Mon, 19 Mar 2012 20:04:24 +0000
Received: (qmail 36951 invoked from network); 19 Mar 2012 20:04:08 -0000
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=DKIM-Signature:X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:From:To:References:In-Reply-To:Subject:Date:Message-ID:MIME-Version:Content-Type:X-Mailer:Thread-Index:Content-Language;
	b=lf7I1myvVdDJXOYXnotxpoj/TPqI8a6crp8RW2ZcmTEQ7Rnvu4HeRsTRoDBGkdWwxT36LMzWXlnwR+v6GwTrFIQLS1ehDeoSjmj0nlS366ZkyI7aILN4IhUUdoGqoX+PANVfprr8yKvy71Av/FL//C7eAbeOFHFozQxNltT6C0o=
	;
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024;
	t=1332187448; bh=vFsJerm7ilhI9/9bmKHA4AyZ32Y2pFQt+RkkYucq9bQ=;
	h=X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:From:To:References:In-Reply-To:Subject:Date:Message-ID:MIME-Version:Content-Type:X-Mailer:Thread-Index:Content-Language;
	b=rl81TTFXxNgFgOlAnY6Plv4zsLqBpgj/DHJ0ntSNp5TVKbKIP1WGgMn+FPJ3QC94JpJHdGllGsEAif7pdDba5ePJCGj4a+lfGYs9MLssEKgE9umLPOdRpuVR6vuXuykVVQbPh0iKB3ZNbMtoCYzNuLpyIOhM2bpLQnRtyqqcaeI=
X-Yahoo-Newman-Property: ymail-3
X-YMail-OSG: Yq9IjOAVM1mIhMBILFa33UGMYJqgormnPQC7JJ3VdJg4gyf
	GzIVIfBfqgDvnyicv2Hbo3xG60cpMEtKI1KUs1nCiy6qbmVBu6_Afa6W6lIy
	ksBujHHWWqmCe6GQUORnONV6EpLGO1QzJCBA6hwiGkX.Zhp2.FXrs6QFG0NL
	SHDWK5lAzPQjKEBQsTsLmOxxeiOm7_s8BXsBKidbnse59xcn_XSIP85Zzp32
	pf5Go1j6kJjSaJt8BSFTeVjQTY4vzcJ6VoWiP84ZFKynP6PvuKq8xTQyfJL2
	5F25QIUPZbvie6ysPyvCw2V10PHFcIf1GBJU5QiWROu.2gtOk9Psah8wR2TH
	tODSQsH_HoI.kqtoQYhMUbkscBOf4m3GZtnSxanlsbZlEd1oz59lB34_o79j
	.Eo0QP1M7NMIHbaU9FqBI
X-Yahoo-SMTP: mpGJl6eswBD2IBufoVEg0Pa8gg--
Received: from WolfDog (polobo@24.93.23.188 with login)
	by smtp104.prem.mail.ac4.yahoo.com with SMTP;
	19 Mar 2012 13:04:08 -0700 PDT
From: "David Johnston" <polobo@yahoo.com>
To: "'Rehan Saleem'" <pk_rehan@yahoo.com>, <pgsql-sql@postgresql.org>
References: <1332186006.44650.YahooMailNeo@web121605.mail.ne1.yahoo.com>
In-Reply-To: <1332186006.44650.YahooMailNeo@web121605.mail.ne1.yahoo.com>
Subject: Re: Postgresql function which compares values from both tables
Date: Mon, 19 Mar 2012 16:04:04 -0400
Message-ID: <00b101cd060b$6f87a560$4e96f020$@yahoo.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_00B2_01CD05E9.E876EFC0"
X-Mailer: Microsoft Outlook 14.0
Thread-Index: AQHFL+j3y9jTbY3Yw2YNdysepRDFF5aBtwfg
Content-Language: en-us
X-Pg-Spam-Score: -0.1 (/)
X-Archive-Number: 201203/49
X-Sequence-Number: 36444

This is a multipart message in MIME format.

------=_NextPart_000_00B2_01CD05E9.E876EFC0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

You would need to install the "HSTORE" extension to convert the record into
a key->value pair then perform the comparison on that.

 

Dave

 

 

From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Rehan Saleem
Sent: Monday, March 19, 2012 3:40 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] Postgresql function which compares values from both tables

 

hi ,
how to write a function which should read data from 2 tables having same
number of columns and should show the common values from those tables.
thanks


------=_NextPart_000_00B2_01CD05E9.E876EFC0
Content-Type: text/html;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" =
xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" =
xmlns=3D"http://www.w3.org/TR/REC-html40"><head><META =
HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Dus-ascii"><meta name=3DGenerator content=3D"Microsoft Word 14 =
(filtered medium)"><style><!--
/* Font Definitions */
@font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
	{font-family:Tahoma;
	panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	margin-bottom:.0001pt;
	font-size:12.0pt;
	font-family:"Times New Roman","serif";}
a:link, span.MsoHyperlink
	{mso-style-priority:99;
	color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{mso-style-priority:99;
	color:purple;
	text-decoration:underline;}
span.EmailStyle17
	{mso-style-type:personal-reply;
	font-family:"Calibri","sans-serif";
	color:#1F497D;}
.MsoChpDefault
	{mso-style-type:export-only;
	font-size:10.0pt;}
@page WordSection1
	{size:8.5in 11.0in;
	margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
	{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]--></head><body lang=3DEN-US link=3Dblue =
vlink=3Dpurple><div class=3DWordSection1><p class=3DMsoNormal><span =
style=3D'font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497=
D'>You would need to install the &#8220;HSTORE&#8221; extension to =
convert the record into a key-&gt;value pair then perform the comparison =
on that.<o:p></o:p></span></p><p class=3DMsoNormal><span =
style=3D'font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497=
D'><o:p>&nbsp;</o:p></span></p><p class=3DMsoNormal><span =
style=3D'font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497=
D'>Dave<o:p></o:p></span></p><p class=3DMsoNormal><span =
style=3D'font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497=
D'><o:p>&nbsp;</o:p></span></p><p class=3DMsoNormal><span =
style=3D'font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497=
D'><o:p>&nbsp;</o:p></span></p><div =
style=3D'border:none;border-left:solid blue 1.5pt;padding:0in 0in 0in =
4.0pt'><div><div style=3D'border:none;border-top:solid #B5C4DF =
1.0pt;padding:3.0pt 0in 0in 0in'><p class=3DMsoNormal><b><span =
style=3D'font-size:10.0pt;font-family:"Tahoma","sans-serif"'>From:</span>=
</b><span style=3D'font-size:10.0pt;font-family:"Tahoma","sans-serif"'> =
pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] =
<b>On Behalf Of </b>Rehan Saleem<br><b>Sent:</b> Monday, March 19, 2012 =
3:40 PM<br><b>To:</b> pgsql-sql@postgresql.org<br><b>Subject:</b> [SQL] =
Postgresql function which compares values from both =
tables<o:p></o:p></span></p></div></div><p =
class=3DMsoNormal><o:p>&nbsp;</o:p></p><div><p class=3DMsoNormal =
style=3D'background:white'><span style=3D'color:black'>hi ,<br>how to =
write a function which should read data from 2 tables having same number =
of columns and should show the common values from those =
tables.<br>thanks<o:p></o:p></span></p></div></div></div></body></html>
------=_NextPart_000_00B2_01CD05E9.E876EFC0--




From dev@archonet.com Mon Mar  19 20:59:41 2012
Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id E006FC7DB12
	for <pgsql-sql@postgresql.org>; Mon, 19 Mar 2012 17:59:41 -0300 (ADT)
Received: from archon2012.plus.com ([80.229.41.160] helo=mail.archonet.com)
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <dev@archonet.com>) id 1S9jg3-0007qv-HJ
	for pgsql-sql@postgresql.org; Mon, 19 Mar 2012 20:59:40 +0000
Received: from [192.168.1.13] (ariel.office.archonet.com [192.168.1.13])
	by mail.archonet.com (Postfix) with ESMTP id 7B005C82C5;
	Mon, 19 Mar 2012 20:59:49 +0000 (GMT)
Message-ID: <4F679E2B.2010207@archonet.com>
Date: Mon, 19 Mar 2012 20:59:23 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.24) Gecko/20111114 Iceowl/1.0b2 Icedove/3.1.16
MIME-Version: 1.0
To: David Johnston <polobo@yahoo.com>
CC: 'Rehan Saleem' <pk_rehan@yahoo.com>, pgsql-sql@postgresql.org
Subject: Re: Postgresql function which compares values from both tables
References: <1332186006.44650.YahooMailNeo@web121605.mail.ne1.yahoo.com>
	<00b101cd060b$6f87a560$4e96f020$@yahoo.com>
In-Reply-To: <00b101cd060b$6f87a560$4e96f020$@yahoo.com>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: quoted-printable
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201203/50
X-Sequence-Number: 36445

On 19/03/12 20:04, David Johnston wrote:
> You would need to install the =93HSTORE=94 extension to convert the rec=
ord
> into a key->value pair then perform the comparison on that.

No, you can do this just fine with a simple join.

BEGIN;

CREATE TABLE ta (id int PRIMARY KEY, d date, t text);
CREATE TABLE tb (id int PRIMARY KEY, d date, t text);

INSERT INTO ta
SELECT 100+n, '2001-01-01'::date + n, 'entry ' || n
FROM generate_series(1,19) n;

INSERT INTO tb
SELECT 200+n, '2001-01-01'::date + n, 'entry ' || n
FROM generate_series(1,19) n;

SELECT ta.id AS id_a, tb.id AS id_b, ta.d, ta.t
FROM ta JOIN tb USING (d,t);

ROLLBACK;

If the fields were named differently you'd need something like:
   FROM ta JOIN tb ON (ta.d1, ta.t1) =3D (tb.d2, tb.t2)

--=20
   Richard Huxton
   Archonet Ltd



From polobo@yahoo.com Mon Mar  19 22:15:17 2012
Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
	by mail.postgresql.org (Postfix) with ESMTP id 50238C7DB12
	for <pgsql-sql@postgresql.org>; Mon, 19 Mar 2012 19:15:17 -0300 (ADT)
Received: from smtp110.prem.mail.ac4.yahoo.com ([76.13.13.93])
	by magus.postgresql.org with smtp (Exim 4.72)
	(envelope-from <polobo@yahoo.com>) id 1S9krA-0001bk-47
	for pgsql-sql@postgresql.org; Mon, 19 Mar 2012 22:15:16 +0000
Received: (qmail 19116 invoked from network); 19 Mar 2012 22:14:58 -0000
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=DKIM-Signature:X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:From:To:Cc:References:In-Reply-To:Subject:Date:Message-ID:MIME-Version:Content-Type:Content-Transfer-Encoding:X-Mailer:Thread-Index:Content-Language;
	b=3zgBo5XYyjCikY++oWo0WydUh2kitCUPfTP1lkSG037ZUzrn+WliImUuq98HNekfqXQmcUC6LzmdaWAOOPmo39Rg8lqBcwTqfFoP2NqM4qrwqb1BmYCz11jm0Vkzl0jHxr9WabUl+NjSdwmYdIZpNz3c6Q5IYuCCHY2rUb6Sd1w=
	;
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024;
	t=1332195298; bh=fFegHyrtpkHr3vj6inDJR1cNR2ojHMsDDpX4Q/PjMAc=;
	h=X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:From:To:Cc:References:In-Reply-To:Subject:Date:Message-ID:MIME-Version:Content-Type:Content-Transfer-Encoding:X-Mailer:Thread-Index:Content-Language;
	b=6KQdpwIlZyGSxhwGbBiwGrr7RhPm2voDP+7ix/zEiNH3DQwJ2T5C4va97U5zREZbp7ZIM5j1eX2HakkvUhPJ4JMV8GIRdIgECb0UBW2c4Wpt5aKXm7KprhC1qGbXjPm2N5/1tA5OQEHdf2VehaSX9LxmxinQiK3P0hWU33ukAlU=
X-Yahoo-Newman-Property: ymail-3
X-YMail-OSG: EecJHH8VM1mBO3t1n_3aDqG4uh2nQ3WnO19YZbbN3OUc8w0
	pBRRpgPs5NL8QXOERNNnHGhnmV63ecvSYuwAGjjIOw97oz2r5n3jWMQZ8HwY
	Fp8.smyHV0OHJyUS3grCoUBvXM5bWFjKvEu7H5SpEeEmF1aaO.7mNp4TxUrj
	FRA8_Bwu75ZeSB9tbtc5oR5ENyTPZnO8Aq_8MWjl3RIDKbxx9YdRADc63G0l
	gR_v2FTVHs2WMz_ZNx.plKZYkbQc706USG5vmpY5oyC5uInC7gZ5gf8t_HCH
	jS2NAeBOS2gON8oDOnk1M7VyqOE3l_FBZ.0TBN4QJEOnW.llwTS51lS9WSf2
	xceu5a.juUQJHxs0QKy.xT19m.NNbr.KokwkcDV0iFSMrBZrUTuNU3wrAZf0
	j1FckT8swYSNfh9ZelvJ5eByy7h42e817PkEcYnqh80184Nn.OndzFLvOfpE
	6TKNfjMcuC73ZJ6UzViQEEzQV
X-Yahoo-SMTP: mpGJl6eswBD2IBufoVEg0Pa8gg--
Received: from WolfDog (polobo@24.93.23.188 with login)
	by smtp110.prem.mail.ac4.yahoo.com with SMTP;
	19 Mar 2012 15:14:58 -0700 PDT
From: "David Johnston" <polobo@yahoo.com>
To: "'Richard Huxton'" <dev@archonet.com>
Cc: "'Rehan Saleem'" <pk_rehan@yahoo.com>, <pgsql-sql@postgresql.org>
References: <1332186006.44650.YahooMailNeo@web121605.mail.ne1.yahoo.com>
	<00b101cd060b$6f87a560$4e96f020$@yahoo.com>
	<4F679E2B.2010207@archonet.com>
In-Reply-To: <4F679E2B.2010207@archonet.com>
Subject: Re: Postgresql function which compares values from both tables
Date: Mon, 19 Mar 2012 18:14:53 -0400
Message-ID: <00dc01cd061d$b6381780$22a84680$@yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
X-Mailer: Microsoft Outlook 14.0
Thread-Index: AQHFL+j3y9jTbY3Yw2YNdysepRDFFwK4N0GnAjWVjryWWmxNwA==
Content-Language: en-us
X-Pg-Spam-Score: -2.0 (--)
X-Archive-Number: 201203/51
X-Sequence-Number: 36446

> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-
> owner@postgresql.org] On Behalf Of Richard Huxton
> Sent: Monday, March 19, 2012 4:59 PM
> To: David Johnston
> Cc: 'Rehan Saleem'; pgsql-sql@postgresql.org
> Subject: Re: [SQL] Postgresql function which compares values from both
> tables
> 
> On 19/03/12 20:04, David Johnston wrote:
> > You would need to install the "HSTORE" extension to convert the record
> > into a key->value pair then perform the comparison on that.
> 
> No, you can do this just fine with a simple join.
> 
> BEGIN;
> 
> CREATE TABLE ta (id int PRIMARY KEY, d date, t text); CREATE TABLE tb (id
int
> PRIMARY KEY, d date, t text);
> 
> INSERT INTO ta
> SELECT 100+n, '2001-01-01'::date + n, 'entry ' || n FROM
> generate_series(1,19) n;
> 
> INSERT INTO tb
> SELECT 200+n, '2001-01-01'::date + n, 'entry ' || n FROM
> generate_series(1,19) n;
> 
> SELECT ta.id AS id_a, tb.id AS id_b, ta.d, ta.t FROM ta JOIN tb USING
(d,t);
> 
> ROLLBACK;
> 
> If the fields were named differently you'd need something like:
>    FROM ta JOIN tb ON (ta.d1, ta.t1) = (tb.d2, tb.t2)
> 

I think I read into the question too much.  If you want to find matching
rows then ( ROW(alias_1.*) = ROW(alias_2.*) ) will work (and you do not even
have to specify the columns explicitly).

Read this for details.  Note that if any of the columns can be NULL you need
to be especially careful.

http://www.postgresql.org/docs/9.0/interactive/functions-comparisons.html

I was thinking that you wanted to know which specific columns matched even
if the row as a whole did not.

Dave J.





From Jayadevan.Maymala@ibsplc.com Tue Mar  20 03:25:47 2012
Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id 0B9111C6AABB;
	Tue, 20 Mar 2012 00:25:47 -0300 (ADT)
Received: from mx1.ibsplc.com ([202.177.44.5])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <prvs=4190dc454=Jayadevan.Maymala@ibsplc.com>)
	id 1S9phg-0006C3-Na; Tue, 20 Mar 2012 03:25:47 +0000
X-IronPort-Anti-Spam-Filtered: true
X-IronPort-Anti-Spam-Result: 
 Ap4EAGT4Z0/AqAcI/2dsb2JhbABCgk+1EYIJAQEBAQN5EAsECQEJL08IBgsICYgMtmeKRIU6YwSIVI0Lhg+KGIJugVQ
X-IronPort-AV: E=Sophos;i="4.73,616,1325442600";
   d="scan'208";a="2224030"
Received: from unknown (HELO Mail1.ibsplc.com) ([192.168.7.8])
	by mx2.ibsplc.com with ESMTP; 20 Mar 2012 08:52:03 +0530
In-Reply-To: <00b101cd060b$6f87a560$4e96f020$@yahoo.com>
References: <1332186006.44650.YahooMailNeo@web121605.mail.ne1.yahoo.com>
	<00b101cd060b$6f87a560$4e96f020$@yahoo.com>
X-Disclaimed: 50290
To: "'Rehan Saleem'" <pk_rehan@yahoo.com>
Cc: pgsql-sql@postgresql.org,
	pgsql-sql-owner@postgresql.org
MIME-Version: 1.0
Subject: Re: Postgresql function which compares values from both tables
X-KeepSent: ABE068CC:D9FD08D9-652579C7:0012372D;
 type=4; name=$KeepSent
X-Mailer: Lotus Notes Release 8.5 December 05, 2008
Message-ID: 
 <OFABE068CC.D9FD08D9-ON652579C7.0012372D-652579C7.00126B68@ibsplc.com>
From: Jayadevan M <Jayadevan.Maymala@ibsplc.com>
Date: Tue, 20 Mar 2012 08:51:10 +0530
X-MIMETrack: Serialize by Router on Mail1/IBS(Release 8.5.2FP3 HF65|August 02,
	2011) at 03/20/2012 08:51:12 AM,
	Serialize complete at 03/20/2012 08:51:12 AM
Content-Type: multipart/alternative;
	boundary="=_alternative 00126B67652579C7_="
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201203/52
X-Sequence-Number: 36447

This is a multipart message in MIME format.
--=_alternative 00126B67652579C7_=
Content-Type: text/plain; charset="US-ASCII"

Hi,
> To: pgsql-sql@postgresql.org
> Subject: [SQL] Postgresql function which compares values from both 
tables
> 
> hi ,
> how to write a function which should read data from 2 tables having 
> same number of columns and should show the common values from those 
tables.
If you want to compare and ensure that the values are same for all 
columns, please have a look at INTERSECT
http://www.postgresql.org/docs/9.1/static/sql-select.html
Regards,
Jayadevan





DISCLAIMER: 

"The information in this e-mail and any attachment is intended only for 
the person to whom it is addressed and may contain confidential and/or 
privileged material. If you have received this e-mail in error, kindly 
contact the sender and destroy all copies of the original communication. 
IBS makes no warranty, express or implied, nor guarantees the accuracy, 
adequacy or completeness of the information contained in this email or any 
attachment and is not liable for any errors, defects, omissions, viruses 
or for resultant loss or damage, if any, direct or indirect."





--=_alternative 00126B67652579C7_=
Content-Type: text/html; charset="US-ASCII"

<font size=2 face="sans-serif">Hi,</font>
<br><font size=2 face="sans-serif">&gt; To: pgsql-sql@postgresql.org<br>
&gt; Subject: [SQL] Postgresql function which compares values from both
tables</font>
<br><font size=2 face="sans-serif">&gt; &nbsp;</font>
<br><font size=2 face="sans-serif">&gt; hi ,<br>
&gt; how to write a function which should read data from 2 tables having
<br>
&gt; same number of columns and should show the common values from those
tables.<br>
If you want to compare and ensure that the values are same for <b>all</b>
columns, please have a look at INTERSECT</font>
<br><a href="http://www.postgresql.org/docs/9.1/static/sql-select.html"><font size=2 face="sans-serif">http://www.postgresql.org/docs/9.1/static/sql-select.html</font></a>
<br><font size=2 face="sans-serif">Regards,</font>
<br><font size=2 face="sans-serif">Jayadevan<br>
</font>
<br><font size=2 face="sans-serif"><br>
<br>
<br>
<br>
DISCLAIMER:</font><font size=3> </font><font size=1 color=#a2a2a2 face="Tahoma"><br>
<br>
&quot;The information in this e-mail and any attachment is intended only
for the person to whom it is addressed and may contain confidential and/or
privileged material. If you have received this e-mail in error, kindly
contact the sender and destroy all copies of the original communication.
IBS makes no warranty, express or implied, nor guarantees the accuracy,
adequacy or completeness of the information contained in this email or
any attachment and is not liable for any errors, defects, omissions, viruses
or for resultant loss or damage, if any, direct or indirect.&quot;</font><font size=3><br>
</font><font size=1><br>
</font><font size=3><br>
</font>
<br>
<br>
--=_alternative 00126B67652579C7_=--