CODEWITHSUNDEEP
postgresqlplpgsql
Steven
Steven

Reputation: 776

Is there a way to generate CREATE statement for pk/fk constraints in progresql programmatically

Here is an example of how to generate CREATE statement from pgAdminIII. But is there a way to generate this programmatically in plpgsql? What I mean is, is there a convenient function that generates this for me?

Upvotes: 1

Views: 81

Answers (1)

ikusimakusi
ikusimakusi

Reputation: 159

This query will create the statements to create the current primary key and foreign key constraints:

SELECT 
DISTINCT
'ALTER TABLE '||schema_from.nspname||'.'||table_from.relname||' 
 ADD CONSTRAINT "'||const.conname||'" '||
 pg_catalog.pg_get_constraintdef (const.oid, true)||';' as add_constraint
FROM pg_constraint const
LEFT JOIN pg_namespace schema_from ON (const.connamespace = schema_from.oid)
LEFT JOIN pg_class table_from ON (const.conrelid = table_from.oid)
WHERE const.contype in ('f','p') --foreign key, primary key
;

Upvotes: 2

Related Questions