Re: [SQL] [NOVICE] Make year 01/01/0001 but leave timestamp alone
WJS> I have a situation where we have a timestamp column but in special cases WJS> we want to set the date to '01/01/0001' and leave the timestamp alone. WJS> For example, '11/04/2005 10:30:05' would become '01/01/0001 10:30:05'. WJS> I've been going through the various date time functions but don't see a WJS> simple way to do this. Can someone help? WJS> Thanks, WJS> Jed Maybe... Chomp the date part and concatenate the timestamp with '01/01/0001' DAQ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Breakdown results by month
I have the ff table: id |total| effective|end_date john 6 01-01-200502-28-2005 john 8 03-01-200506-30-2005 How can I return: id |total| effective|end_date john 6 01-01-200501-31-2005 john 6 02-01-200502-28-2005 john 8 03-01-200503-31-2005 john 8 04-01-200504-30-2005 john 8 05-01-200505-31-2005 john 8 06-01-200506-30-2005 Any help would be appreciated. Thanks ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Trigger on select?
Hey, anyone know if it is possible to fire a trigger before a select? I'm considering creating some tables which contain data summarized from other tables as kind of a cache mechanism. The hope is I can speed up some queries that get executed a lot (and are kind of slow) by maintaining data (like sums of big columns, for example). I was all ready to go and then I discovered that trigger-before-select is not supported. (Is it in any DB?) The idea is this: Any time I execute a query that would access the summary data, the "before select" trigger fires and goes out and builds any summary data missing from the summary table. When I do an insert,update,delete on the primary data table, another trigger fires that removes the applicable data from the summary table. This way, I only cache the information I need in the summary table, right before I need it. But it can stay there as long as the base information doesn't change so I don't have to redo the expensive operation of summarizing it any more often than necessary. Its kind of like an index in a way, but it is not maintained at insert/update time. Rather, it is updated as it is needed. Anyone have any ideas about how I can accomplish this? Kyle ---(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] Trigger on select?
Kyle Bateman wrote: Hey, anyone know if it is possible to fire a trigger before a select? I'm considering creating some tables which contain data summarized from other tables as kind of a cache mechanism. The hope is I can speed up some queries that get executed a lot (and are kind of slow) by maintaining data (like sums of big columns, for example). I was all ready to go and then I discovered that trigger-before-select is not supported. (Is it in any DB?) The idea is this: Any time I execute a query that would access the summary data, the "before select" trigger fires and goes out and builds any summary data missing from the summary table. No. You must instead generate a view. When I do an insert,update,delete on the primary data table, another trigger fires that removes the applicable data from the summary table. This way, I only cache the information I need in the summary table, right before I need it. But it can stay there as long as the base information doesn't change so I don't have to redo the expensive operation of summarizing it any more often than necessary. Its kind of like an index in a way, but it is not maintained at insert/update time. Rather, it is updated as it is needed. Anyone have any ideas about how I can accomplish this? something like create view wrapper_table as select * from original table where (select pseudo_trigger_function()) IS TRUE; The above example is off the top of my head. It may require some editing. Best Wishes, Chris Travers Metatron Technology Consulting ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
