Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id 7EABA1337BF2
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue,  5 Apr 2011 11:57:00 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 03401-03 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue,  5 Apr 2011 14:56:53 +0000 (UTC)
X-Greylist: delayed 00:06:38.79482 by SQLgrey-1.7.6
Received: from mail-vw0-f46.google.com (mail-vw0-f46.google.com
	[209.85.212.46])
	by mail.postgresql.org (Postfix) with ESMTP id 0D6611337BF8
	for <pgsql-sql@postgresql.org>; Tue,  5 Apr 2011 11:56:52 -0300 (ADT)
Received: by vws1 with SMTP id 1so318258vws.19
	for <pgsql-sql@postgresql.org>; Tue, 05 Apr 2011 07:56:53 -0700 (PDT)
Received: by 10.52.18.14 with SMTP id s14mr2985304vdd.164.1302015014213; Tue,
	05 Apr 2011 07:50:14 -0700 (PDT)
MIME-Version: 1.0
Received: by 10.52.157.65 with HTTP; Tue, 5 Apr 2011 07:49:54 -0700 (PDT)
In-Reply-To: <201104050744.52471.adrian.klaver@gmail.com>
References: <201104050727.24713.johnf@jfcomputer.com>
	<201104050744.52471.adrian.klaver@gmail.com>
From: Peter Steinheuser <psteinheuser@myyearbook.com>
Date: Tue, 5 Apr 2011 10:49:54 -0400
Message-ID: <BANLkTimqv-k0n7Fvq8Tg+6H3MWDrf3zBAw@mail.gmail.com>
Subject: Re: is there a refactor
To: Adrian Klaver <adrian.klaver@gmail.com>
Cc: pgsql-sql@postgresql.org, John Fabiani <johnf@jfcomputer.com>
Content-Type: multipart/alternative; boundary=20cf3054a12f6a17a304a02cfe4c
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.899 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 201104/4
X-Sequence-Number: 35251

--20cf3054a12f6a17a304a02cfe4c
Content-Type: text/plain; charset=ISO-8859-1

You could also do something like:

select nspname, relname, attname
from pg_attribute a
 JOIN pg_class c ON (a.attrelid = c.oid)
 JOIN pg_namespace n ON (n.oid = c.relnamespace)
where a.attname ~ 'yourcolumn'
and c.relname !~ 'pg'
and n.nspname not in ('pg_catalog','information_schema')
order by 1,2,3;

I'd functionalize it.

On Tue, Apr 5, 2011 at 10:44 AM, Adrian Klaver <adrian.klaver@gmail.com>wrote:

> On Tuesday, April 05, 2011 7:27:24 am John Fabiani wrote:
> > Hi,
> > I would like to have a simple way to retrieve information for a field
> name.
> > By that I mean have some SQL select that will return all the tables a
> field
> > name exist within a database.  I did not find anything with google but of
> > course google depends on the search string.
> >
> > Thanks in advance,
> > Johnf
>
> test(5432)aklaver=>SELECT table_name from information_schema.columns  where
> column_name = 'id';
>   table_name
> ----------------
>  user_test
>  table2
>  table1
>  hours
>  jedit_test
>  topics
>  t2
>  stone
>  serial_test
>  messages
>  binary_test
>  user_test
>  timestamp_test
>  role_t
>  py_test
>  money_test
>  lock_test
>  local_1
>  lang_test
>  interval_test
>  foob
>  fooa
>  fldlength
>  fk_1
>  default_test
>  csv_null
>  check_two
>  check_test
>  array_test
> (29 rows)
>
> --
> Adrian Klaver
> adrian.klaver@gmail.com
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>



-- 
Peter Steinheuser
psteinheuser@myyearbook.com

--20cf3054a12f6a17a304a02cfe4c
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

You could also do something like:<br><br>select nspname, relname, attname <=
br>from pg_attribute a<br>=A0JOIN pg_class c ON (a.attrelid =3D c.oid)<br>=
=A0JOIN pg_namespace n ON (n.oid =3D c.relnamespace)<br>where a.attname ~ &=
#39;yourcolumn&#39;<br>

and c.relname !~ &#39;pg&#39;<br>and n.nspname not in (&#39;pg_catalog&#39;=
,&#39;information_schema&#39;)<br>order by 1,2,3;<br><br>I&#39;d functional=
ize it.<br><br><div class=3D"gmail_quote">On Tue, Apr 5, 2011 at 10:44 AM, =
Adrian Klaver <span dir=3D"ltr">&lt;<a href=3D"mailto:adrian.klaver@gmail.c=
om">adrian.klaver@gmail.com</a>&gt;</span> wrote:<br>

<blockquote class=3D"gmail_quote" style=3D"margin: 0pt 0pt 0pt 0.8ex; borde=
r-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">On Tuesday, April=
 05, 2011 7:27:24 am John Fabiani wrote:<br>
&gt; Hi,<br>
&gt; I would like to have a simple way to retrieve information for a field =
name.<br>
&gt; By that I mean have some SQL select that will return all the tables a =
field<br>
&gt; name exist within a database. =A0I did not find anything with google b=
ut of<br>
&gt; course google depends on the search string.<br>
&gt;<br>
&gt; Thanks in advance,<br>
&gt; Johnf<br>
<br>
test(5432)aklaver=3D&gt;SELECT table_name from information_schema.columns =
=A0where<br>
column_name =3D &#39;id&#39;;<br>
 =A0 table_name<br>
----------------<br>
=A0user_test<br>
=A0table2<br>
=A0table1<br>
=A0hours<br>
=A0jedit_test<br>
=A0topics<br>
=A0t2<br>
=A0stone<br>
=A0serial_test<br>
=A0messages<br>
=A0binary_test<br>
=A0user_test<br>
=A0timestamp_test<br>
=A0role_t<br>
=A0py_test<br>
=A0money_test<br>
=A0lock_test<br>
=A0local_1<br>
=A0lang_test<br>
=A0interval_test<br>
=A0foob<br>
=A0fooa<br>
=A0fldlength<br>
=A0fk_1<br>
=A0default_test<br>
=A0csv_null<br>
=A0check_two<br>
=A0check_test<br>
=A0array_test<br>
(29 rows)<br>
<br>
--<br>
Adrian Klaver<br>
<a href=3D"mailto:adrian.klaver@gmail.com">adrian.klaver@gmail.com</a><br>
<font color=3D"#888888"><br>
--<br>
Sent via pgsql-sql mailing list (<a href=3D"mailto:pgsql-sql@postgresql.org=
">pgsql-sql@postgresql.org</a>)<br>
To make changes to your subscription:<br>
<a href=3D"http://www.postgresql.org/mailpref/pgsql-sql" target=3D"_blank">=
http://www.postgresql.org/mailpref/pgsql-sql</a><br>
</font></blockquote></div><br><br clear=3D"all"><br>-- <br>Peter Steinheuse=
r<br><a href=3D"mailto:psteinheuser@myyearbook.com">psteinheuser@myyearbook=
.com</a><br>

--20cf3054a12f6a17a304a02cfe4c--