Обсуждение: Getting reference key elements in right orders

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

seiliki@so-net.net.tw writes:
> I need to know the match columns of referencing and referenced keys.

The query you show definitely won't match them up correctly, since that
=ANY test is not order-sensitive.  What you need to do is generate the
integers from 1 to array_length(conkey) and then join on attnum =
conkey[i], rather than using =ANY.  You can find some examples in the
information_schema views.  (In fact, you might well find that the
information_schema views are close enough already to what you need.)

            regards, tom lane