Re: [GENERAL] PL/PGSQL for permutations?

[D. Stimits]

Joe Conway wrote:

D. Stimits wrote:

> table field pair. E.G., if I had in table 'one':
> left  right
> ====  =====
> a     b
> a     c
> b     d
>
> ...then I'd need a list of a, b, c, d, and produce a new table:
> left  right
> ====  =====
> a     b
> a     c
> a     d
> b     a
> b     c
> b     d
> c     a
> c     b
> c     d
> d     a
> d     b
> d     c
I don't have 7.2 to test on (and you really should upgrade to 7.3.4 if
possible anyway), but why not:
create table t1(f1 text, f2 text);
insert into t1 values('a','b');
insert into t1 values('a','c');
insert into t1 values('b','d ');
select a, b
from
  (select distinct f1 as a from t1 union select distinct f2 from t1)
   as ss1,
  (select distinct f1 as b from t1 union select distinct f2 from t1)
   as ss2
where ss1.a != ss2.b;
 a  | b
----+----
 a  | b
 a  | c
 a  | d
 b  | a
 b  | c
 b  | d
 c  | a
 c  | b
 c  | d
 d  | a
 d  | b
 d  | c
(12 rows)
This worked quite well, thank you! I'm still in need though of learning 
more about PL/PGSQL, as I have other programming to add (well, I could 
do this in C as a PostgreSQL C function, but I want to stick to PL/PGSQL 
for the moment). I'm still looking for a non-trivial, in-depth, full 
reference to PL/PGSQL. I've found many good introductory or tutorial 
type web pages, but not a full and complete reference to PL/PGSQL. The 
permutations were themselves the easy part, now each permutation has to 
do some non-trivial combinatorics on trigger whenever a change is made.

D. Stimits



---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
     joining column's datatypes do not match