Greetings!
I recently joined a company that uses a fairly small PostGres database. I have never used PostGres before, but I am familiar with (but not expert in) SQL Server. The PostGres database has 90 tables (including the one I just added). When the database was originally developed, the designer merely created tables to describe the various pieces of the system. There was no attempt to use primary or foreign keys to describe or enforce relationships. I would like to change this. 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. 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. How can I check for the presence of constraints inside a function?
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.
Thank you very much.
Rob Richardson
RAD-CON INC.