Wed, 10 Apr 2013 13:30:52 -0700 (PDT)
Wed, 10 Apr 2013 13:30:52 -0700 (PDT)
X-Newsgroups: pgsql.general
Date: Wed, 10 Apr 2013 13:30:52 -0700 (PDT)
Complaints-To: groups-abuse(at)google(dot)com
Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=76.87.68.198; posting-account=96NFGAoAAABqgpEyKCN3YH2nEalcbJuu
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <7993cbff-9ea2-4ca2-a671-1e4626f87f7b(at)googlegroups(dot)com>
Subject: Creating a somewhat complicated cartesian result
From: Nick <nboutelier(at)gmail(dot)com>
Injection-Date: Wed, 10 Apr 2013 20:30:52 +0000
Content-Type: text/plain; charset=ISO-8859-1
To: pgsql-general(at)postgresql(dot)org

I have a table called "where_clauses" which contains a bunch of conditions I would like to use for building dynamic queries. I would like to know all possible queries I could perform using this data. Here is my "where_clauses" data...

INSERT INTO where_clauses (id,col_name,clause) VALUES (1,'x','x < 1');
INSERT INTO where_clauses (id,col_name,clause) VALUES (2,'x','x < 2');
INSERT INTO where_clauses (id,col_name,clause) VALUES (3,'x','x < 3');
INSERT INTO where_clauses (id,col_name,clause) VALUES (4,'y','y < 1');
INSERT INTO where_clauses (id,col_name,clause) VALUES (5,'y','y < 2');
INSERT INTO where_clauses (id,col_name,clause) VALUES (6,'y','y < 3');
INSERT INTO where_clauses (id,col_name,clause) VALUES (7,'z','z < 1');

Ideally I would like the "all possible queries" in the form of an array of ids. For example, the "all possible queries" result would be...

{1}
{1,4}
{1,4,7}
{1,5}
{1,5,7}
{1,6}
{1,6,7}
{2}
{2,4}
{2,4,7}
{2,5}
{2,5,7}
{2,6}
{2,6,7}
{3}
{3,4}
{3,4,7}
{3,5}
{3,5,7}
{3,6}
{3,6,7}
{4}
{4,7}
{5}
{5,7}
{6}
{6,7}
{7}

Note that im throwing out joining on equal columns. What is a query or function that would give all possible where_clauses?