Re: limit table to one row

On Thu, Jun 4, 2009 at 4:13 PM, Brandon
Metcalf<brandon@geronimoalloys.com> wrote:
> Is there a way when creating a table to limit it to one row?  That is,
> without using a stored procedure?
>
> I searched the documentation, but didn't find anything.
>
> --

you can also do it with a trigger.

create or replace function one_row () returns trigger as
$$
declare
  _rows bigint;
begin
  execute 'select count(*) from ' || tg_relname into _rows;

  if _rows > 1 then
    raise exception 'one row only please';
  end if;

  return new;
end;
$$ language plpgsql;

create table one_row(id int);
create trigger on_one_row after insert on one_row execute procedure one_row();

as written, you only need one trigger function, and can attach it to
multiple tables.  this has a couple of (small) advantages over the
unique constraint method...its more general and can satisfy a broader
range of conditions, is checked at the end of statement, not at each
row, and does not requires arbitrary annotation in the table.

merlin