From SteveC@innocent.com Mon Jan  24 15:09:55 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 907C23A545A
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 24 Jan 2005 15:09:55 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 40085-09
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 24 Jan 2005 15:09:49 +0000 (GMT)
Received: from rosemontschool.org (rosemont-sch.org [209.158.100.18])
	by svr1.postgresql.org (Postfix) with ESMTP id C0B823A546F
	for <pgsql-sql@postgresql.org>; Mon, 24 Jan 2005 15:09:49 +0000 (GMT)
Received: by rosemontschool.org (Postfix, from userid 99)
	id BF2761B45FB; Mon, 24 Jan 2005 09:57:40 -0500 (EST)
Received: from odyssey (rosemont-sch.org [209.158.100.18])
	by rosemontschool.org (Postfix) with ESMTP id 9DA071B45F9
	for <pgsql-sql@postgresql.org>; Mon, 24 Jan 2005 09:57:38 -0500 (EST)
Subject: Looking up table names by REFERENCES
From: Steve Castellotti <SteveC@innocent.com>
To: pgsql-sql@postgresql.org
Content-Type: multipart/alternative; boundary="=-JfpzofW1oP0zYcES2gNW"
Date: Tue, 25 Jan 2005 04:09:09 +1300
Message-Id: <1106579349.746.8.camel@odyssey>
Mime-Version: 1.0
X-Mailer: Evolution 2.0.2 (2.0.2-3) 
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.555 tagged_above=0 required=5
	tests=FORGED_RCVD_HELO, HTML_20_30, HTML_MESSAGE
X-Spam-Level: 
X-Archive-Number: 200501/229
X-Sequence-Number: 20017


--=-JfpzofW1oP0zYcES2gNW
Content-Type: text/plain
Content-Transfer-Encoding: 7bit


    Hello all. I'm trying to write a recursive procedure to allow me to
handle some data backup routines and deal with conflicts when restoring
from within the program.

    Essentially, what I'd like to be able to do is if a table called
"image" has a column called "file_id" which references a column called
"file_id" in another table called "file" I want to be able to determine
that pragmatically. If I wanted to backup all of the information about
images in the database, I would need to backup all of the information
about the file(s) each image corresponds to.

    For instance, I can get a list of all (user) table names with:

SELECT relname AS table_name, oid
	FROM pg_class
		WHERE NOT relname ~ 'pg_.*'
			AND NOT relname ~ 'pga_.*'
			AND NOT relname ~ '.*_pkey'
			AND NOT relname ~ '.*_id_key'
		ORDER BY relname;

and I can get a list of column names and their types (for the "image"
table) with:

SELECT a.attname AS field, t.typname AS type 
	FROM pg_class c, pg_attribute a, pg_type t 
		WHERE c.relname = 'image' and a.attnum > 0 
			and a.attrelid = c.oid and a.atttypid = t.oid 
		ORDER BY a.attnum;


    Surely there's a simple way I can trace REFERENCES in a particular
column across tables?


    Any help would be most appreciated, especially if I could be cc'd
directly.


Cheers

Steve Castellotti

--=-JfpzofW1oP0zYcES2gNW
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
  <META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8">
  <META NAME="GENERATOR" CONTENT="GtkHTML/3.3.2">
</HEAD>
<BODY>
<BR>
&nbsp;&nbsp;&nbsp; Hello all. I'm trying to write a recursive procedure to allow me to handle some data backup routines and deal with conflicts when restoring from within the program.<BR>
<BR>
&nbsp;&nbsp;&nbsp; Essentially, what I'd like to be able to do is if a table called &quot;image&quot; has a column called &quot;file_id&quot; which references a column called &quot;file_id&quot; in another table called &quot;file&quot; I want to be able to determine that pragmatically. If I wanted to backup all of the information about images in the database, I would need to backup all of the information about the file(s) each image corresponds to.<BR>
<BR>
&nbsp;&nbsp;&nbsp; For instance, I can get a list of all (user) table names with:<BR>
<BR>
SELECT relname AS table_name, oid<BR>
	FROM pg_class<BR>
		WHERE NOT relname ~ 'pg_.*'<BR>
			AND NOT relname ~ 'pga_.*'<BR>
			AND NOT relname ~ '.*_pkey'<BR>
			AND NOT relname ~ '.*_id_key'<BR>
		ORDER BY relname;<BR>
<BR>
and I can get a list of column names and their types (for the &quot;image&quot; table) with:<BR>
<BR>
SELECT a.attname AS field, t.typname AS type <BR>
	FROM pg_class c, pg_attribute a, pg_type t <BR>
		WHERE c.relname = 'image' and a.attnum &gt; 0 <BR>
			and a.attrelid = c.oid and a.atttypid = t.oid <BR>
		ORDER BY a.attnum;<BR>
<BR>
<BR>
&nbsp;&nbsp;&nbsp; Surely there's a simple way I can trace REFERENCES in a particular column across tables?<BR>
<BR>
<BR>
&nbsp;&nbsp;&nbsp; Any help would be most appreciated, especially if I could be cc'd directly.<BR>
<BR>
<BR>
Cheers<BR>
<BR>
Steve Castellotti
</BODY>
</HTML>

--=-JfpzofW1oP0zYcES2gNW--




From mike@fuhr.org Tue Jan  25 19:10:55 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 944A13A5890
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 25 Jan 2005 19:10:55 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 06713-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 25 Jan 2005 19:10:49 +0000 (GMT)
Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158])
	by svr1.postgresql.org (Postfix) with ESMTP id B3BA33A5862
	for <pgsql-sql@postgresql.org>; Tue, 25 Jan 2005 19:10:49 +0000 (GMT)
Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1])
	by tigger.fuhr.org (8.13.1/8.13.1) with ESMTP id j0PJAj5v013890
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK);
	Tue, 25 Jan 2005 12:10:48 -0700 (MST)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: from winnie.fuhr.org (localhost [127.0.0.1])
	by winnie.fuhr.org (8.13.1/8.13.1) with ESMTP id j0PJAjS1078225;
	Tue, 25 Jan 2005 12:10:45 -0700 (MST)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: (from mfuhr@localhost)
	by winnie.fuhr.org (8.13.1/8.13.1/Submit) id j0PJAjdM078224;
	Tue, 25 Jan 2005 12:10:45 -0700 (MST) (envelope-from mfuhr)
Date: Tue, 25 Jan 2005 12:10:45 -0700
From: Michael Fuhr <mike@fuhr.org>
To: Steve Castellotti <SteveC@innocent.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Looking up table names by REFERENCES
Message-ID: <20050125191045.GA78147@winnie.fuhr.org>
References: <1106579349.746.8.camel@odyssey>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <1106579349.746.8.camel@odyssey>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.003 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200501/235
X-Sequence-Number: 20023

On Tue, Jan 25, 2005 at 04:09:09AM +1300, Steve Castellotti wrote:

>     Surely there's a simple way I can trace REFERENCES in a particular
> column across tables?

The pg_constraint table contains foreign key constraints.  Here's
an example query that appears to work in trivial tests:

SELECT c.conname,
       c.conrelid::regclass, a1.attname,
       c.confrelid::regclass, a2.attname AS fattname
FROM pg_constraint AS c
JOIN pg_attribute AS a1 ON a1.attrelid = c.conrelid AND a1.attnum = ANY (c.conkey)
JOIN pg_attribute AS a2 ON a2.attrelid = c.confrelid AND a2.attnum = ANY (c.confkey)
WHERE c.contype = 'f';

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/