Re: 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)

--
Richard Huxton
Archonet Ltd