PL/PGSQL for permutations?

[D. Stimits]

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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Joe Conway]

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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[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

[Joe Conway]

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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)