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

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