DROP TABLE crash9;
CREATE TABLE crash9 (
    field1 text, 
    field2 timestamptz 
);


COPY crash9 (field1, field2) FROM stdin;
0106000020E610000000000000	2012-06-06 21:59:08.49036
\N	\N
\.


DO $$
DECLARE
  sql TEXT;
  rec RECORD;
  had_column BOOLEAN;
  i INTEGER;
  new_name TEXT;
  reloid REGCLASS;
BEGIN

  reloid := 'crash9'::regclass;

  -- Ensure required fields exist

  -- We need field2 
  FOR rec IN SELECT * FROM ( VALUES ('field2') ) t(cname)
  LOOP --{
    new_name := null;
    << column_setup >>
    LOOP --{
      had_column := FALSE;
      BEGIN
        sql := 'ALTER TABLE ' || reloid::text || ' ADD ' || rec.cname
          || ' TIMESTAMPTZ NOT NULL DEFAULT now()';
        RAISE DEBUG 'Running %', sql;
        EXECUTE sql;
        EXIT column_setup;
      EXCEPTION
      WHEN duplicate_column THEN
        RAISE NOTICE 'Column % already exists', rec.cname;
        had_column := TRUE;
      WHEN others THEN
        RAISE EXCEPTION '%.%: % (%)',
          reloid, rec.cname, SQLERRM, SQLSTATE;
      END;

      IF had_column THEN

        -- Ensure data type is a TIMESTAMP WITH TIMEZONE
        sql := 'ALTER TABLE ' || reloid::text
          || ' ALTER ' || rec.cname
          || ' SET NOT NULL,'
          || ' ALTER ' || rec.cname
          || ' SET DEFAULT now()';
        BEGIN
          RAISE DEBUG 'Running %', sql;
          EXECUTE sql;
          EXIT column_setup;
        EXCEPTION
        WHEN not_null_violation THEN -- failed not-null
          RAISE NOTICE '%, renaming', SQLERRM;
        WHEN cannot_coerce THEN -- failed cast
          RAISE NOTICE '%, renaming', SQLERRM;
        WHEN others THEN
          RAISE EXCEPTION '%.%: % (%)',
            reloid, rec.cname, SQLERRM, SQLSTATE;
        END;

        -- invalid column, need rename and re-create it
        i := 0;
        << rename_column >>
        LOOP --{
          new_name := '_' || rec.cname || i;
          BEGIN
            sql := 'ALTER TABLE ' || reloid::text || ' RENAME COLUMN ' || rec.cname || ' TO ' || new_name;
            RAISE DEBUG 'Running %', sql;
            EXECUTE sql;
          EXCEPTION
          WHEN duplicate_column THEN
            i := i+1;
            CONTINUE rename_column;
          WHEN others THEN
            RAISE EXCEPTION 'renaming %.%: % (%)',
              reloid, rec.cname, SQLERRM, SQLSTATE;
          END;
          EXIT rename_column;
        END LOOP; --}
        CONTINUE column_setup;

      END IF;
    END LOOP; -- }

    -- Try to copy data from new name if possible
    IF new_name IS NOT NULL THEN -- {
      RAISE NOTICE 'Trying to recover data from % coumn', new_name;
      BEGIN
        -- Copy existing values to new field
        sql := 'UPDATE ' || reloid::text || ' SET ' || rec.cname || ' = '
            || new_name || '::timestamptz';
        RAISE DEBUG 'Running %', sql;
        EXECUTE sql;

      EXCEPTION
      WHEN others THEN
        RAISE NOTICE 'Could not initialize % with existing values: % (%)',
          rec.cname, SQLERRM, SQLSTATE;
        CONTINUE;
      END;
    END IF; -- }

  END LOOP; -- }

  -- We need field1 
  FOR rec IN SELECT * FROM ( VALUES ('field1',4326) ) t(cname,csrid) LOOP --{
    << column_setup >> LOOP --{

      << column_fixup >>
      LOOP --{

        sql := 'CREATE INDEX ON ' || reloid::text || ' ( ' || rec.cname || ')';
        RAISE DEBUG 'Running %', sql;
        EXECUTE sql;

        -- if we reached this line, all went good
        EXIT column_setup;

      END LOOP; -- } column_fixup 

      RAISE EXCEPTION 'Should not get here';

    END LOOP; -- } column_setup 

  END LOOP; -- } on expected geometry columns

END;
$$ LANGUAGE PLPGSQL;

