Обсуждение: Can a function determine whether a primary key constraint exists on a table?

>                                           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

Rob Richardson wrote:
> 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

Welcome.

> 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

I'd use a slightly different approach, basically ('x' and 'y' are place
holders):

BEGIN;
ALTER TABLE x ADD COLUMN x_id SERIAL NOT NULL PRIMARY KEY;
-- ALTER TABLE x ADD CONSTRAINT x_y_id_fk FOREIGN KEY (y_id) REFERENCES
y (y_id) MATCH FULL ON UPDATE CASCADE -- and maybe: ON DELETE CASCADE;
UPDATE x SET x_id = DEFAULT;
COMMIT; -- after you checked the results

How to determine whether a table has a PK was already explained.

Regards,
--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
    7500 AK Enschede

// Integrate Your World //

In article <52EF20B2E3209443BC37736D00C3C1380AD5FEFF@EXADV1.host.magwien.gv.at>,
Albe Laurenz <all@adv.magwien.gv.at> wrote:

% >                                     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, perhaps simpler,

 select * from information_schema.table_constraints
  where constraint_type = 'PRIMARY KEY';

--

Patrick TJ McPhee
North York  Canada
ptjm@interlog.com