XKauhf2R

1CREATE FUNCTION is_attr(name CHAR, id OID) RETURNS BOOLEAN AS $$
2BEGIN RETURN EXISTS (SELECT 1 FROM pg_attribute WHERE
3  attrelid = id AND attname = name AND NOT attisdropped);
4END; $$ LANGUAGE 'plpgsql' STABLE RETURNS NULL ON NULL INPUT;
5
6CREATE FUNCTION created_at() RETURNS TRIGGER AS $$
7BEGIN
8  IF is_attr('created_at', TG_RELID) THEN NEW.created_at = CURRENT_TIMESTAMP; END IF;
9  IF is_attr('updated_at', TG_RELID) THEN NEW.updated_at = CURRENT_TIMESTAMP; END IF;
10RETURN NEW; END; $$ LANGUAGE 'plpgsql';
11
12CREATE TABLE advance (advance_id SERIAL PRIMARY KEY,
13  participant_id      INTEGER       NOT NULL
14    REFERENCES participant(participant_id),
15  creator_id          INTEGER       NOT NULL
16    REFERENCES account(account_id),
17  venture_id          INTEGER       NOT NULL
18    REFERENCES venture(venture_id),
19
20  maximum             DECIMAL(8,2)  NOT NULL
21    CHECK (maximum > 0),
22  amount              INTEGER       NOT NULL
23    CHECK (amount > 0),
24
25  expired_at          TIMESTAMPTZ   NOT NULL
26    CHECK (expired_at > created_at),
27  cleared_at          TIMESTAMPTZ
28    CHECK (cleared_at > expired_at),
29  created_at          TIMESTAMPTZ   NOT NULL);
30
31CREATE TRIGGER advance_created_at BEFORE INSERT ON advance
32  FOR EACH ROW EXECUTE PROCEDURE created_at();
33
34INSERT INTO advance(participant_id, creator_id, venture_id, maximum, amount, expired_at) VALUES (
35  (SELECT account_id FROM account WHERE name = 'K3'),
36  (SELECT account_id FROM account WHERE name = 'K3'),
37  (SELECT venture_id FROM venture WHERE name = 'Kiwilight.com, LLC'),
38  100, 2.00, '02/26/2012 16:00:00');
39
40ERROR:  new row for relation "advance" violates check constraint "advance_check"