SAPessi

CREATE TABLE client (
  id INTEGER,
  firstname VARCHAR(255),
  lastname VARCHAR(255),
  datecreated TIMESTAMP
);

CREATE TABLE client_history (
  id INTEGER,
  firstname VARCHAR(255),
  lastname VARCHAR(255),
  datecreated TIMESTAMP,
  history_creation TIMESTAMP,
  history_user INTEGER – A foreign key to the user who updated this record if required
);

– This function expects as a parameter the name of the table you want to
– create history for and a unique identifier for the record.
– in my case all tables have an ID column. Alternatively you could hand
– as a parameter also the name of the unique id column
CREATE OR REPLACE FUNCTION create_history(tablename VARCHAR(255), recordid INTEGER) RETURNS BOOLEAN AS $$
DECLARE
  vhisttablename NAME; – history table name
  vhistfieldcount INTEGER;  – number of fields in history table
  vtablefieldcount INTEGER; – number of fields in main table
  vtmprowcount INTEGER; – temporary variable to store query results
  vcurfield RECORD; – variable to loop over fields of main table to create history
  vhisttablesql TEXT; – sql to create history table
  vfieldlist TEXT; – list fo fields in main table
  vhisttablefields TEXT; – list of fields in history table
  vhistinsertsql TEXT; – SQL for insert statement
  vtmptablename VARCHAR(255); – temp variable to check if history exists
BEGIN
  vhisttablename := tablename||‘_hist’;

  – Check if the history table exists, if not we need to create it
  SELECT INTO vtmptablename relname
  FROM pg_class
  WHERE relname = vhisttablename;

  vfieldlist := ”;
  vhisttablefields := ”;

  – count the fields in the history/current table if history exists
  IF vtmptablename IS NOT NULL
  THEN
    SELECT INTO vhistfieldcount count(*)
    FROM pg_attribute AS a
    INNER JOIN pg_class AS c ON (c.oid = a.attrelid)
    INNER JOIN pg_namespace AS n ON (n.oid = c.relnamespace)
    WHERE a.attnum > 0
    AND c.relname = vhisttablename – history table name
    AND NOT a.attisdropped
    AND pg_table_is_visible(c.oid);

    SELECT INTO vtablefieldcount count(*)
    FROM pg_attribute AS a
    INNER JOIN pg_class AS c ON (c.oid = a.attrelid)
    INNER JOIN pg_namespace AS n ON (n.oid = c.relnamespace)
    WHERE a.attnum > 0
    AND c.relname = tablename – main table
    AND NOT a.attisdropped
    AND pg_table_is_visible(c.oid);
  END IF;

  – Get all the attributes and their type from the original table
  FOR vcurfield IN
  SELECT a.attname AS COLUMN, format_type(a.atttypid, a.atttypmod) AS datatype
  FROM pg_attribute AS a
  INNER JOIN pg_class AS c ON (c.oid = a.attrelid)
  INNER JOIN pg_namespace AS n ON (n.oid = c.relnamespace)
  WHERE a.attnum > 0
  AND c.relname = tablename
  AND NOT a.attisdropped
  AND pg_table_is_visible(c.oid)
  LOOP
    – populate lists of fields both for history creation and select from main table
    vhisttablefields := vhisttablefields||vcurfield.COLUMN||‘ ‘||vcurfield.datatype||‘ NULL, ‘;
    vfieldlist := vfieldlist||vcurfield.COLUMN||‘, ‘;

    – If the history table exists and the number of fields is different
    – from the main table (+3 as we add a timestamp, a user field and an history unique id)
    IF vtmptablename IS NOT NULL AND vtablefieldcount+3 <> vhistfieldcount
    THEN
      – make sure that this is the missing field in the history table
       PERFORM a.attname
       FROM pg_attribute AS a
       INNER JOIN pg_class AS c ON (c.oid = a.attrelid)
       INNER JOIN pg_namespace AS n ON (n.oid = c.relnamespace)
       WHERE a.attnum > 0
       AND c.relname = vhisttablename
       AND NOT a.attisdropped
       AND a.attname = vcurfield.COLUMN
       AND pg_table_is_visible(c.oid);

       GET DIAGNOSTICS vtmprowcount = ROW_COUNT;

      – If it is then generate the SQL and execute the alter table
       IF vtmprowcount = 0
       THEN
         vhisttablesql := ‘ALTER TABLE ‘||vhisttablename||‘ ADD COLUMN ‘||vcurfield.COLUMN||‘ ‘||vcurfield.datatype||‘ NULL;’;
         EXECUTE vhisttablesql;
       END IF;
     END IF;
  END LOOP;

  – The history table doesn’t exist. Create it
  IF vtmptablename IS NULL OR vtmptablename = ”
  THEN
    vhisttablesql := ‘CREATE TABLE ‘||vhisttablename||‘ ( histid SERIAL PRIMARY KEY, ‘;

    vhisttablesql := vhisttablesql||vhisttablefields||‘ history_creation TIMESTAMP NOT NULL DEFAULT now() );’;

    EXECUTE vhisttablesql;

    – create indexes
    vhisttablesql := ‘ CREATE INDEX idx_’||vhisttablename||‘_1 ON ‘||vhisttablename||‘ (id); ‘;
    EXECUTE vhisttablesql;

    vhisttablesql := ‘ CREATE INDEX idx_’||vhisttablename||‘_2 ON ‘||vhisttablename||‘ (history_creation); ‘;
    EXECUTE vhisttablesql;
  END IF;

  – Proceed with the history creation
  vhistinsertsql := ‘INSERT INTO ‘||vhisttablename||‘ (‘||vfieldlist||‘ history_creation) SELECT ‘||vfieldlist||‘ now() FROM ‘||tablename||‘ WHERE id=’||recordid||‘;’;

  RAISE NOTICE ‘Executing %’, vhistinsertsql;
  EXECUTE vhistinsertsql;

  GET DIAGNOSTICS vtmprowcount = ROW_COUNT;
  IF vtmprowcount > 0
  THEN
    RETURN TRUE;
  ELSE
    RETURN FALSE;
  END IF;
END;
$$
LANGUAGE plpgsql;

SELECT h.id, firstname, lastname, datecreated, hist_creation
FROM client_hist AS h
INNER JOIN (
  SELECT min(history_creation) AS mintstamp, id AS id
  FROM client_hist
  WHERE history_creation > THE-TIME-YOU-NEED
  AND id= YOUR-RECORD-ID
  GROUP BY id
) AS m ON (m.id = h.id AND m.mintstamp = h.history_creation);

– This function returns the histid you need to look at in your history table
CREATE OR REPLACE FUNCTION select_hist_id(tablename VARCHAR(255), recordid INTEGER, tstamp TIMESTAMP) RETURNS INTEGER AS $$
DECLARE
  curs REFCURSOR;
  vid INTEGER;
BEGIN
  OPEN curs FOR EXECUTE ‘SELECT h.histid
  FROM ‘||tablename||‘_hist AS h
  INNER JOIN (
    SELECT min(history_creation) AS mintstamp, id
    FROM ‘||tablename||‘_hist
    WHERE history_creation > ‘”||tstamp||”‘
    AND id=’||recid||‘
    GROUP BY id
  ) AS m ON (m.id = h.id AND m.mintstamp = h.history_creation);’;

  FETCH curs INTO vid;

  RETURN vid;
END;
$$
LANGUAGE plpgsql;

– At this point you can just get ids like this
– records in client as of this time 2009-11-02 21:13:41.552601
SELECT select_hist_id(‘client’, id, ‘2009-11-02 21:13:41.552601′::TIMESTAMP) FROM client;

CREATE FUNCTION foo(recordid int, firstname OUT VARCHAR(10)…)