Re: [SQL] left outer join on more than 2 tables?
On 16/06/2009 19:12, Rob Sargent wrote the following: Richard Broersma wrote: On Tue, Jun 16, 2009 at 2:40 PM, Rob Sargent wrote: Is there a city without a reference to region? I don't know, but the OP wanted to know complaints by region. I didn't try this, but with regionless cities, you may need a full join if you want a complete accounting of all complaints, some being logged to the null region. And wouldn't you want to count(cm.id)? Count(cm.id) and Count(*) produce the same result. But I like Count(*) more since it helps to correctly express the idea that we are counting rows per group and not cm.id(s) per group. "Same result" is not true. I loaded tables. Using count(*) you get count=1 for regions without complaints. Using count(complaint.id) you get count = 0. (The deference amount to counting the left hand side (region) vs the right hand side (complaint) which I believe is what OP is after). Thanks everyone for your help. Your solutions worked. Much appreciated. - Carol -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] left outer join on more than 2 tables? (UNCLASSIFIED)
Classification: UNCLASSIFIED Caveats: NONE My problem with that is that you are counting rows per region and you have done a left jion on region. That means there will be at least one row per region even if there are 0 compliants. It might yield the same result now, but if you even have a period where a region recieves no complaints they will have a complaint count of 1 instead of 0. That is just my guess based on the logic. -Original Message- From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Richard Broersma Sent: Tuesday, June 16, 2009 3:50 PM To: Rob Sargent Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] left outer join on more than 2 tables? On Tue, Jun 16, 2009 at 2:40 PM, Rob Sargent wrote: > Is there a city without a reference to region? I don't know, but the OP wanted to know complaints by region. > And wouldn't you want to count(cm.id)? Count(cm.id) and Count(*) produce the same result. But I like Count(*) more since it helps to correctly express the idea that we are counting rows per group and not cm.id(s) per group. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql Classification: UNCLASSIFIED Caveats: NONE -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Trapping statement timeout
Hello All: I certain that for you guys that this will be trivial. What I need to do is to trap, go into an exception block, when a statement times out. Let me explain. I have a processing that creates summary records based upon records in another table. Think in terms of summary sales records for sales persons at multiple stores in multiple districts. This process can run for a while and moves lots of data around. I foresee the situation where multiple users might kick off a run for the same data at the same time. So I would like to implement a locking mechanism where by if a district is being run, another user can not kick it off. Please consider the following: create or replace FUNCTION fof_run( fundkey_in integer date_in date) returns void as $body$ DECLARE rowcnt integer; BEGIN BEGIN TRANSACTION; SET LOCAL STATEMENT_TIMEOUT = 5000 select count(*)into rowcnt from fofrun where fi_parentkey = fundkey_in and date_ = date_in; if rowcnt = 0 then insert into fofrum (fi_parentkey , date_, start_) values( fund_key_in, date_in, now ); end if; select * from fofrun where fi_parentkey = fundkey_in and date_ = date_in FOR UPDATE; SET LOCAL STATEMENT_TIMEOUT = 0 COMMIT; EXCEPTION WHEN END; The table FOFRUN will be the keymaster. So for a fund and a date if a records exists, lock it for update. But only wait five seconds for the lock. If you get the lock, all is good. If not I need to return a nice error message Question: When the SELECT FOR UPDATE fails/timeout what Error does it toss? I looked in the PostgreSQL Error Codes and did not see one that matched. When I am in the EXCEPTION block can I execute a normal SELECT against FOFRUN to determine when the prior lock was obtained, based upon the value in start_ Thank you for your attention to this matter. Kevin Duffy
