From: Leif B. Kristensen <leif@solumslekt.org>
To: pgsql-sql@postgresql.org
Subject: Finding multiple events of the same kind
Date: Sun, 11 Jun 2006 14:53:07 +0200
Message-ID: <200606111453.07326.leif@solumslekt.org> (raw)
I've got two tables:
CREATE TABLE events (
event_id INTEGER PRIMARY KEY,
tag_fk INTEGER NOT NULL REFERENCES tags (tag_id),
place_fk INTEGER NOT NULL REFERENCES places (place_id),
event_date CHAR(18) NOT NULL DEFAULT '000000003000000001',
sort_date DATE NOT NULL DEFAULT '40041024BC',
event_note TEXT NOT NULL DEFAULT ''
);
CREATE TABLE participants (
person_fk INTEGER NOT NULL REFERENCES persons (person_id),
event_fk INTEGER NOT NULL REFERENCES events (event_id) ON DELETE
CASCADE,
is_principal BOOLEAN NOT NULL DEFAULT false,
PRIMARY KEY (person_fk, event_fk)
);
The table "participants" is of course a many-to-many relation
between "events" and "persons". My problem is that it's entirely
possible to insert eg. multiple birth events for one person, and I'd
like to be able to spot these.
I've made this function that will return a birth date, but it will of
course be somewhat undefined in case of multiple events (tag_fk
2=birth, 62=stillbirth, 1035="guesstimated" birth).
CREATE OR REPLACE FUNCTION get_pbdate(INTEGER) RETURNS TEXT AS $$
DECLARE
pb_date TEXT;
BEGIN
SELECT event_date INTO pb_date FROM events, participants
WHERE events.event_id = participants.event_fk
AND participants.person_fk = $1
AND events.tag_fk in (2,62,1035)
AND participants.is_principal IS TRUE;
RETURN COALESCE(pb_date,'000000003000000001');
END;
$$ LANGUAGE plpgsql;
The originating database had a field for "primary" event, along with
some business logic for deciding between multiple events of the same
type, but I don't want to maintain something like that. I'll rather run
a report spotting persons with multiple birth events. Any ideas?
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: leif@solumslekt.org
Subject: Re: Finding multiple events of the same kind
In-Reply-To: <200606111453.07326.leif@solumslekt.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox