title: Generic trigger to restrict insertions based on count
body: <h2>Background</h2>
  
  <p>In a PostgreSQL 9.0 database, there are various tables that have many-to-many relationships. The number of those relationships must be restricted. A couple of example tables include:</p>
  
  <pre><code>CREATE TABLE authentication (
    id bigserial NOT NULL, -- Primary key
    cookie character varying(64) NOT NULL, -- Authenticates the user with a cookie
    ip_address character varying(40) NOT NULL -- Device IP address (IPv6-friendly)
  )
  
  CREATE TABLE tag_comment (
    id bigserial NOT NULL, -- Primary key
    comment_id bigint, -- Foreign key to the comment table
    tag_name_id bigint -- Foreign key to the tag name table
  )
  </code></pre>
  
  <p>Different relationships, however, have different limitations. For example, in the <code>authentication</code> table, a given <code>ip_address</code> is allowed 1024 <code>cookie</code> values; whereas, in the <code>tag_comment</code> table, each <code>comment_id</code> can have 10 associated <code>tag_name_id</code>s.</p>
  
  <h2>Problem</h2>
  
  <p>Currently, a number of functions have these restrictions hard-coded; scattering the limitations throughout the database, and preventing them from being changed dynamically.</p>
  
  <h2>Question</h2>
  
  <p>How would you impose a maximum many-to-many relationship limit on tables in a generic fashion?</p>
  
  <h3>Idea</h3>
  
  <p>Create a table to track the limits:</p>
  
  <pre><code>CREATE TABLE imposed_maximums (
    id serial NOT NULL,
    table_name  character varying(128) NOT NULL,
    column_group character varying(128) NOT NULL,
    column_count character varying(128) NOT NULL,
    max_size INTEGER
  )
  </code></pre>
  
  <p>Establish the restrictions:</p>
  
  <pre><code>INSERT INTO imposed_maximums
    (table_name, column_group, column_count, max_size) VALUES
    ('authentication', 'ip_address', 'cookie', 1024);
  INSERT INTO imposed_maximums
    (table_name, column_group, column_count, max_size) VALUES
    ('tag_comment', 'comment_id', 'tag_id', 10);
  </code></pre>
  
  <p>Create a trigger function:</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION impose_maximum()
    RETURNS trigger AS
  $BODY$
  BEGIN
    -- Join this up with imposed_maximums somehow?
    select
      count(1)
    from
      -- the table name
    where
      -- the group column = NEW value to INSERT;
  
    RETURN NEW;
  END;
  </code></pre>
  
  <p>Attach the trigger to every table:</p>
  
  <pre><code>CREATE TRIGGER trigger_authentication_impose_maximum
    BEFORE INSERT
    ON authentication
    FOR EACH ROW
    EXECUTE PROCEDURE impose_maximum();
  </code></pre>
  
  <p>Obviously it won't work as written... is there a way to make it work, or otherwise enforce the restrictions such that they are:</p>
  
  <ul>
  <li>in a single location; and</li>
  <li>not hard-coded?</li>
  </ul>
  
  <p>Thank you!</p>