Re: [SQL] Trigger definition . . . puzzled
"Rolf A. de By" <[EMAIL PROTECTED]> writes: > Thanks for that. There is some misunderstanding here. For this example, > I had taken the sting out of my trigger function and turned it into a > much more concise no-op, with warnings. The actual code of my original > trigger function is irrelevant. The no-op trigger function displays the > same strange behaviour: it works as expected for INSERTs, but not for > UPDATEs. The update goes through! And it shouldn't. Reading between the lines, I gather you have an inheritance setup and are expecting a trigger on the parent table to fire for events occurring in the child tables. Doesn't work like that; you need to put triggers on the child tables. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Trigger definition . . . puzzled
Thanks Tom, Yes, this is an inheritance set-up. But actually no: I am executing all my data changes against the parent table, and want the trigger on that parent table to fire for an insert on the parent table as it does. But I also want the trigger to fire when an update on the parent table is executed, and this does not happen. Rolf Tom Lane wrote: "Rolf A. de By" <[EMAIL PROTECTED]> writes: Thanks for that. There is some misunderstanding here. For this example, I had taken the sting out of my trigger function and turned it into a much more concise no-op, with warnings. The actual code of my original trigger function is irrelevant. The no-op trigger function displays the same strange behaviour: it works as expected for INSERTs, but not for UPDATEs. The update goes through! And it shouldn't. Reading between the lines, I gather you have an inheritance setup and are expecting a trigger on the parent table to fire for events occurring in the child tables. Doesn't work like that; you need to put triggers on the child tables. regards, tom lane -- dr ir Rolf A. de By assoc prof Geodata Management and Engineering dept Geoinformation Processing ITC -- International Institute for Geo-information Science & Earth Observation Hengelosestraat 99 7500 AA Enschede, The Netherlands ph +31 53 4874553 fx +31 53 4874335 email [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Trigger definition . . . puzzled
"Rolf A. de By" <[EMAIL PROTECTED]> writes: > Yes, this is an inheritance set-up. But actually no: I am executing all > my data changes against the parent table, and want the trigger on that > parent table to fire for an insert on the parent table as it does. But > I also want the trigger to fire when an update on the parent table is > executed, and this does not happen. There is no update on any row in the parent table. The update is on a row in a child table. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Trigger definition . . . puzzled
Ouch, that hurts! It suddenly dawns on me . . . Big difference on table that you specify and table where it actually takes effect. Confusing at first. Thanks a million, Tom. "Rolf A. de By" <[EMAIL PROTECTED]> writes: Yes, this is an inheritance set-up. But actually no: I am executing all my data changes against the parent table, and want the trigger on that parent table to fire for an insert on the parent table as it does. But I also want the trigger to fire when an update on the parent table is executed, and this does not happen. There is no update on any row in the parent table. The update is on a row in a child table. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Query design assistance - getting daily totals
Hi, Rodrigo is exactly right in my opinion. To provide a little more info on this calendar or day dimension idea.. You can create, for example, a time table dimension which stores every day of every year as a unique record (for as far into the future as you need). You can then associate various attributes to each day, depending on your business needs like so: id|datetime|is_business_day|is_weekday|is_fed_holiday Of course it's not normalized but that's the point. You then just store the id in various places and it's easy to join back to this table and figure out if a particular day has an attribute you're interested in (or you can find the id's for all the days which have a particular attribute for a given date range - to go the other direction, for example). You can get more on this type of thinking from the most excellent resource by Ralph Kimball "The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling (Second Edition)" - this book did more to open my eyes to alternative to traditional "normalized" modeling than anything else. It also made me feel less guilty about building certain non-normal structures. :) I hope that's helpful.. Steve At 12:21 PM 12/13/2007, [EMAIL PROTECTED] wrote: Date: Wed, 12 Dec 2007 14:53:08 -0500 From: "=?UTF-8?Q?Rodrigo_De_Le=C3=B3n?=" <[EMAIL PROTECTED]> To: "Paul Lambert" <[EMAIL PROTECTED]> Cc: pgsql-sql@postgresql.org Subject: Re: Query design assistance - getting daily totals Message-ID: <[EMAIL PROTECTED]> On Dec 12, 2007 1:39 AM, Paul Lambert <[EMAIL PROTECTED]> wrote: > It's a financial application which needs to work using a concept of > 'financial periods' which may not necessarily correspond to calendar > months and it's much easier to manage in this way than it is to merge it > all together using a date field. Eg, 1st January may actually be the > 15th 'working day' of the 9th 'financial period' - however looking at > just a date of jan-1 there is no way of knowing this and it's the > periods that matter more so than the actual date. I think what you need is a Calendar Table to "map" actual dates to "buckets" e.g. 'financial periods', etc. See: http://codeinet.blogspot.com/2006/08/auxiliary-calendar-table-for-sql.html ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] SELECT MAX returns wrong value
Hi Everyone, Sorry if I am missing something obvious but I think I have found a bug. If I perform the following SQL SELECT MAX(column) FROM table WHERE expression and there is no match, Postgres returns a record count of 1. There is no value in max, it is NULL. Thus trying to perform some "other" tasks based on if a record was returned or not is proving a little difficult in this instance. We are using 8.1.9 on Debian. Thanks for any help you might be able to offer. -- If I can ever be of any further assistance, please contact me. Gavin 'Beau' Baumanis eClinic Pty Ltd ABN 80 092 450 274 657 Nicholson Street Carlton North Victoria 3054 Australia P: +61 3 9381 4567 F: +61 3 9381 4657 M: +61 438 545 586 E: [EMAIL PROTECTED] W: http://www.eclinic.com.au begin:vcard fn:Beau n:Baumanis;Gavin org:PalCare Pty. Ltd. adr:;;657 Nicholson Street;Carlton North;Melbourne;3054;Australia email;internet:[EMAIL PROTECTED] title:Application Developer tel;work:+61-3 9381-4567 tel;cell:+61-438-545-586 note;quoted-printable:Certified Novell Engineer (CNE)=0D=0A= Microsoft Certificed Systems Engineer (MCSE)=0D=0A= Post Graduate Certificate in IT=0D=0A= Certificate III Electronics Technician x-mozilla-html:TRUE url:http://www.palcare.com.au version:2.1 end:vcard ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] SELECT MAX returns wrong value
Gavin Baumanis <[EMAIL PROTECTED]> writes: > If I perform the following SQL > SELECT MAX(column) FROM table WHERE expression > and there is no match, Postgres returns a record count of 1. > There is no value in max, it is NULL. This is the behavior required by the SQL standard. > Thus trying to perform some "other" tasks based on if a record was > returned or not is proving a little difficult in this instance. Test for null result, perhaps? regards, tom lane ---(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
