Обсуждение: PL/PGSQL for permutations?

I've found a number of basic references for PL/PGSQL, but am looking for
something more complete. First question, is there available a *complete*
reference for PL/PGSQL? I'm using PostgreSQL version 7.2.3-5.73 (for
Redhat rpm packaging version), so it is version 7.2 I am concerned with
information on (this is a default version, so at least on this machine,
I'm not interested in updates in version).

The situation that leads me to ask for this is a simple need to select
two fields from a table, and generate a table that itself has two
fields, populated by every permutation of unique values from the first
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

This isn't hard, but I'm struggling with PL/PGSQL data types. I'd love
to just find a map or associative array type, and populate it from
queries, sort it, and generate a new array or map from a nested loop.
I'm not exactly sure how the PostgreSQL non-standard array type would
work for this from PL/PGSQL, if at all.

D. Stimits, stimits AT comcast DOT net

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)

HTH,

Joe

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

D. Stimits wrote:
> 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.
>

I can't point you to anything more than the online docs for PL/pgSQL,
but if you're needing non-trivial statistics/math you should take a look
at using PL/R instead (requires at least Postgres 7.3):
   http://www.joeconway.com/plr/

HTH,

Joe