Archive for December, 2009

This table and trigger will facilitate periodic currency updates (ie: race condition free) from XE feeds for a single base currency. In this case, USD.

CREATE TABLE currency_feed
(
  currency character(3) NOT NULL,
  country character varying NOT NULL,
  from_usd double precision NOT NULL,
  to_usd double precision NOT NULL,
  CONSTRAINT currency_feed_pkey PRIMARY KEY (currency)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE currency_feed OWNER TO postgres;

-- this trigger replicates the 'on duplicate key update' mysql directive
CREATE FUNCTION repl_currency() RETURNS trigger AS $BODY$
BEGIN
  IF (EXISTS(SELECT 1 FROM "currency_feed" C WHERE C."currency" = NEW."currency")) THEN
    UPDATE "currency_feed" SET "from_usd" = NEW."from_usd", "to_usd" = NEW."to_usd" WHERE "currency" = NEW."currency";
    RETURN OLD;
  ELSE
    RETURN NEW;
  END IF;
END;
$BODY$ LANGUAGE plpgsql;

ALTER FUNCTION repl_currency() OWNER TO postgres;
CREATE TRIGGER repl_currency BEFORE INSERT ON "currency_feed" FOR EACH ROW EXECUTE PROCEDURE repl_currency();

Periodic import is then just :

set client_encoding to 'ISO-8859-1';
copy currency_feed from '/path/to/feed.csv' delimiter ',' csv quote as '"';

… fast falling in love with Postgres