Re: PL/PGSQL for permutations?

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