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