Re: Triggers & Conditional Assignment - Mailing list pgsql-sql

From Gnanavel S
Subject Re: Triggers & Conditional Assignment
Date
Msg-id eec3b03c050915025835eb044e@mail.gmail.com
Whole thread Raw
In response to Triggers & Conditional Assignment  (Neil Saunders <n.j.saunders@gmail.com>)
List pgsql-sql
coalesce(NEW.end_date , OLD.end_date) will solve the issue.

On 9/15/05, Neil Saunders <n.j.saunders@gmail.com > wrote:
Hi,

I've run in to a small problem when writing a trigger.

For simplicities sake lets say that I have 2 tables – 'bookings' and
'unavailable_periods'. Both have columns 'start_date','end_date', and
'property_id'.

I have written a trigger that is fired on inserts and updates for both
tables that simply ensures that no intervals (defined by start_date
and end_date) overlap for the same property across both tables.

It works simply by doing a SELECT using the OVERLAP keyword on
NEW.start_date , and NEW.end_date for both tables (Ignoring the record
being modified). This works fine on inserts (Where both start_date and
end_date are specified), and updates that modify both start_date and
end_date, but for updates where I only update 'start_date', for
example, the trigger fails because NEW.end_date is empty.

Whats the best way around this?

I've tried to write something along the lines of the following:

DECLARE
    sdate DATE;
    edate DATE;

BEGIN
    sdate = (NEW.start_date IS NOT NULL) ? NEW.start_date : OLD.start_date;
    edate = (NEW.end_date IS NOT NULL) ? NEW.end_date : OLD.end_date;


But conditional assignment doesn't seem to be catered for. The next
best thing is a series of IF THEN ELSIF ELSE statements to assign
sdate and edate, or is there another technique that I've missed
entirely?

Kind Regards,

Neil Saunders.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@postgresql.org so that your
       message can get through to the mailing list cleanly



--
with regards,
S.Gnanavel
Satyam Computer Services Ltd.

pgsql-sql by date:

Previous
From: Neil Saunders
Date:
Subject: Triggers & Conditional Assignment
Next
From: Daryl Richter
Date:
Subject: Re: showing multiple REFERENCE details of id fields in single