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