Code

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

Came across a bit of a strange problem with an ad-hoc query. Basically I have a log of categorisation actions against a realm dataset for which I needed to find a list of realms in the last month where the last action on a pkey group (realm + userid, realms can multiple userid’s) different from the first action, and the first action was ‘A’. Luckily there’s an indexed ‘logdate’ field, otherwise I would have no idea how to find this data in a single query – so I’m extremely keen for feedback from anyone who could do it with elegance! Ne’er a dirtier subselect has ye seen
More >