[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
[SQL] Difference from average
Hi all,
I'm developing a property rental database. One of the tables tracks
the price per week for different properties:
CREATE TABLE "public"."prices" (
"id" SERIAL,
"property_id" INTEGER,
"start_date" TIMESTAMP WITHOUT TIME ZONE,
"end_date" TIMESTAMP WITHOUT TIME ZONE,
"price" DOUBLE PRECISION NOT NULL
) WITH OIDS;
CREATE INDEX "prices_idx" ON "public"."prices"
USING btree ("property_id");
I'd like to display the prices per property in a table, with each row
coloured different shades; darker shades representing the more
expensive periods for that property. To do this, I propose to
calculate the percentage difference of each rows price from the
average for that property, so if for example I have two rows, one for
price=200 and one for price=300, i'd like to retrieve both records
along with the calculated field indicating that the rows are -20%,
+20% from the average, respectively.
I've started with the following query, but since I'm still learning
how PostgreSQL works, I'm confused as to the efficiency of the
following statement:
SELECT *, (price - (SELECT avg(price) from prices)) as diff FROM prices;
EXPLAIN reveals (albeit not a real test, as only the two rows above)
Seq Scan on prices (cost=1.03..2.05 rows=2 width=32)
InitPlan
-> Aggregate (cost=1.03..1.03 rows=1 width=8)
-> Seq Scan on prices (cost=0.00..1.02 rows=2 width=8)
Does this mean that I'll be performing a nested table scan every time
I run this query? Also, I haven't yet calculated the percentage
difference for this, which in my eyes means another instance of
"SELECT avg(price) from prices". Is this the best way of doing this?
Can I optimize this away by re-writing this as a function and storing
"SELECT avg(price) from prices)" in a variable?
All opinions gratefully received.
Kind Regards,
Neil
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] PGSQL encryption functions
OK, you're not really "breaking" md5. If the attacker already knows the information being encrypted, then all you're testing is the concatenation order- Surely the information is more important than the order? md5 is a one way hash function, and so using an alternate algorithm will provide no benefit whatsoever; you're just running through 9 permutations. Kind Regards, Neil. On 11/2/05, Mark R. Dingee <[EMAIL PROTECTED]> wrote: > Mike & Tom, > > The script I'm using to "break" md5 presumes that the cracker knows the 3 > elements being concatenated together to form the plain-text sting which is > then passed into md5. The method I'm using then begins running through > various permutations. Do you believe that the methodology is appropriate or > that I'm being a bit paranoid? > > Thanks > > On Tuesday 01 November 2005 05:13 pm, Tom Lane wrote: > > "Mark R. Dingee" <[EMAIL PROTECTED]> writes: > > > md5 works, but I've been able to > > > brute-force crack it very quickly, > > > > Really? Where's your publication of this remarkable breakthrough? > > > > regards, tom lane > > > > ---(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 > > On Wednesday 02 November 2005 04:26 am, Mario Splivalo wrote: > > On Tue, 2005-11-01 at 17:13 -0500, Tom Lane wrote: > > > "Mark R. Dingee" <[EMAIL PROTECTED]> writes: > > > > md5 works, but I've been able to > > > > brute-force crack it very quickly, > > > > > > Really? Where's your publication of this remarkable breakthrough? > > > > I'd say you can't bruteforce md5, unless you're extremley lucky. > > However, md5 is easily broken, you just need to know how to construct > > the hashes. > > > > One could switch to SHA for 'increaased' security. > > > > Although I don't think he'd be having problems using MD5 as he described > > it. I'd also lilke to see he's example of brute-force 'cracking' the MD5 > > digest. > > > > Mike > > ---(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 > ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Foreign key to 2 tables problem
As far as I'm aware, not without using an intermediatary table (id, cust_id, supplier_id) . Otherwise, how would you know which table the foreign key was referencing? That said, an intermediatary table isn't a very clean solution; What problem are you trying to solve, exactly? Kind Regards, Neil. On 11/22/05, Joost Kraaijeveld <[EMAIL PROTECTED]> wrote: > Hi, > > Is there a way to create a foreign key to 2 tables: e.g. a bankaccount > table that has a column "owner", that must point to a record in either > the customer or the supplier table? > > > TIA > > -- > Groeten, > > Joost Kraaijeveld > Askesis B.V. > Molukkenstraat 14 > 6524NB Nijmegen > tel: 024-3888063 / 06-51855277 > fax: 024-3608416 > e-mail: [EMAIL PROTECTED] > web: www.askesis.nl > > > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Triggers
Try: create or replace function update_last_edit() returns trigger as $$ begin new.last_edit=now(); return new; end; $$ language plpgsql; On 11/22/05, Leif B. Kristensen <[EMAIL PROTECTED]> wrote: > On Tuesday 22 November 2005 17:25, Achilleus Mantzios wrote: > >O Leif B. Kristensen έγραψε στις Nov 22, 2005 : > >> I'm trying to understand triggers. I have read the documentation in > >> the manual as well as the few pages in the Douglas book about the > >> subject, but I don't see how to implement a trigger that simply > >> updates a 'last_edit' date field in my 'persons' table whenever I do > >> an insert or update into my 'participants' table; that is a trigger > >> that basically does an "UPDATE persons SET 'last_edit' = NOW() WHERE > >> persons.person_id = participants.person_fk". Is that even possible? > > > >smth like: > >foodb=# CREATE or REPLACE FUNCTION upd_dad() RETURNS "trigger" AS ' > >foodb'# BEGIN > >foodb'# UPDATE dad set lastedit=now() where id=new.dadid; > >foodb'# RETURN new; > >foodb'# END; > >foodb'# ' > >foodb-# LANGUAGE plpgsql; > >CREATE FUNCTION > >foodb=# CREATE TRIGGER upd_dad_tg AFTER INSERT OR UPDATE ON kid FOR > > EACH ROW EXECUTE PROCEDURE upd_dad(); > > leif=> create or replace function update_last_edit() returns trigger as > $$ > leif$> begin > leif$> update persons set last_edit=now() where person_id=new.person_fk; > leif$> return new; > leif$> end; > leif$> $$ language plpgsql; > CREATE FUNCTION > leif=> create trigger update_last_edit after insert or update on > participants > leif-> for each row execute procedure update_last_edit(); > CREATE TRIGGER > leif=> insert into participants (participant_id,person_fk) values (1,1); > ERROR: record "new" has no field "last_edit" > CONTEXT: PL/pgSQL function "process_last_edited" line 2 at assignment > leif=> > > What am I missing? > -- > Leif Biberg Kristensen | Registered Linux User #338009 > http://solumslekt.org/ | Cruising with Gentoo/KDE > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster > ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Triggers
And change AFER INSERT to BEFORE INSERT On 11/22/05, Achilleus Mantzios <[EMAIL PROTECTED]> wrote: > O Leif B. Kristensen έγραψε στις Nov 22, 2005 : > > > On Tuesday 22 November 2005 17:25, Achilleus Mantzios wrote: > > >O Leif B. Kristensen έγραψε στις Nov 22, 2005 : > > >> I'm trying to understand triggers. I have read the documentation in > > >> the manual as well as the few pages in the Douglas book about the > > >> subject, but I don't see how to implement a trigger that simply > > >> updates a 'last_edit' date field in my 'persons' table whenever I do > > >> an insert or update into my 'participants' table; that is a trigger > > >> that basically does an "UPDATE persons SET 'last_edit' = NOW() WHERE > > >> persons.person_id = participants.person_fk". Is that even possible? > > > > > >smth like: > > >foodb=# CREATE or REPLACE FUNCTION upd_dad() RETURNS "trigger" AS ' > > >foodb'# BEGIN > > >foodb'# UPDATE dad set lastedit=now() where id=new.dadid; > > >foodb'# RETURN new; > > >foodb'# END; > > >foodb'# ' > > >foodb-# LANGUAGE plpgsql; > > >CREATE FUNCTION > > >foodb=# CREATE TRIGGER upd_dad_tg AFTER INSERT OR UPDATE ON kid FOR > > > EACH ROW EXECUTE PROCEDURE upd_dad(); > > > > leif=> create or replace function update_last_edit() returns trigger as > > $$ > > leif$> begin > > leif$> update persons set last_edit=now() where person_id=new.person_fk; > > leif$> return new; > > leif$> end; > > leif$> $$ language plpgsql; > > CREATE FUNCTION > > leif=> create trigger update_last_edit after insert or update on > > participants > > leif-> for each row execute procedure update_last_edit(); > > CREATE TRIGGER > > leif=> insert into participants (participant_id,person_fk) values (1,1); > > ERROR: record "new" has no field "last_edit" > > CONTEXT: PL/pgSQL function "process_last_edited" line 2 at assignment > ^ > > > leif=> > > > > What am I missing? > > > > apparently some forgotten process_last_edited() function. > > -- > -Achilleus > > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Archives site down?
Apologies if this has been mentioned on announce (I'm not subscribed), but the mailing list archive site appears to have a problem (403 Forbidden): http://archives.postgresql.org/ ---(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] LinkedList
Ray, There's a good introductory article on Sitepoint for doing this kind of thing: http://www.sitepoint.com/article/hierarchical-data-database The "Modified Preorder Tree Traversal" is quite a nice method that I would expect to be a magnitude faster than recursive joins, although does have some drawbacks. Kind Regards, Neil. On 4/27/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote: > decibel=# select * from t; > a | b > ---+--- > 1 | 0 > 3 | 1 > 5 | 3 > 7 | 5 > 2 | 0 > 4 | 2 > 6 | 4 > 8 | 6 > (8 rows) > > decibel=# select * from t x join t y on(x.a=y.b) where y.a=7; > a | b | a | b > ---+---+---+--- > 5 | 3 | 7 | 5 > (1 row) > > decibel=# select * from t x join t y on(x.a=y.b) where y.a=8; > a | b | a | b > ---+---+---+--- > 6 | 4 | 8 | 6 > (1 row) > > decibel=# > > As you can see, it selects the right data, but you'll need to step > through it somehow. You might be able to do it with a generate_series(), > or you can use a function. If we get WITH support/recursion in 8.2 you'd > use that. > > I think that "SQL For Smarties" by Joe Celko might have an example of > how to do this without using a function. Even if it doesn't it's a book > any serious database developer should own. > > On Wed, Apr 26, 2006 at 10:35:15AM -0700, Ray Madigan wrote: > > Scott, > > > > Thanks for your reply, I tried what you said, worked around a few things > > but I am still stuck. The main reason is I didn't do an adequate job of > > explaining the situation. The table implements many linked lists and I want > > to traverse one of them given the end of the list. > > > > Say the table contains > > > > h | v | j > > 1 0 100 > > 3 1 300 > > 5 3 500 > > 7 5 700 > > > > 2 0 200 > > 4 2 400 > > 6 4 600 > > 8 6 800 > > > > If I specify t.h = 8 I want to traverse the even part of the table > > If I specify t.h = 7 I want to traverse the odd part of the table > > > > If you can send me to a book to read I am willing > > > > Thanks > > > > -Original Message- > > From: [EMAIL PROTECTED] > > [mailto:[EMAIL PROTECTED] Behalf Of Scott Marlowe > > Sent: Wednesday, April 26, 2006 8:59 AM > > To: Ray Madigan > > Cc: pgsql-sql@postgresql.org > > Subject: Re: [SQL] LinkedList > > > > > > On Wed, 2006-04-26 at 11:09, Ray Madigan wrote: > > > I have a table that I created that implements a linked list. I am not an > > > expert SQL developer and was wondering if there are known ways to traverse > > > the linked lists. Any information that can point me in the direction to > > > figure this out would be appreciated. The table contains many linked > > lists > > > based upon the head of the list and I need to extract all of the nodes > > that > > > make up a list. The lists are simple with a item and a link to the > > history > > > item so it goes kind of like: > > > > > > 1, 0 > > > 3, 1 > > > 7, 3 > > > 9, 7 > > > ... > > > > > > Any suggestions would be helpful, or I will have to implement the table > > > differently. > > > > You should be able to do this with a fairly simple self-join... > > > > select a.id, b.aid, a.field1, b.field1 > > from mytable a > > join mytable b > > on (a.id=b.aid) > > > > Or something like that. > > > > ---(end of broadcast)--- > > TIP 2: Don't 'kill -9' the postmaster > > > > > > ---(end of broadcast)--- > > TIP 6: explain analyze is your friend > > > > -- > Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] > Pervasive Software http://pervasive.comwork: 512-231-6117 > vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster > ---(end of broadcast)--- TIP 6: explain analyze is your friend
