Finding multiple events of the same kind

Поиск
Список
Период
Сортировка
От Leif B. Kristensen
Тема Finding multiple events of the same kind
Дата
Msg-id 200606111453.07326.leif@solumslekt.org
обсуждение исходный текст
Ответы Re: Finding multiple events of the same kind  (Frank Bax <fbax@sympatico.ca>)
Список pgsql-sql
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
NULLREFERENCES 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
ANDparticipants.person_fk = $1       AND events.tag_fk in (2,62,1035)       AND participants.is_principal IS TRUE;
RETURNCOALESCE(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


В списке pgsql-sql по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Problems Testing User-Defined Function
Следующее
От: Frank Bax
Дата:
Сообщение: Re: Finding multiple events of the same kind