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
noidea 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