Re: Can you help me with this query?
От | Nick Barr |
---|---|
Тема | Re: Can you help me with this query? |
Дата | |
Msg-id | 003301c4538b$cdd93420$3202a8c0@webbased10 обсуждение исходный текст |
Ответ на | Can you help me with this query? (mike.griffin@mygenerationsoftware.com) |
Ответы |
Re: Can you help me with this query?
|
Список | pgsql-general |
<snip> > > create or replace function getattnames(oid, smallint[]) > returns name[] as ' > select array(select attname from pg_attribute > where attrelid = $1 > and attnum = any ($2)) > ' language sql; > > SELECT cl.relname as TABLE_NAME, > cr.relname as FK_TABLE_NAME, > getattnames(ct.conrelid, ct.conkey) as TBL_ATTS, > getattnames(ct.confrelid, ct.confkey) as FK_TBL_ATTS > FROM pg_constraint ct > JOIN pg_class cl ON cl.oid=conrelid > JOIN pg_namespace nl ON nl.oid=cl.relnamespace > JOIN pg_class cr ON cr.oid=confrelid > JOIN pg_namespace nr ON nr.oid=cr.relnamespace > LEFT OUTER JOIN pg_description des ON des.objoid=ct.oid > WHERE contype='f'; > How about: SELECT cl.relname as TABLE_NAME, cr.relname as FK_TABLE_NAME, (select array(select attname from pg_attribute where attrelid = ct.conrelid and attnum = any (ct.conkey))) AS TBL_ATTS, (select array(select attname from pg_attribute where attrelid = ct.confrelid and attnum = any (ct.confkey))) AS FK_TBL_ATTS FROM pg_constraint ct JOIN pg_class cl ON cl.oid=conrelid JOIN pg_namespace nl ON nl.oid=cl.relnamespace JOIN pg_class cr ON cr.oid=confrelid JOIN pg_namespace nr ON nr.oid=cr.relnamespace LEFT OUTER JOIN pg_description des ON des.objoid=ct.oid WHERE contype='f'; Note the function is no longer there ;-) Nick
В списке pgsql-general по дате отправления: