Can a function determine whether a primary key constraint exists on a table?

From: "Rob Richardson" <Rob(dot)Richardson(at)rad-con(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Can a function determine whether a primary key constraint exists on a table?
Date: 2006-10-12 13:17:44
Message-ID: 04A6DB42D2BA534FAC77B90562A6A03D46BE5A@server.rad-con.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Scott Ribe 2006-10-12 13:29:04 Re: more anti-postgresql FUD
Previous Message Merlin Moncure 2006-10-12 13:17:15 Re: [HACKERS] Database Auditing