Interesting project
http://code.google.com/p/s3ql

I wanted a method to prevent the emptying of a database table once it has some rows:

CREATE OR REPLACE FUNCTION no_empty_tables() RETURNS "trigger" AS $_$
DECLARE
 rowcount integer;
BEGIN
  SELECT count(*) INTO rowcount FROM test_table;
  IF rowcount = 1 THEN
    raise exception 'Cannot allow empty table';
  END IF;
  RETURN OLD;
END;
$_$ LANGUAGE plpgsql;

CREATE TRIGGER dont_allow_empty_table BEFORE DELETE ON
test_table FOR EACH ROW EXECURE PROCEDURE no_empty_tables();

That doesn't stop the use of TRUNCATE from emptying the table. But the newly released PostgreSQL 8.4 offers an "ON TRUNCATE" trigger and a TRUNCATE priv.

I was building a database table in which a flag column specifies one row as the default. Using PostgreSQL with a test table:

create table small_test ( name varchar(10), is_default boolean default false );

Couldn't a table constraint could be used to guarantee only zero or one rows have that flag set to true. I thought a function plus this constraint would work:

alter table small_test add constraint allow_only_one_default check (count_default_rows() < 2);

But it only kicks in after two rows have the default flag. That constraint runs before the insert so it doesn't pick up on one row already having the flag set. Setting the comparison to "< 1" doesn't really make sense. Using a custom trigger with a custom function does the trick:

create or replace function test_only_one_default() returns trigger as $_$
declare
  rowcount integer;
begin
  if new.is_default then
    select count(*) into rowcount from small_test where is_default;
    if rowcount > 0 then
      raise exception 'Second default setting is not possible';
    end if;
  end if;
  return new;
end;
$_$ language plpgsql;

create trigger allow_only_one_default before insert or update on
  small_test for each row execute procedure test_only_one_default();

Later explorations lead me to a simple table constrant of a partial unique index.