CREATE TABLE banner_banners(
  id            SERIAL UNIQUE NOT NULL,
  site          INT4,
  name          TEXT,
  url           TEXT NOT NULL,
  image         TEXT NOT NULL,
  target        TEXT DEFAULT '_self',
  priority      INT4[] DEFAULT '{1}',
  type          INT2 DEFAULT 1,
  start_time    TIMESTAMPTZ[] DEFAULT '{\'now\'}',
  duration      INTERVAL[] DEFAULT '{\'1 millennium\'}',
  createdate    TIMESTAMPTZ NOT NULL DEFAULT now(),
  modifydate    TIMESTAMPTZ,
  builddate     TIMESTAMPTZ
);

DROP TYPE banner_type CASCADE;
CREATE TYPE banner_type AS (
  uid           INT4,
  id            INT4,
  site          INT4,
  name          TEXT,
  url           TEXT,
  image         TEXT,
  target        TEXT,
  priority      INT4,
  type          INT2,
  start_time    TIMESTAMPTZ,
  duration      INTERVAL,
  createdate    TIMESTAMPTZ,
  modifydate    TIMESTAMPTZ,
  builddate     TIMESTAMPTZ
);

CREATE OR REPLACE FUNCTION banner_expand() RETURNS SETOF banner_type AS '
  DECLARE
    myrec       RECORD;
    finalrec    RECORD;
    low         INTEGER;
    high        INTEGER;
    weight      INTEGER;
    tsta        INT4;
    tstb        TIMESTAMPTZ;
    tstc        INTERVAL;
    i           INTEGER;
    j           INTEGER;

  BEGIN
    CREATE TEMP SEQUENCE uid_seq INCREMENT 1 MINVALUE 0 START 1;

    FOR myrec IN SELECT * FROM banner_banners LOOP
      SELECT INTO low
        replace(split_part(array_dims(myrec.priority), '':'', 1), ''['','''')::INT4;
      SELECT INTO high
        replace(split_part(array_dims(myrec.priority), '':'', 2), '']'','''')::INT4;

      FOR i IN low..high LOOP
        SELECT INTO tsta myrec.priority[i];
        SELECT INTO tstb myrec.start_time[i];
        SELECT INTO tstc myrec.duration[i];

        FOR j IN 1..tsta LOOP
          raise notice ''[%:%]  i:% j:%=%'', low, high, i, j, tsta;

          raise notice ''(loop number %)  priority[%] % start_time[%] % duration[%] %'',j, i, tsta, i, tstb, i, tstc;


          SELECT INTO finalrec nextval(''uid_seq'')::INT4 AS uid,
             myrec.id, myrec.site, myrec.name, myrec.url,
             myrec.image, myrec.target, tsta, myrec.type,
             tstb, tstc, myrec.createdate,
             myrec.modifydate, myrec.builddate
            WHERE now() BETWEEN tstb
            AND (tstb + (tstc - ''1 second''));
          raise notice ''Returning'';
          RETURN NEXT finalrec;
          raise notice '' Done'';
        END LOOP;
        j:=0;
      END LOOP;
      i:=0;
    END LOOP;
    DROP SEQUENCE uid_seq;
    RETURN;
  END;
' LANGUAGE 'plpgsql';


CREATE VIEW banner_expand AS SELECT * FROM banner_expand();

INSERT INTO banner_banners(site, name, url, image, target, priority, type, start_time, duration) VALUES (208,'Put Money Today','http://www.savemoneytoday.com/','_self','/banners/banner3.gif','{3,7}',5, '{"2002-01-01 00:00:00","2003-01-01 00:00:00"}', '{"100 years","1 week"}');
SELECT * FROM banner_banners;
SELECT * FROM banner_expand;

