CREATE SEQUENCE asset_seq; CREATE TABLE assets ( asset_id integer not null default nextval('asset_seq'), desk text not null, status text not null, priority text not null, deadline date not null ); CREATE TABLE documentasset ( doc_id integer not null unique, label text not null ) INHERITS (assets); CREATE TABLE mediaasset ( media_id integer not null unique ) INHERITS (assets); #### /* From your command prompt type createlang plpgsql It generates the following code automatically so it isn't nessessary to remember this next bit */ CREATE FUNCTION "plpgsql_call_handler" () RETURNS opaque AS '/usr/local/lib/plpgsql.so', 'plpgsql_call_handler' LANGUAGE 'C'; CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER "plpgsql_call_handler" LANCOMPILER 'PL/pgSQL'; #### CREATE FUNCTION asset_id_ins_upd () RETURNS OPAQUE AS ' DECLARE found INTEGER; BEGIN IF TG_OP = ''UPDATE'' THEN -- Return OK for unchanging asset_id on -- update operations IF NEW.asset_id = OLD.asset_id THEN RETURN NEW; END IF; END IF; SELECT 1 INTO found FROM assets WHERE assets.asset_id = NEW.asset_id; IF FOUND THEN RAISE EXCEPTION END IF; END; ' LANGUAGE 'plpgsql' WITH (isstrict); CREATE TRIGGER assets_asset_id_ins_upd BEFORE INSERT OR UPDATE ON assets FOR EACH ROW EXECUTE PROCEDURE asset_id_ins_upd();