pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
RES: Joins between int and int[] 2+ messages / 2 participants [nested] [flat]
* RES: Joins between int and int[] @ 2006-06-28 14:31 Rodrigo Sakai <rodrigo.sakai@zanthus.com.br> 2006-06-28 15:17 ` Re: RES: Joins between int and int[] Achilleus Mantzios <achill@matrix.gatewaynet.com> 0 siblings, 1 reply; 2+ messages in thread From: Rodrigo Sakai @ 2006-06-28 14:31 UTC (permalink / raw) To: 'Achilleus Mantzios' <achill@matrix.gatewaynet.com>; +Cc: pgsql-sql Thanks Mantzios, your answer helped a lot! But I have a lot of multi column foreign keys! Any other ideia?? Thanks in advance! -----Mensagem original----- De: Achilleus Mantzios [mailto:achill@matrix.gatewaynet.com] 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 Ýãñáøå óôéò Jun 27, 2006 : > 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). 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. > > > > > > 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!!! > > > > -- -Achilleus ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: RES: Joins between int and int[] 2006-06-28 14:31 RES: Joins between int and int[] Rodrigo Sakai <rodrigo.sakai@zanthus.com.br> @ 2006-06-28 15:17 ` Achilleus Mantzios <achill@matrix.gatewaynet.com> 0 siblings, 0 replies; 2+ messages in thread From: Achilleus Mantzios @ 2006-06-28 15:17 UTC (permalink / raw) To: Rodrigo Sakai <rodrigo.sakai@zanthus.com.br>; +Cc: pgsql-sql O Rodrigo Sakai Ýãñáøå óôéò Jun 28, 2006 : > Thanks Mantzios, your answer helped a lot! But I have a lot of multi column > foreign keys! Any other ideia?? > You must use/write a function that takes as an argument an array and returns the elements of this array as a set. I dont recall if some function like that exists in contrib but you could search in the archives. Also if my memory serves well there was a similar talk in th -sql list some months ago. Also keep in mind that if your queries are standardized then you can write a program in C/perl/java/php/... that does what you want. If your queries are ad-hoc then go the first approach. > Thanks in advance! > > > -----Mensagem original----- > De: Achilleus Mantzios [mailto:achill@matrix.gatewaynet.com] > 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 Ýãñáøå óôéò Jun 27, 2006 : > > > 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). > > 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. > > > > > > > > > > > > > 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!!! > > > > > > > > > > -- -Achilleus ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2006-06-28 15:17 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2006-06-28 14:31 RES: Joins between int and int[] Rodrigo Sakai <rodrigo.sakai@zanthus.com.br> 2006-06-28 15:17 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox