> The PostGres
> database has 90 tables (including the one I just added).
[...]
> I would like to write a function that would add a
> column to a table, populate it with the number 1 to n (where
> n is the number of rows in the table), make that column the
> table's primary key, create a sequence beginning with n+1,
> and give the new column a default of nextval('new_sequence').
> All of this is, if I understand things correctly,
> straightforward.
Consider adding a column of the pseudotype 'serial'.
This is in fact an integer with a sequence behind it.
It will save some effort.
I would NOT do this with a function, but rather consider each
individual case and make the changes manually.
> But what if the table already has a primary
> key contraint? A few of them do, but I believe the designer
> used them to enforce uniqueness, not to describe
> relationships. So I would like my function to check of the
> target table has a primary key constraint. If it does, that
> constraint should be dropped and a new one added to ensure
> that the column values are unique.
If the primary key columns are not likely to change,
you should leave them as they are. There is no need to create
an artificial primary key if there is a good natural primary key.
> How can I check for the
> presence of constraints inside a function?
select t.oid as tableid, t.relname as tablename,
c.oid as constraintid, conname as constraintname
from pg_constraint c join pg_class t on (c.conrelid = t.oid);
Or similar.
> Also, can someone point me to a web resource that describes
> the syntax of PostGres functions? The database I have has
> several functions that I can use as examples, but I don't
> have a reference book.
http://www.postgresql.org/docs/8.1/static/server-programming.html
Yours,
Laurenz Albe