Triggers to enforce referential integrity for foreign keys, if postgres didn't support them natively.

gistfile1.sql

CREATE OR REPLACE FUNCTION check_fk_child() RETURNS trigger AS $$
DECLARE
  fk_local TEXT := TG_ARGV[0];
  parent_table TEXT := TG_ARGV[1];
  fk_val INT;
  is_valid BOOLEAN;
  query TEXT;
BEGIN
  -- fk_val = getattr(NEW, fk_local)
  EXECUTE format('SELECT $1.%I', fk_local) USING NEW INTO fk_val;
  query := format('SELECT id IS NOT NULL FROM %I WHERE id = %L FOR SHARE', parent_table, fk_val);
  EXECUTE query INTO is_valid;
  RAISE DEBUG '%, %', query, is_valid;
  IF is_valid IS NULL THEN
    RAISE EXCEPTION 'insert or update on table "%" violates foreign key trigger "%"', TG_RELNAME, TG_NAME USING 
      DETAIL=format('Key (%s)=(%s) is not present in table "%s".', fk_local, fk_val, parent_table);
  END IF;
  return NEW;
END;
$$ LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION check_fk_parent() RETURNS trigger AS $$
DECLARE
  child_table TEXT := TG_ARGV[0];
  fk_name TEXT := TG_ARGV[1];
  is_referenced BOOLEAN;
  query TEXT;
BEGIN
  IF TG_OP = 'DELETE' OR NEW.id != OLD.id THEN
    query := format('SELECT %I IS NOT NULL FROM %I WHERE %I = %L LIMIT 1', fk_name, child_table, fk_name, OLD.id);
    EXECUTE query INTO is_referenced;
    RAISE DEBUG '%, %', query, is_referenced;
    IF is_referenced IS NOT NULL THEN
      RAISE EXCEPTION 'update or delete on table "%" violates foreign key trigger "%" from table "%"', TG_RELNAME, TG_NAME, child_table USING
        DETAIL=format('Key (id)=(%s) is still referenced from table "%s".', OLD.id, child_table);
    END IF;
  END IF;
  IF TG_OP = 'DELETE' THEN
    RETURN OLD;
  ELSE
    RETURN NEW;
  END IF;
END;
$$ LANGUAGE 'plpgsql';


CREATE TABLE parent (
  id SERIAL PRIMARY KEY
);

CREATE TABLE child (
  parent_id INT -- REFERENCES parent(id)
);

CREATE TRIGGER fk AFTER DELETE OR UPDATE OF id ON parent FOR EACH ROW EXECUTE PROCEDURE check_fk_parent('child', 'parent_id');
CREATE TRIGGER fk AFTER INSERT OR UPDATE OF parent_id ON child FOR EACH ROW EXECUTE PROCEDURE check_fk_child('parent_id', 'parent');

INSERT INTO child VALUES (1);
-- ERROR:  insert or update on table "child" violates foreign key trigger "fk"
-- DETAIL:  Key (parent_id)=(1) is not present in table "parent".
INSERT INTO parent VALUES (1);
INSERT INTO child VALUES (1);
DELETE FROM parent;
-- ERROR:  update or delete on table "parent" violates foreign key trigger "fk" from table "child"
-- DETAIL:  Key (id)=(1) is still referenced from table "child".

python_implementation

CREATE OR REPLACE FUNCTION python_check_fk_child() RETURNS trigger AS $$
parent_table = TD['args'][1]
try:
    q = SD[parent_table]
except KeyError:
    q = SD[parent_table] = plpy.prepare(
            'SELECT id IS NOT NULL FROM %s WHERE id = $1 FOR SHARE' % (parent_table),
            ['int'])
is_valid = plpy.execute(q, [TD['new'][TD['args'][0]]])
if not len(is_valid):
    plpy.error('foo')
$$ LANGUAGE plpythonu;

CREATE OR REPLACE FUNCTION python_check_fk_parent() RETURNS trigger AS $$
child_table = TD['args'][0]
fk_name = TD['args'][1]

try:
    q = SD[(child_table, fk_name)]
except KeyError:
    q = SD[(child_table, fk_name)] = plpy.prepare(
            'SELECT %s IS NOT NULL FROM %s WHERE %s = $1 LIMIT 1' % (
                plpy.quote_ident(fk_name), plpy.quote_ident(child_table), plpy.quote_ident(fk_name)
            ),
            ['int'])

if not (TD['new'] and TD['old']['id'] == TD['new']['id']):
    references = plpy.execute(q, [TD['old']['id']], 1)
    if len(references):
        plpy.error('foo')
$$ LANGUAGE plpythonu;

The python version is faster due to being able to cache the query plans!