1

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?

Community
  • 1
  • 1
Steven
  • 714
  • 2
  • 8
  • 21

1 Answers1

2

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
;
ikusimakusi
  • 159
  • 8
  • Thanks. I was seeing if there was some other existing stored proc or function that did this. :) – Steven Mar 01 '16 at 18:57