[SQL] Howto convert / (re)store xml column to table in trigger using contrib/xml2?
Hi, Is it possible to convert / (re)store a column that contains xml (see below) into real table in a trigger, using contrib/xml2? The reason is that an application stores a xml blob in a column but we need the data in a real table for a third party report generator. So I want to use a trigger that, upon insert, update or delete, moves the data into that table. The XML looks like this: field1 value1 field2 value2 And I have a table with the (text) columns field1 and field2. Any pointers to a solution is really appreciated (solutions also ;-)) TIA -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 web: www.askesis.nl ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Funny date-sorting task
Hi, I've got a stack of tasks to show in a list. Every task has a timestamp X that may be NULL or a date. It contains the date when this tasks should be done. Sometimes it has date and the time-part, too. The list should be like this: 1) X sometime today should come first in ascending time order. 2) X in the past should show up after (1) in descending order so that not so long back dates come first 3) X = NULL 4) X sometime in the future The point is, I like to do the skeduled tasks for today as planned. = (1) Those allready lost appointments should not defer those today that are still in time but I like to get them after the today-tasks in an order where there is a chance that a nearer lost appointment might be still rescued even though it's a bit late. The dates longer back might be lost for good anyway so they can wait a bit longer. = (2) Provided I get through (1) and (2) I'd venture the unknown where there wasn't a date until now. = (3) Well, and future dates will be minded when their time is there. = (4) For now I do this by having a sorting-column in the tasks-table that gets updated in 4 steps where my application has to select every group (1) - (4) then sequentially walk through the recordset and update the sort-order-column by a counter. Later I sort ascending by the sort-order-column. It kind of works but I consider it ugly. Could you provide a clever solution? ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Funny date-sorting task
At 07:40 PM 5/12/07, Andreas wrote: I've got a stack of tasks to show in a list. Every task has a timestamp X that may be NULL or a date. It contains the date when this tasks should be done. Sometimes it has date and the time-part, too. The list should be like this: 1) X sometime today should come first in ascending time order. 2) X in the past should show up after (1) in descending order so that not so long back dates come first 3) X = NULL 4) X sometime in the future Could you provide a clever solution? ORDER BY CASE WHEN X=today THEN 1 ELSE CASE WHEN Xtoday THEN 4 ELSE 5 END END END END ---(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
