Getting reference key elements in right orders

Поиск
Список
Период
Сортировка
От seiliki@so-net.net.tw
Тема Getting reference key elements in right orders
Дата
Msg-id 20100517095331.34646F48516@m5.so-net.net.tw
обсуждение исходный текст
Ответы Re: Getting reference key elements in right orders  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
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

В списке pgsql-general по дате отправления:

Предыдущее
От: Scott Marlowe
Дата:
Сообщение: Re: Why Performance of SQL Query is *much* Slower in GUI PgAdmin
Следующее
От: Thom Brown
Дата:
Сообщение: Re: pg_dumpall custom format?