Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2010
    Posts
    5

    Unanswered: SQL Query for Concatenation

    Hi,
    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'

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by sreekanthkata View Post
    Please suggest me on this to run properly.
    don't end your SELECT clause with concatenation (||)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2010
    Posts
    5

    SQL Query

    Ya i know , thats why i am asking is there any replacement for this?

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by sreekanthkata View Post
    Ya i know , thats why i am asking is there any replacement for this?
    Leave out the || ?

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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.
    Dave

  6. #6
    Join Date
    May 2008
    Posts
    277
    Oooh, I'll play!

    After your last ||, add (including the single quotes): ' -- this prevents an error caused by ending the statement with concatenation (||)'

    Voila(?)!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •