I want to take out the query for the corresponding foreign keys through sql statement. But i am facing error ehile i end the statement with concatenation (||). Please suggest me on this to run properly.
SELECT n.nspname ||'.ALTER TABLE '|| tc.table_name ||' ADD CONSTRAINT '||
tc.constraint_name ||' FOREIGN KEY (actno) references '|| ccu.table_name || '( '|| ccu.column_name AS references_field || ') ON DELETE CASCADE ON UPDATE NO ACTION;'||
FROM pg_namespace n,information_schema.table_constraints tc
LEFT JOIN information_schema.key_column_usage kcu
ON tc.constraint_catalog = kcu.constraint_catalog
AND tc.constraint_schema = kcu.constraint_schema
AND tc.constraint_name = kcu.constraint_name
LEFT JOIN information_schema.referential_constraints rc
ON tc.constraint_catalog = rc.constraint_catalog
AND tc.constraint_schema = rc.constraint_schema
AND tc.constraint_name = rc.constraint_name
LEFT JOIN information_schema.constraint_column_usage ccu
ON rc.unique_constraint_catalog = ccu.constraint_catalog
AND rc.unique_constraint_schema = ccu.constraint_schema
AND rc.unique_constraint_name = ccu.constraint_name
where ccu.table_name='account' and n.nspname='public'
I think what the others are trying to get across to you is why would you put concat and then not concat anything to it? If you have selected everything that you want to select, then there is nothing left and therfore you remove the last set of concat operators.