X-Original-To: pgsql-sql-postgresql.org@postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id 522049FA5C9
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 28 Jun 2006 11:31:44 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 74855-07 for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 28 Jun 2006 11:31:38 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from mail.zanthus.com.br (mail.zanthus.com.br [200.212.65.85])
	by postgresql.org (Postfix) with ESMTP id 8BCB99FA5BA
	for <pgsql-sql@postgresql.org>; Wed, 28 Jun 2006 11:31:37 -0300 (ADT)
Received: from cprsakai ([192.168.0.71])
	by mail.zanthus.com.br (8.13.4/8.13.4/Debian-3) with ESMTP id
	k5SEYkHh027378; Wed, 28 Jun 2006 11:34:46 -0300
Reply-To: <rodrigo.sakai@zanthus.com.br>
From: "Rodrigo Sakai" <rodrigo.sakai@zanthus.com.br>
To: "'Achilleus Mantzios'" <achill@matrix.gatewaynet.com>
Cc: <pgsql-sql@postgresql.org>
Subject: RES: Joins between int and int[]
Date: Wed, 28 Jun 2006 11:31:18 -0300
Message-ID: <002501c69abf$84d524f0$4700a8c0@TREEZANTHUS>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
X-Mailer: Microsoft Office Outlook 11
In-Reply-To: <Pine.LNX.4.44.0606280958420.8110-100000@matrix.gatewaynet.com>
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1807
Thread-Index: Acaaf1OO+lL4r29EQ8mA562qKix8aAAQBeSA
X-Zanthus-MailScanner: Found to be clean
X-Zanthus-MailScanner-From: rodrigo.sakai@zanthus.com.br
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200606/295
X-Sequence-Number: 25531

Thanks Mantzios, your answer helped a lot! But I have a lot of multi =
column
foreign keys! Any other ideia??

  Thanks in advance!
=20

-----Mensagem original-----
De: Achilleus Mantzios [mailto:achill@matrix.gatewaynet.com]=20
Enviada em: quarta-feira, 28 de junho de 2006 04:01
Para: Rodrigo Sakai
Assunto: Re: [SQL] Joins between int and int[]

O Rodrigo Sakai =DD=E3=F1=E1=F8=E5 =F3=F4=E9=F2 Jun 27, 2006 :

>   Hi,
>=20
> =20
>=20
>   I want to use the system catalog tables/views to query some things, =
and
> one of the queries I have to do is joining pg_attribute and =
pg_constraint
to
> know what constraint belongs to which table.attribute. My question is =
how
> can I write the join clause between a int (pg_attribute.attnum) and =
int[ ]
> (pg_constraint.conkey).

Are you having tables with multi column foreign keys?
e.g. (a,b) REFERENCES partable(para,parb).

If not then join with pg_constraint.conkey[1],
if yes then the problem becomes a little less trivial.

>=20
> =20
>=20
> =20
>=20
> The query is:
>=20
> =20
>=20
> select relname, attname, attnotnull, atthasdef
>=20
>   from pg_class as pc=20
>=20
>   inner join pg_attribute as pa
>=20
>     on pc.oid=3Dpa.attrelid
>=20
>   inner join pg_constraint pcons
>=20
>      on pc.oid=3Dpcons.conrelid
>=20
>     and pa.attnum =3D pcons.conkey                  -------> the =
problem
>=20
> =20
>=20
> =20
>=20
>  Thanks!!!=20
>=20
> =20
>=20
>=20

--=20
-Achilleus