pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTriggers & Conditional Assignment
3+ messages / 3 participants
[nested] [flat]
* Triggers & Conditional Assignment
@ 2005-09-15 09:25 Neil Saunders <n.j.saunders@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Neil Saunders @ 2005-09-15 09:25 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Triggers & Conditional Assignment
@ 2005-09-15 09:58 Gnanavel S <s.gnanavel@gmail.com>
parent: Neil Saunders <n.j.saunders@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Gnanavel S @ 2005-09-15 09:58 UTC (permalink / raw)
To: n.j.saunders@gmail.com; +Cc: 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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Triggers & Conditional Assignment
@ 2005-09-15 13:56 Tom Lane <tgl@sss.pgh.pa.us>
parent: Neil Saunders <n.j.saunders@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2005-09-15 13:56 UTC (permalink / raw)
To: n.j.saunders@gmail.com; +Cc: pgsql-sql
Neil Saunders <n.j.saunders@gmail.com> writes:
> I've tried to write something along the lines of the following:
> 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 equivalent construct in SQL is CASE.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-09-15 13:56 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-09-15 09:25 Triggers & Conditional Assignment Neil Saunders <n.j.saunders@gmail.com>
2005-09-15 09:58 ` Gnanavel S <s.gnanavel@gmail.com>
2005-09-15 13:56 ` Tom Lane <tgl@sss.pgh.pa.us>
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