Can you help me with this query?

[mike.griffin]

The query below will return all of the foreign keys in the current schema,
I get the ForeignKey name as FK_NAME and both the primary and foreign
table's name and schema, now I just need the columns involved in the
foreign key itself, there is a column called confkey in pg_constraint and
it's an array? It holds the column id
SELECT ct.oid, conname as FK_NAME, confkey, nl.nspname as PK_TABLE_SCHEMA,
cl.relname as PK_TABLE_NAME,
nr.nspname as FK_TABLE_SCHEMA, cr.relname as FK_TABLE_NAME, description
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'

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Joe Conway]

mi**********@mygenerationsoftware.com wrote:

The query below will return all of the foreign keys in the current schema,
I get the ForeignKey name as FK_NAME and both the primary and foreign
table's name and schema, now I just need the columns involved in the
foreign key itself, there is a column called confkey in pg_constraint and
it's an array? It holds the column id

It is an array because foreign keys can have more than one participating
field -- how do you want that represented? Here's a way that you can get
an array of the participating field names, assuming you're using
Postgres 7.4.x:

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';

table_name | fk_table_name | tbl_atts | fk_tbl_atts
--------------------+--------------------+-------------+-------------
rule_and_refint_t3 | rule_and_refint_t1 | {id3a,id3b} | {id1a,id1b}
rule_and_refint_t3 | rule_and_refint_t2 | {id3a,id3c} | {id2a,id2c}
fktable | pktable | {fk} | {id}
clstr_tst | clstr_tst_s | {b} | {rf_a}
(4 rows)

HTH,

Joe

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Joe Conway]

mi**********@mygenerationsoftware.com wrote:

The query below will return all of the foreign keys in the current schema,
I get the ForeignKey name as FK_NAME and both the primary and foreign
table's name and schema, now I just need the columns involved in the
foreign key itself, there is a column called confkey in pg_constraint and
it's an array? It holds the column id

It is an array because foreign keys can have more than one participating
field -- how do you want that represented? Here's a way that you can get
an array of the participating field names, assuming you're using
Postgres 7.4.x:

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';

table_name | fk_table_name | tbl_atts | fk_tbl_atts
--------------------+--------------------+-------------+-------------
rule_and_refint_t3 | rule_and_refint_t1 | {id3a,id3b} | {id1a,id1b}
rule_and_refint_t3 | rule_and_refint_t2 | {id3a,id3c} | {id2a,id2c}
fktable | pktable | {fk} | {id}
clstr_tst | clstr_tst_s | {b} | {rf_a}
(4 rows)

HTH,

Joe

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Joe Conway]

(please keep posts on the list so that others can follow along)

mi**********@mygenerationsoftware.com wrote:

Joe, I'm sorry but I cannot create functions or anything like that, it's
all has to be a select statement (or series of select statements). Users
pull up our product and browse all the databases and scheme's and such, if
we asked them to create functions or if we did it through calls it would
be intrusive.

I tried to flatten out the function into a nested query but couldn't quite
get it right, is it possible to embed the select statement in the function
into the main select statement?

SELECT cl.relname as TABLE_NAME,
cr.relname as FK_TABLE_NAME,
array(select attname from pg_attribute
where attrelid = ct.conrelid
and attnum = any (ct.conkey)) as TBL_ATTS,
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';

table_name | fk_table_name | tbl_atts | fk_tbl_atts
--------------------+--------------------+-------------+-------------
rule_and_refint_t3 | rule_and_refint_t1 | {id3a,id3b} | {id1a,id1b}
rule_and_refint_t3 | rule_and_refint_t2 | {id3a,id3c} | {id2a,id2c}
fktable | pktable | {fk} | {id}
clstr_tst | clstr_tst_s | {b} | {rf_a}
(4 rows)

HTH,

Joe

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Joe Conway]

(please keep posts on the list so that others can follow along)

mi**********@mygenerationsoftware.com wrote:

Joe, I'm sorry but I cannot create functions or anything like that, it's
all has to be a select statement (or series of select statements). Users
pull up our product and browse all the databases and scheme's and such, if
we asked them to create functions or if we did it through calls it would
be intrusive.

I tried to flatten out the function into a nested query but couldn't quite
get it right, is it possible to embed the select statement in the function
into the main select statement?

SELECT cl.relname as TABLE_NAME,
cr.relname as FK_TABLE_NAME,
array(select attname from pg_attribute
where attrelid = ct.conrelid
and attnum = any (ct.conkey)) as TBL_ATTS,
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';

table_name | fk_table_name | tbl_atts | fk_tbl_atts
--------------------+--------------------+-------------+-------------
rule_and_refint_t3 | rule_and_refint_t1 | {id3a,id3b} | {id1a,id1b}
rule_and_refint_t3 | rule_and_refint_t2 | {id3a,id3c} | {id2a,id2c}
fktable | pktable | {fk} | {id}
clstr_tst | clstr_tst_s | {b} | {rf_a}
(4 rows)

HTH,

Joe

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[mike.griffin]

Joe, I'm sorry but I cannot create functions or anything like that, it's
all has to be a select statement (or series of select statements). Users
pull up our product and browse all the databases and scheme's and such, if
we asked them to create functions or if we did it through calls it would
be intrusive.

I tried to flatten out the function into a nested query but couldn't quite
get it right, is it possible to embed the select statement in the function
into the main select statement?

- Mike

mi**********@mygenerationsoftware.com wrote:

The query below will return all of the foreign keys in the current
schema,
I get the ForeignKey name as FK_NAME and both the primary and foreign
table's name and schema, now I just need the columns involved in the
foreign key itself, there is a column called confkey in pg_constraint
and
it's an array? It holds the column id

It is an array because foreign keys can have more than one participating
field -- how do you want that represented? Here's a way that you can get
an array of the participating field names, assuming you're using
Postgres 7.4.x:

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';

table_name | fk_table_name | tbl_atts | fk_tbl_atts
--------------------+--------------------+-------------+-------------
rule_and_refint_t3 | rule_and_refint_t1 | {id3a,id3b} | {id1a,id1b}
rule_and_refint_t3 | rule_and_refint_t2 | {id3a,id3c} | {id2a,id2c}
fktable | pktable | {fk} | {id}
clstr_tst | clstr_tst_s | {b} | {rf_a}
(4 rows)

HTH,

Joe

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[mike.griffin]

Joe, I'm sorry but I cannot create functions or anything like that, it's
all has to be a select statement (or series of select statements). Users
pull up our product and browse all the databases and scheme's and such, if
we asked them to create functions or if we did it through calls it would
be intrusive.

I tried to flatten out the function into a nested query but couldn't quite
get it right, is it possible to embed the select statement in the function
into the main select statement?

- Mike

mi**********@mygenerationsoftware.com wrote:

The query below will return all of the foreign keys in the current
schema,
I get the ForeignKey name as FK_NAME and both the primary and foreign
table's name and schema, now I just need the columns involved in the
foreign key itself, there is a column called confkey in pg_constraint
and
it's an array? It holds the column id

It is an array because foreign keys can have more than one participating
field -- how do you want that represented? Here's a way that you can get
an array of the participating field names, assuming you're using
Postgres 7.4.x:

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';

table_name | fk_table_name | tbl_atts | fk_tbl_atts
--------------------+--------------------+-------------+-------------
rule_and_refint_t3 | rule_and_refint_t1 | {id3a,id3b} | {id1a,id1b}
rule_and_refint_t3 | rule_and_refint_t2 | {id3a,id3c} | {id2a,id2c}
fktable | pktable | {fk} | {id}
clstr_tst | clstr_tst_s | {b} | {rf_a}
(4 rows)

HTH,

Joe

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Nick Barr]

<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



---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Nick Barr]

<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



---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[mike.griffin]

That doesn't complile, I don't think you can use a nested select where a
column goes? I get an error yelping about one of the select statements,
did you test it out? I'm using pgAdmin and don't think that it is the
problem?

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



---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[mike.griffin]

That doesn't complile, I don't think you can use a nested select where a
column goes? I get an error yelping about one of the select statements,
did you test it out? I'm using pgAdmin and don't think that it is the
problem?

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



---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match