[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-sql
Subject: [SQL] Joins between int and int[]
From: "Rodrigo Sakai" <rodrigo.sakai () zanthus ! com ! br>
Date: 2006-06-27 14:02:06
Message-ID: 000a01c699f2$4796d340$4700a8c0 () TREEZANTHUS
[Download RAW message or body]
Hi,
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).
The query is:
select relname, attname, attnotnull, atthasdef
from pg_class as pc
inner join pg_attribute as pa
on pc.oid=pa.attrelid
inner join pg_constraint pcons
on pc.oid=pcons.conrelid
and pa.attnum = pcons.conkey -------> the problem
Thanks!!!
[Attachment #3 (text/html)]
<html xmlns:o="urn:schemas-microsoft-com:office:office" \
xmlns:w="urn:schemas-microsoft-com:office:word" \
xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=Content-Type content="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 11 (filtered medium)">
<style>
<!--
/* Font Definitions */
@font-face
{font-family:Wingdings;
panose-1:5 0 0 0 0 0 0 0 0 0;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:purple;
text-decoration:underline;}
span.EstiloDeEmail17
{mso-style-type:personal-compose;
font-family:Arial;
color:windowtext;}
@page Section1
{size:595.3pt 841.9pt;
margin:70.85pt 3.0cm 70.85pt 3.0cm;}
div.Section1
{page:Section1;}
-->
</style>
</head>
<body lang=PT-BR link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> Hi,<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-US style='font-size:
10.0pt;font-family:Arial'> 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).<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-US style='font-size:
10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-US style='font-size:
10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-US style='font-size:
10.0pt;font-family:Arial'>The query is:<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-US style='font-size:
10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-US style='font-size:
10.0pt;font-family:Arial'>select relname, attname, attnotnull, \
atthasdef<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-US style='font-size:
10.0pt;font-family:Arial'> from pg_class as pc <o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-US style='font-size:
10.0pt;font-family:Arial'> inner join pg_attribute as \
pa<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-US style='font-size:
10.0pt;font-family:Arial'> on \
pc.oid=pa.attrelid<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-US style='font-size:
10.0pt;font-family:Arial'> inner join pg_constraint \
pcons<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-US style='font-size:
10.0pt;font-family:Arial'> on \
pc.oid=pcons.conrelid<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-US style='font-size:
10.0pt;font-family:Arial'> and pa.attnum = \
pcons.conkey
-----</span></font><font size=2 face=Wingdings><span lang=EN-US
style='font-size:10.0pt;font-family:Wingdings'>à</span></font><font
size=2 face=Arial><span lang=EN-US style='font-size:10.0pt;font-family:Arial'>
the problem<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-US style='font-size:
10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><strong><b><font size=2 face=Arial><span lang=EN-US
style='font-size:10.0pt;font-family:Arial'><o:p> </o:p></span></font></b></strong></p>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span lang=EN-US
style='font-size:12.0pt'> Thanks!!! </span></font><span \
lang=EN-US><o:p></o:p></span></p>
</div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span lang=EN-US
style='font-size:12.0pt'><o:p> </o:p></span></font></p>
</div>
</body>
</html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic