Quick Links

Re: Can you help me with this query?

From:	Brendan Jurd <blakjak(at)blakjak(dot)sytes(dot)net>
To:	mike(dot)griffin(at)mygenerationsoftware(dot)com
Cc:	PostgreSQL-general <pgsql-general(at)postgresql(dot)org>
Subject:	Re: Can you help me with this query?
Date:	2004-06-16 09:34:13
Message-ID:	40D01415.1020200@blakjak.sytes.net
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Thread:
Lists:	pgsql-general

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Hi Mike, 
 
If your foreign keys are all single-attribute, you can solve the
problem easily by only using the first element of each key. 
 
I like to use the following view to examine foreign keys: 
 
CREATE VIEW fkey AS 
 SELECT t.relname AS tab, c.attname AS col, ft.relname AS ftab,
fc.attname AS fcol 
 FROM pg_constraint fk 
  INNER JOIN pg_class t ON fk.conrelid=t.oid 
  INNER JOIN pg_attribute c ON fk.conkey[1]=c.attnum AND
c.attrelid=t.oid 
  INNER JOIN pg_class ft ON fk.confrelid=ft.oid 
  INNER JOIN pg_attribute fc ON fk.confkey[1]=fc.attnum AND
fc.attrelid=ft.oid 
 WHERE fk.contype = 'f' 
; 
 
Note "confkey[1]=..." in the join condition.  Of course, if the FK uses
multiple columns at either end the whole thing becomes rapidly
useless.  
 
HTH 
 
BJ 
 
 
<a class="moz-txt-link-abbreviated" href="mailto:mike(dot)griffin(at)mygenerationsoftware(dot)com">mike(dot)griffin(at)mygenerationsoftware(dot)com</a> wrote:
<blockquote
cite="mid46038(dot)168(dot)215(dot)73(dot)114(dot)1087316384(dot)squirrel(at)168(dot)215(dot)73(dot)114"
type="cite">
<pre wrap="">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

</pre>
<blockquote type="cite">
<pre wrap=""><a class="moz-txt-link-abbreviated" href="mailto:mike(dot)griffin(at)mygenerationsoftware(dot)com">mike(dot)griffin(at)mygenerationsoftware(dot)com</a> wrote:
</pre>
<blockquote type="cite">
<pre wrap="">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
</pre>
</blockquote>
<pre wrap="">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?

<a class="moz-txt-link-freetext" href="http://archives.postgresql.org">http://archives.postgresql.org</a>

</pre>
</blockquote>
<pre wrap="">

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to <a class="moz-txt-link-abbreviated" href="mailto:majordomo(at)postgresql(dot)org">majordomo(at)postgresql(dot)org</a>
</pre>
</blockquote>
</body>
</html>

Attachment	Content-Type	Size
unknown_filename	text/html	4.3 KB

In response to

Re: Can you help me with this query? at 2004-06-15 16:19:44 from mike.griffin

Responses

Re: Can you help me with this query? at 2004-06-16 10:22:15 from Nick Barr

Browse pgsql-general by date

	From	Date	Subject
Next Message	btober	2004-06-16 09:39:01	CREATE DATABASE ENCRYPTED ?
Previous Message	Christophe Musielak	2004-06-16 09:07:50	7.4 performance issue