Postgres generate sql output using data dictionary tables

Question

All I need is to get a SQL query output as :

ALTER TABLE TABLE_NAME
ADD CONSTRAINT
FOREIGN KEY (COLUMN_NAME)
   REFERENCES (PARENT_TABLE_NAME);

I'm running the below DYNAMIC query USING DATA DICTIONARY TABLES,

SELECT DISTINCT
   'ALTER TABLE  ' || cs.TABLE_NAME ||
   'ADD CONSTRAINT' || rc.CONSTRAINT_NAME ||
   'FOREIGN KEY' || c.COLUMN_NAME ||
   'REFERENCES' || cs.TABLE_NAME ||
   ' (' || cs.CONSTRAINT_NAME || ') ' ||
   ' ON UPDATE ' || rc.UPDATE_RULE ||
   ' ON DELETE ' || rc.DELETE_RULE
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC,
   INFORMATION_SCHEMA.TABLE_CONSTRAINTS CS, 
   INFORMATION_SCHEMA.COLUMNS C
WHERE cs.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
  AND cs.TABLE_NAME = c.TABLE_NAME 
  AND UPPER(cs.TABLE_SCHEMA) = 'SSP2_PCAT';

But here even though I'm able to generate the desired output, the concern is its not giving the PARENT_TABLE_NAME here, rather its giving the same table_name after the ALTER TABLE Keywords.

I hope this is clear as we are using Dynamic SQL here and any help is absolutely appreciated!

Maybe you'd get an answer if you didn't write your question in upper case letters. — Laurenz Albe, Jan 23 '19 at 09:26
You have too few join conditions. For example, in `rc`and `cs` the schema name should also be identical. You also need to join with `information_schema.constraint_column_usage` to get the connection between the constraint and the columns on which it is defined. — Laurenz Albe, Jan 23 '19 at 18:39

score 2 · Accepted Answer · answered Jan 24 '19 at 05:30

Your query is missing a couple of join tables and join conditions. Also, don't forget that a foreign key can be defined on more than one column. Finally, your query is vulnerable to SQL injection via object names.

But it would be much simpler if you used pg_catalog.pg_constraint rather than the `information_schema':

SELECT format('ALTER TABLE %s ADD CONSTRAINT %I %s',
              conrelid::regclass,
              conname,
              pg_get_constraintdef(oid))
FROM pg_catalog.pg_constraint
WHERE contype = 'f'
  AND upper(connamespace::regnamespace::text) = 'SSP2_PCAT';

Thank you Sirr, You guys are so helpful with immense knowledge! It absolutely worked for my requirement - Laurenz — user10531062, Jan 24 '19 at 16:50

Postgres generate sql output using data dictionary tables

1 Answers1