CODEWITHSUNDEEP
postgresqldynamic-sqlpostgresql-9.3database-metadata
user10531062
user10531062

Reputation: 197

Postgres generate sql output using data dictionary tables

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!

Upvotes: 1

Views: 1900

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246063

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';

Upvotes: 2

Related Questions