Postgresql and the trigger of death

Description of the problem:
I have a table with 2 columns date_acq and time_acq as text

CREATE TABLE "shapefiles" (gid serial PRIMARY KEY,
  "date_acq varchar(10),
  "time_acq" varchar(8));

I want a colum with a datetime time with the informations from date_acq and time_acq so that I can use the < and > sql operator.

I added a new column datetime_acq of type datetime and a trigger getting the information from date_acq and time_acq and putting them into the new column.
The trick is that the date is express in french format that is DD/MM/YYYY.

After learning and fighting with postgres stored procedure language I finally got a nice looking solution:

BEGIN;
ALTER TABLE shapefiles DROP COLUMN datetime_acq;
ALTER TABLE shapefiles ADD COLUMN datetime_acq timestamp with time zone;
COMMIT;

DROP FUNCTION IF EXISTS datetime_acq_timestamp() CASCADE;
CREATE FUNCTION datetime_acq_timestamp() RETURNS trigger AS $datetime_acq_timestamp$
    DECLARE
 temp_date text;
 french_pattern text := 'DD/MM/YYYY';
    BEGIN
        -- Check that date_acq and time_acq are given
        IF NEW.date_acq IS NULL THEN
            RAISE EXCEPTION 'date_acq cannot be null';
        END IF;
        IF NEW.time_acq IS NULL THEN
            RAISE EXCEPTION 'time_acq cannot be null';
        END IF;

        -- Updating datetime_acq column
        SELECT TO_CHAR(TO_DATE(NEW.date_acq,french_pattern), 'YYYY-MM-DD') into temp_date;
        NEW.datetime_acq :=  temp_date || ' ' || NEW.time_acq;
        RETURN NEW;
    END;
$datetime_acq_timestamp$ LANGUAGE plpgsql;

CREATE TRIGGER datetime_acq_trigger BEFORE INSERT OR UPDATE ON shapefiles
    FOR EACH ROW EXECUTE PROCEDURE datetime_acq_timestamp();

"Et voilà!" Now I have a nice column with valuable informations:

XSD schema validation for XSL and auto-completion in eclipse (second edition)

In Jan 8, 2006 I made a post about making xsl autocompletion work under eclipse... but I was in a hurry so the old post is not top quality ;)

Let fix it and make it a first class citizen :)

First you can download 2 already pre-configured empty files:

• test-default-namespace.xsl
  

  
  <?xml version="1.0" encoding="UTF-8"?>
  <stylesheet xmlns="http://www.w3.org/1999/XSL/Transform"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemalocation="http://www.w3.org/1999/XSL/Transform 
      http://www.w3.org/2005/02/schema-for-xslt20.xsd"
    version="2.0">
  
  </stylesheet>
  

  
  


• test-not-default-namespace.xsl
  

  
  <?xml version="1.0" encoding="UTF-8"?>
  <xsl:stylesheet 
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemalocation="http://www.w3.org/1999/XSL/Transform 
      http://www.w3.org/2005/02/schema-for-xslt20.xsd"
    version="2.0">
  
  </xsl:stylesheet>
  

  
  

Now you need to setup eclipse, Go to Windows > Preferences then XML > XML Catalogs.
Copy the exact same informations that the screen capture below!!

Validate your the xsl with Right click on the file in eclispe > Validate

When you hit Ctrl+Space, it should work now!
You shouldn't have any error or warning, especially the warning telling you that eclipse couldn't find the dtd or xml schema for xsl namespace!

Gotchas:

• Especially if you internet connection is slow, you need to wait a few seconds after opening the xsl before hitting Ctrl+Space because eclipse is downloading the file from http://www.w3.org/2005/02/schema-for-xslt20.xsd
• I don't know why because I have to close/reopen the eclipse editor for changes to take place

Technorati tags: xsl eclipse

Fomatting messages

I always found formatting Strings was painful in Java. Especially since I learn Ruby where you can do:

some = 'foobar'
my_string = "Here are #{some} value"

In Java, a basic example would be:

String searchPolygon = "POLYGON((" + lowerLeft + ", " + 
    lowerRight + ", " + upperRight + ", " 
    + upperLeft + ", " + lowerLeft + "))";

If you have too many parameters or you are looking for more efficiency, you can do something like (you can use StringBuffer instead of StringBuilder see javadoc):

StringBuilder searchPolygon = new StringBuilder();
searchPolygon.append("POLYGON((");
searchPolygon.append(lowerLeft);
searchPolygon.append(", ");
searchPolygon.append(lowerRight);
searchPolygon.append(", ");
searchPolygon.append(upperRight);
searchPolygon.append(", ");
searchPolygon.append(upperLeft);
searchPolygon.append(", ");
searchPolygon.append(lowerLeft);
searchPolygon.append("))");
searchPolygon.toString();

But still, it's ugly :(

Or you can use MessageFormat wich make the code more much readable in my opinion ;)

MessageFormat polygonFormatter = 
    new MessageFormat("POLYGON(({0}, {1}, {2}, {3}, {4}))");
Object[] values = new Object[] { 
    lowerLeft, lowerRight, upperRight, upperLeft, lowerLeft };
String searchPolygon = polygonFormatter.format(values);

It could have even be better if we could have use a map with key/value instead of indexes... but I didn't found anyting in the java language itself that does this :(