From: greg@turnstep.com
To: pgsql-sql@postgresql.org
Subject: Re: max length of sql select statement (long!)
Date: Tue, 15 Jul 2003 14:39:09 -0000
Message-ID: <e4b54406459d42ece70ff3c36dbf62f9@biglumber.com> (raw)
In-Reply-To: <1058187439.5983.2.camel@sam>
-----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-----
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: greg@turnstep.com
Subject: Re: max length of sql select statement (long!)
In-Reply-To: <e4b54406459d42ece70ff3c36dbf62f9@biglumber.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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