Hi! I need to know the match columns of referencing and referenced keys.
CREATE TABLE referenced (c1 TEXT,c2 TEXT, PRIMARY KEY(c1,c2)); CREATE TABLE referencing (c1 TEXT,c2 TEXT,c3 TEXT, PRIMARY KEY(c1,c2,c3), CONSTRAINT fk FOREIGN KEY (c1,c2) REFERENCES referenced (c1,c2)); The following SQL is similar to pg_get_constraintdef(): SELECT ARRAY_TO_STRING(ARRAY(SELECT attname FROM pg_attribute WHERE attrelid=master.oid AND attnum=ANY(confkey)),';') AS master_columns ,ARRAY_TO_STRING(ARRAY(SELECT attname FROM pg_attribute WHERE attrelid=detail.oid AND attnum=ANY(conkey)),';') AS detail_columns FROM pg_class master,pg_class detail,pg_constraint WHERE master.relname='referenced' AND detail.relname='referencing' AND confrelid=master.oid AND conrelid=detail.oid AND contype='f' AND confupdtype='c' AND confdeltype='c' It appears to do the job like this: master_columns detail_columns ------------------------------ c1;c2 c1;c2 However, I am not sure the referencing and referenced key elements in the above selected strings, master_columns and detail_columns, are guaranteed to be in correct order. I suspect they will become these from time to time: master_columns detail_columns ------------------------------ c1;c2 c2;c1 I am thinking that sorting subscripts of array "pg_constraint.confkey" should guarantee the correct order, but I have no idea how to do that. My questions are: Is the above SQL reliable? If it is not, how to make it reliable? Thank you in advance! CN -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
