From: greg@turnstep.com
To: pgsql-sql@postgresql.org
Subject: Re: max length of sql select statement (long!)
In-Reply-To: <1058187439.5983.2.camel@sam>
Date: Tue, 15 Jul 2003 14:39:09 -0000
Message-ID: <e4b54406459d42ece70ff3c36dbf62f9@biglumber.com>


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> ... I don't want to take GO:000006 into account (two parents in which 
> I am intested in). That menas, whenever I ask for children of two nodes, 
> I want a DISTINCT SET of children.

To start with, you can avoid the Java and do this in SQL:

SELECT child FROM gograph WHERE parent='GO:0000002' OR parent='GO:0000005'
EXCEPT
(SELECT child FROM gograph WHERE parent='GO:0000002'
INTERSECT 
SELECT child FROM gograph WHERE parent='GO:0000005');


And yes, I would certainly start by normalizing things a little bit:


CREATE SEQUENCE goid_seq;

CREATE TABLE goID (
  idname TEXT,
  id INTEGER NOT NULL DEFAULT nextval('goid_seq')
);

INSERT INTO goid(idname) SELECT DISTINCT parent FROM gograph;

INSERT INTO goid(idname) 
SELECT DISTINCT child FROM gograph WHERE NOT EXISTS (SELECT 1 FROM goID WHERE idname = child);

CREATE TABLE gomap (
  parent INTEGER,
  child INTEGER
);

INSERT INTO gomap SELECT 
(SELECT id FROM goid WHERE idname=parent),
(SELECT id FROM goid WHERE idname=child)
FROM gograph


As far as the binaryInteraction table, a little more information is needed: 
how are each of these tables being populated? Why the distinct? Is it because 
there may be duplicate rows in the table? The reason I as is that it might be 
better to ue triggers to compute some of the information as it comes in, 
depending on which tables are changes and how often.


- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200307151035

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE/FBGrvJuQZxSWSsgRAlEfAKCL4ttDdTRHxPRW9N00nowPh1/q/QCgqrkv
e7Ncj4al4aJ4ihktEyweJJo=
=Z/rk
-----END PGP SIGNATURE-----