Re: [SQL] Need magical advice for counting NOTHING

2009-07-22 Thread A. Kretschmer
In response to Andreas : > Hi, > The source select counts log-events per user. > All is well when a user has at least one event per log_type in the log > within a given timespan. > If one log_type is missing COUNT() has nothing to count and there is > expectedly no result line that says 0. > BUT

Re: [SQL] Bit by "commands ignored until end of transaction block" again

2009-07-22 Thread Joshua Tolley
On Thu, Jul 23, 2009 at 02:04:53AM -0400, Glenn Maynard wrote: > On Thu, Jul 23, 2009 at 1:31 AM, Richard Huxton wrote: > >>  - Let me use SAVEPOINT outside of a transaction, > > > > You are never outside a transaction. All queries are executed within a > > transaction. > > "Transaction block", th

Re: [SQL] Need magical advice for counting NOTHING

2009-07-22 Thread Shane Ambler
Andreas wrote: Hi, The source select counts log-events per user. All is well when a user has at least one event per log_type in the log within a given timespan. If one log_type is missing COUNT() has nothing to count and there is expectedly no result line that says 0. BUT I need this 0-line be

Re: [SQL] Bit by "commands ignored until end of transaction block" again

2009-07-22 Thread Richard Huxton
Glenn Maynard wrote: On Thu, Jul 23, 2009 at 1:31 AM, Richard Huxton wrote: - Let me use SAVEPOINT outside of a transaction, You are never outside a transaction. All queries are executed within a transaction. "Transaction block", then, if you insist. I think this is the root of your proble

Re: [SQL] Bit by "commands ignored until end of transaction block" again

2009-07-22 Thread Glenn Maynard
On Thu, Jul 23, 2009 at 1:31 AM, Richard Huxton wrote: >>  - Let me use SAVEPOINT outside of a transaction, > > You are never outside a transaction. All queries are executed within a > transaction. "Transaction block", then, if you insist. > I think this is the root of your problem - all queries

Re: [SQL] Bit by "commands ignored until end of transaction block" again

2009-07-22 Thread Richard Huxton
Glenn Maynard wrote: Postgres kills a transaction when an error happens. This is a pain; it assumes that all SQL errors are unexpected and fatal to the transaction. There's a very simple case where it's not: UNIQUE checks. Ah, it's usually "syntax errors" wrt interactive sessions. > I'm ge

[SQL] Bit by "commands ignored until end of transaction block" again

2009-07-22 Thread Glenn Maynard
Postgres kills a transaction when an error happens. This is a pain; it assumes that all SQL errors are unexpected and fatal to the transaction. There's a very simple case where it's not: UNIQUE checks. I'm generating a cache, with a simple flow: - Search for the cache key; if it exists, return

[SQL] Need magical advice for counting NOTHING

2009-07-22 Thread Andreas
Hi, The source select counts log-events per user. All is well when a user has at least one event per log_type in the log within a given timespan. If one log_type is missing COUNT() has nothing to count and there is expectedly no result line that says 0. BUT I need this 0-line because of a cross

Re: [SQL] how to tell if column set on update

2009-07-22 Thread nha
Hello, Le 22/07/09 13:42, Frank Bax a écrit : > chester c young wrote: >>> Le 20/07/09 15:19, chester c young a écrit : within a trigger need to know if the UPDATE statement >>> set a column. the column might be set to the old value >>> or a different value. [...] >>> If the column to t

Re: [SQL] Double aggregate problem

2009-07-22 Thread nha
Hello, Le 22/07/09 20:04, Peter Eisentraut a écrit : > On Wednesday 22 July 2009 19:16:21 David Weilers wrote: >> I have the following query: >> >> select v.id, array_to_string(array_accum(s.name),', ') as sector , >> array_to_string(array_accum(p.name),', ') as provincie from tblvacature >> v, tb

Re: [SQL] Double aggregate problem

2009-07-22 Thread nha
Hello, Le 22/07/09 18:16, David Weilers a écrit : > Dear anyone, > > I have the following query: > > select v.id, array_to_string(array_accum(s.name),', ') as sector , > array_to_string(array_accum(p.name),', ') as provincie from tblvacature > v, tblaccount a , tblvacaturesector vs, tblsector

Re: [SQL] Double aggregate problem

2009-07-22 Thread Peter Eisentraut
On Wednesday 22 July 2009 19:16:21 David Weilers wrote: > I have the following query: > > select v.id, array_to_string(array_accum(s.name),', ') as sector , > array_to_string(array_accum(p.name),', ') as provincie from tblvacature > v, tblaccount a , tblvacaturesector vs, tblsector s , > tblvacatur

[SQL] Double aggregate problem

2009-07-22 Thread David Weilers
Dear anyone, I have the following query: select v.id, array_to_string(array_accum(s.name),', ') as sector , array_to_string(array_accum(p.name),', ') as provincie from tblvacature v, tblaccount a , tblvacaturesector vs, tblsector s , tblvacatureprovincie vp, tblprovincie p where v.id = 11 and

Re: [SQL] how to tell if column set on update

2009-07-22 Thread Frank Bax
chester c young wrote: Le 20/07/09 15:19, chester c young a écrit : within a trigger need to know if the UPDATE statement set a column. the column might be set to the old value or a different value. (want to make sure the app is sending all necessary values) thanks If the column to test i