[SQL] Triggers & Conditional Assignment
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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Triggers & Conditional Assignment
coalesce(NEW.end_date , OLD.end_date) will solve the issue.On 9/15/05, Neil Saunders <[EMAIL PROTECTED] > 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_dateand end_date) overlap for the same property across both tables.It works simply by doing a SELECT using the OVERLAP keyword onNEW.start_date , and NEW.end_date for both tables (Ignoring the recordbeing modified). This works fine on inserts (Where both start_date andend_date are specified), and updates that modify both start_date andend_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:DECLAREsdate DATE;edate DATE; BEGINsdate = (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 assignsdate and edate, or is there another technique that I've missedentirely?Kind Regards,Neil Saunders.---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- with regards,S.GnanavelSatyam Computer Services Ltd.
Re: [SQL] showing multiple REFERENCE details of id fields in single
Ferindo Middleton Jr wrote: I have a table which has two id fields which REFERENCE data back at another table. It's setup like this: class_prerequisite_bindings(id SERIAL, class_id INTEGER REFERENCES classes(id), prerequisiteINTEGER REFERENCES classes(id)) The classes table is like this: classes(idSERIAL, course_titleTEXT, course_codeTEXT) I have the following query: SELECT * FROM class_prerequisite_bindings, classes WHERE class_prerequisite_bindings.class_id = 64 AND class_prerequisite_bindings.class_id = classes.id; If I run the query above, the result will only give me info about the class_id field matching id 64 back in the classes table. PROBLEM: I want this query to also show the info about the prerequisite field which would also have info at the classes table. This query will only show the course_title and course_code of the class_id but I need this for the prerequisite field as well. I think I need to do a JOIN, but I don't understand how. How can I do this? create table classes( id SERIAL PRIMARY KEY, course_title TEXT, course_codeTEXT ); go insert into classes( course_title, course_code ) values( 'A', 'A1' ); insert into classes( course_title, course_code ) values( 'B', 'B1' ); insert into classes( course_title, course_code ) values( 'C', 'C1' ); go create table class_prerequisite_bindings( id SERIAL, class_id INTEGER REFERENCES classes(id), prerequisite INTEGER REFERENCES classes(id) ); go insert into class_prerequisite_bindings( class_id, prerequisite ) values( 1, 2 ); insert into class_prerequisite_bindings( class_id, prerequisite ) values( 1, 3 ); go SELECT a.id AS class_id, c.id AS prerequisite_class FROM classes a JOINclass_prerequisite_bindings b ON b.class_id = a.id JOINclasses c ON c.id = b.prerequisite WHERE a.id = 1; go Ferindo ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Daryl "We want great men who, when fortune frowns, will not be discouraged." -- Colonel Henry Knox, 1776 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Triggers & Conditional Assignment
Neil Saunders <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] passing values into .sql scripts
I want to do something like this: \set tmp :acct 'a value' \i query.sql where query.sql looks like this: select sum(amount), dr_acct from ledger where dr_acct = :acct group by dr_acct; select sum(amount), cr_acct from ledger where cr_acct = :acct group by cr_acct; select (select sum(amount) from ledger where dr_acct = :acct ) - (select sum(amount) from ledger where cr_acct = :acct ); However, this is what I get: psql:pnc.sql:1: ERROR: column "a value" does not exist Is there a different way I should be doing this? (I'm used to Oracle's &1...&n parameters.) Geoffrey -- Geoffrey S. Knauth | http://knauth.org/gsk ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] How to install Postgresql 8 on different drive letter?
Hi, I downloaded postgresql 8.0 MSI. How can I tell postgresql to install on a different drive letter? Thank you Yahoo! for Good Click here to donate to the Hurricane Katrina relief effort.
Re: [SQL] passing values into .sql scripts
On Thu, Sep 15, 2005 at 05:27:52PM -0400, Geoffrey Knauth wrote: > I want to do something like this: > > \set tmp :acct 'a value' What's your intention here? The above sets the variable tmp to the value of the variable acct concatenated with 'a value', but you don't show acct being set anywhere. Did you mean to set acct? If so then try this: \set acct '\'a value\'' > \i query.sql > > where query.sql looks like this: > > select sum(amount), dr_acct from ledger where dr_acct = :acct > group by dr_acct; > select sum(amount), cr_acct from ledger where cr_acct = :acct > group by cr_acct; > select >(select sum(amount) from ledger where dr_acct = :acct ) > - (select sum(amount) from ledger where cr_acct = :acct ); > > However, this is what I get: > > psql:pnc.sql:1: ERROR: column "a value" does not exist What's pnc.sql? Is that the real name of the file you referred to as query.sql? -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] passing values into .sql scripts
That did exactly what I wanted. Thank you! (I had tried \set acct ... before, but it was your quoting that fixed my problem.) Geoffrey -- Geoffrey S. Knauth | http://knauth.org/gsk On Sep 15, 2005, at 18:43, Michael Fuhr wrote: What's your intention here? The above sets the variable tmp to the value of the variable acct concatenated with 'a value', but you don't show acct being set anywhere. Did you mean to set acct? If so then try this: \set acct '\'a value\'' ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
